Left joining with LIKE behavior
Spent a few hours trying to track down why a query was working on my database but not in my elasticube.
It was due to this unexpected behavior in one of my joins and I thought I would share.
In my data I have "jobs" which have a 4 digit job code and a year that they occurred.
So jobcode 'J181' is a J job that happened in 2018.
My challenge was to figure out which jobs from this year were renewed for next year by checking if there is a 2019 job for the same letter. So if theres a J191 job, that means the J181 was renewed.
So heres what that looks like. I left join on the jobs table again looking for future year jobs what that particular job letter. So in my data, G and H have 2019 jobs, and P does not, so this query is correct, and I can filter out rows that have an empty FJ.JobID.
The problem I ran into is this:
The only change I made is in the left join, comparing the two jobID's with LIKE instead of =.
Doing this in sql give the same results as an = comparison but in sisense the behavior completely changes.
I ended up creating a column in my cube table that holds the job letter so I wouldn't have to use the LEFT operator. When comparing these strings, LIKE or = give the same results.
Theres something about the combination of the LEFT() string function and using LIKE as a comparison together that gives these results.
I thought I would share this and see if anyone can point out something I'm not seeing, I often use LIKE to compare strings instead of equals and my expectation is that without a wild card it would function the same as =.
-
What about creating a custom field against each record with the value you are expecting for the next year, if that is possible then you can just do a match, or even do a count of how many records have that entry in the job ref column then you just have the value if it has a followup entry or not?
Please sign in to leave a comment.
Comments
1 comment