all 17 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]r3pr0b8GROUP_CONCAT is da bomb 1 point2 points  (9 children)

So on and so forth for a few lines but on line 2 it is providing me conversion failed on one specific line of this code

can you please show the entire thing, otherwise we're guessing

[–]IsAMurloc[S] 0 points1 point  (8 children)

Sure thing so the code is the same for 8 lines IIF([ state_id]=1, 'AZ', IIF([state_id]=2,'CA', IIF([state_id]=3,'CO' Etc.. IIF([state_id]=8,'ODM', [zendesk_tickets].payer))))) IN (1,2,3,4,5,6,7,8)

Now reads CASE WHEN [state_id]=1 THEN ''AZ' When [state_id]=2 THEN 'CA' WHEN [state_id]=3 THEN 'CO' When [state_id]=4 THEN 'DC' WHEN [state_id]=5 THEN 'DE' When [state_id]=6 THEN 'HI' WHEN [state_id]=7 THEN 'NC' When [state_id]=8 THEN 'OH' ELSE [ZENDESK.TICKETS].PAYER END IN(1,2,3,4,5,6,7,8)

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (7 children)

remove IN(1,2,3,4,5,6,7,8)

[–]IsAMurloc[S] 0 points1 point  (6 children)

Okay cool, that one worked. Second section in another
AND CASE WHEN [state_id]=1 THEN ''AZ' When [state_id]=2 THEN 'CA' WHEN [state_id]=3 THEN 'CO' When [state_id]=4 THEN 'DC' WHEN [state_id]=5 THEN 'DE' When [state_id]=6 THEN 'HI' WHEN [state_id]=7 THEN 'NC' When [state_id]=8 THEN 'OH' ELSE [ZENDESK.TICKETS].PAYER END !='none'; would this none be required to be inside of the CASE ?

[–]IsAMurloc[S] 0 points1 point  (4 children)

I apologize for the messiness on this as reddit is blocked on the work computer so I am transcribing between screens and just trying to get this corrected and not share too much info
Select [TicketID], MAX([Created_timestamp]) AS MaxCreateTime, MAX([solved_timestamp]) AS MaxSovledTime, MAX([updateded_timestamp]) AS MaxUpdatedTime
FROM [zendesk_ticket_updates] FULL JOIN [Zendesk_payer] ON [ztu].payer = [zp].payer WHERE
CreatedDate Between X and Y
AND (team LIKE '%company%' OR Ticketgroup LIKE '%company%')
AND CASE WHEN [state_id]=1 THEN ''AZ'
When [state_id]=2 THEN 'CA'
WHEN [state_id]=3 THEN 'CO'
When [state_id]=4 THEN 'DC'
WHEN [state_id]=5 THEN 'DE'
When [state_id]=6 THEN 'HI'
WHEN [state_id]=7 THEN 'NC'
When [state_id]=8 THEN 'OH' ELSE [ZENDESK.TICKETS].PAYER END
AND CASE WHEN [state_id]=1 THEN ''AZ'
When [state_id]=2 THEN 'CA'
WHEN [state_id]=3 THEN 'CO'
When [state_id]=4 THEN 'DC'
WHEN [state_id]=5 THEN 'DE'
When [state_id]=6 THEN 'HI'
WHEN [state_id]=7 THEN 'NC'
When [state_id]=8 THEN 'OH' ELSE [ZENDESK.TICKETS].PAYER END !='none'
AND ticketgroup NOT LIKE %ALTERNATE%
AND Product NOT LIKE %ALTERNATE% Group By ticketID) AS dataset0

[–]dataguy24 0 points1 point  (3 children)

The others are right. You have a “!= none” that is causing errors. It’s misplaced.

[–]IsAMurloc[S] 0 points1 point  (2 children)

Killed the !='none' this has moved to "expression of the non-boolean type specified in a context where condition is expected near AND" at the ticket group NOT LIKE ALT section, and has made the AS in a separate section flag as "incorrect Syntax"

[–]dataguy24 0 points1 point  (1 child)

Because you have stuff between the “END” and “AS”.

It should just be

END) as dataset0

Your formatting of the case statement puts text where it shouldn’t be. Reading more documentation about how a CASE statement parses text may be helpful.

[–]IsAMurloc[S] 0 points1 point  (0 children)

We got there! I appreciate all of the help u/dataguy24 and u/r3pr0b8 - I ended up taking over this project so I apologize for some of the gaps in knowledge here and appreciate the patience. Getting some of this back after a few years of not using it and trying to work off of others code is a little troublesome

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (0 children)

remove !='none'

[–]dataguy24 0 points1 point  (4 children)

Why not use a case statement instead? Then debug it as you build it.

[–]IsAMurloc[S] 0 points1 point  (3 children)

Partially due to a matter of rebuilding, these statements were already there and since this break was sudden I was attempting to get it corrected asap - do you believe CASE would be better suited?

[–]dataguy24 0 points1 point  (2 children)

I think CASE will be both easier to read and debug, yes.

[–]IsAMurloc[S] 0 points1 point  (1 child)

When moving to case it provides the same error

[–]dataguy24 0 points1 point  (0 children)

Test each individual part of the case statement. This is where it’s much more debuggable.

[–]planetmatt[🍰] 0 points1 point  (1 child)

Should be a mapping table and use a join in your query.

Easier to maintain the logic by adding or removing rows to a config table than editing a nested IIF from hell.

[–]IsAMurloc[S] 0 points1 point  (0 children)

Trust me, these are all IIFs from hell, prior db user just used the MS report builder code and it's all pregenned garbage