Reference Code SQL Missing Aliasing

Stephen Johnson shared this problem 11 months ago
Resolved

I have a view that uses a reference code to format some activity values in our database. The SQL that is being generated by Yellowfin looks like this:

CASE
           WHEN activity IN(0, 1)
           THEN 'Client'
           WHEN activity IN(3, 4)
           THEN 'Depot'
           WHEN activity = 7
           THEN 'Lunch'
           WHEN activity = 5
           THEN 'Break'
           WHEN activity = 6
           THEN 'Refuel'
           WHEN activity IN(37, 38, 39)
           THEN 'Inspections/Paperwork'
           WHEN activity IN(32, 31)
           THEN 'Meeting'
           WHEN activity IN(30, 36, 35)
           THEN 'Training'
           WHEN activity IN(34, 12, 9, 10, 2, 15, 8, 33, 53)
           THEN 'Other'
           ELSE 'Unknown'
       END,
...
FROM events

The problem is that the Yellowfin is failing to alias the table and reference the alias in this CASE block. In other words, the SQL it should be generating would look like this:

CASE
           WHEN pickupEvent.activity IN(0, 1)
           THEN 'Client'
           WHEN pickupEvent.activity IN(3, 4)
           THEN 'Depot'
           WHEN pickupEvent.activity = 7
           THEN 'Lunch'
           WHEN pickupEvent.activity = 5
           THEN 'Break'
           WHEN pickupEvent.activity = 6
           THEN 'Refuel'
           WHEN pickupEvent.activity IN(37, 38, 39)
           THEN 'Inspections/Paperwork'
           WHEN pickupEvent.activity IN(32, 31)
           THEN 'Meeting'
           WHEN pickupEvent.activity IN(30, 36, 35)
           THEN 'Training'
           WHEN pickupEvent.activity IN(34, 12, 9, 10, 2, 15, 8, 33, 53)
           THEN 'Other'
           ELSE 'Unknown'
       END,
...
FROM events AS pickupEvent

The reason why this is important is because I want to join our events table to itself (to link the pickupEvents with their corresponding dropoffEvents). When I do this my view breaks because the activity column is now ambiguous between the two instances of the events table. I have not tested this, but I would expect the view to also break if I joined any other table that had an 'activity' column.

It is SQL best practice to always use aliases when defining tables and referencing columns in order to avoid ambiguity. Yellowfin needs to follow best practices in the SQL it generates.

Comments (3)

photo
1

Hi Stephen Johnson,

Thanks for your email.

I have tested Refcodes with Case statement on 7.4, 20180313 and couldn't replicate the issue:

2e7a9c39f5b3e38af27a0b83b82a1607


I may be something else here, please give me your exact steps. I will try again to replicate the issue as well please let me know the Build you are currently using.

Let me know if you have any questions.

Regards,

Mahesh

photo
1

Thank you for your reply Mahesh. I did more investigation and found the source of the problem. It's not the reference code formatting that is causing the problem. This particular field has a calculation step before the reference code formatting is applied and it's in that step that the unqualified column names were being used. I was able to edit the calculation and add the qualifications myself.

photo
1

Hi Stephen Johnson,

Thanks for your reply.

Glad to know you found the root cause and fixed the issue.

Please let me know if you have any questions.

Regards,

Mahesh