I am using the statement below to pull deliveries from a SQL table and save as a CSV file. The bold lines below will flip the suite and street around based on either the customer account# or if it finds SUITE or STE in the Street Address field. This part is not working for me. It is not matching any of the WHEN conditions and just uses the ELSE. Can someone tell me what I am doing wrong, or is this even possible using PlanetPress Database?


select[SalesOrder_OrderId] as 'Delivery #'
,case when [SalesOrder_ShiptoName] like '%SEE BELOW%' then [SB_NAME].[Text] else [SalesOrder_ShiptoName] end as 'Customer Name'
,case
when (ltrim([Customer_Account]) like 'AOP%' or ltrim([Customer_Account]) like 'PNW%') then ltrim(rtrim([SalesOrder_ShiptoAddress1]))+';'+ltrim(rtrim([SalesOrder_ShiptoAddress2]))
when ltrim([SalesOrder_ShiptoAddress2]) like 'SUITE%' then ltrim(rtrim([SalesOrder_ShiptoAddress1]))+';'+ltrim(rtrim([SalesOrder_ShiptoAddress2]))
when ltrim([SalesOrder_ShiptoAddress2]) like 'STE%' then ltrim(rtrim([SalesOrder_ShiptoAddress1]))+';'+ltrim(rtrim([SalesOrder_ShiptoAddress2]))
when [SalesOrder_ShiptoName] like '%SEE BELOW%' then [SB_ADDRESS].[Text]
else ltrim(rtrim([SalesOrder_ShiptoAddress2]))+';'+ltrim(rtrim([SalesOrder_ShiptoAddress1])) end as 'Site Address 1'
,[SalesOrder_ShiptoCity] as 'Site City' ,[SalesOrder_ShiptoState] as 'Site State' ,[SalesOrder_ShiptoZip] as 'Site Zip'
from [DDP-DDMS-Ensite].[dbo].[ViewSalesOrderdetail]
left outer join [DDP-DDMS-Ensite].[dbo].[ViewSalesOrderNotes] SB_NAME on SB_NAME.[RecordType]='AAAA' AND SB_NAME.[Sequence]='110' and SB_NAME.[GlobalNoteKey]=left([SalesOrder_OrderId],8)
left outer join [DDP-DDMS-Ensite].[dbo].[ViewSalesOrderNotes] SB_ADDRESS on SB_ADDRESS.[RecordType]='AAAA' AND SB_ADDRESS.[Sequence]='120' and SB_ADDRESS.[GlobalNoteKey]=left([SalesOrder_OrderId],8)
left outer join [DDP-DDMS-Ensite].[dbo].[ViewSalesOrderNotes] SB_CITY on SB_CITY.[RecordType]='AAAA' AND SB_CITY.[Sequence]='130' and SB_CITY.[GlobalNoteKey]=left([SalesOrder_OrderId],8)
left outer join [DDP-DDMS-Ensite].[dbo].[ViewSalesOrderNotes] SB_STATE on SB_STATE.[RecordType]='AAAA' AND SB_STATE.[Sequence]='130' and SB_STATE.[GlobalNoteKey]=left([SalesOrder_OrderId],8)
left outer join [DDP-DDMS-Ensite].[dbo].[ViewSalesOrderNotes] SB_ZIP on SB_ZIP.[RecordType]='AAAA' AND SB_ZIP.[Sequence]='130' and SB_ZIP.[GlobalNoteKey]=left([SalesOrder_OrderId],8)
) as ShipTo

Can you provide and example of the data to see why it does that? (anonymize the data).

And you could regroup those:


as

Hi Jean-Cédric, See if this link below will work for you. This is how it looks in Excel after I have imported it. When I run from SMS the Site_Address_1 works correctly.

Lines 197 and 198 have the Suite showing up in the I column. I have columns J & K listed for troubleshooting.

https://lh3.googleusercontent.com/pw/AM-...4-no?authuser=0

Have you tried you SQL query in another SQL editor like MSSMS?

From what I see in your example, line 197-198 would both end-up with: [SalesOrder_ShiptoAddress1]+';'+[SalesOrder_ShiptoAddress2] becasue they both start with AOP in the column Account

Yes, I designed my query in MSSMS and it works fine there.
In this image, you can see that on lines 197 and 198 that columns J & K listed have the suite and street swapped from the other records. So yes, these *should* be flipped based on the Account number but isn't!

https://lh3.googleusercontent.com/pw/AM-...4-no?authuser=0

Your link doesn't work

Anyhow, it would be best you open a technical support ticket so a technician can remote at your site and see, play around with your code to try and reproduce the issue.