IMPORTANT ANNOUNCEMENT

These forums will be permanently set to read-only mode on July 18, 2022. From that day on, no new posting or comment will be allowed on the site, but the historical content will remain intact and searchable.

A new location for posting questions about PlanetPress Suite is now available:

OL Learn - PlanetPress Classic (opens in new tab)

Topic Options
#58227 - 03/07/22 02:41 PM PlanetPress Database - CASE WHEN not working
Tom@ECI Offline
OL Newbie

Registered: 04/20/10
Posts: 24
Loc: Fort Worth, Tx
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

Top
#58228 - 03/07/22 04:11 PM Re: PlanetPress Database - CASE WHEN not working [Re: Tom@ECI]
Jean-Cédric Offline
OL Expert

Registered: 10/03/16
Posts: 681
Loc: Québec, Canada
Can you provide and example of the data to see why it does that? (anonymize the data).

And you could regroup those:
Code:
,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'


as

Code:
,case
      when ltrim([Customer_Account]) like 'AOP%' OR 
           ltrim([Customer_Account]) like 'PNW%' OR
           ltrim([SalesOrder_ShiptoAddress2]) like 'SUITE%' OR
           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'
_________________________
♪♫♪♫
99 frigging bugs in my code
99 frigging bugs
Take one down
Code around
127 frigging bugs in my code
♪♫♪♫

Top
#58229 - 03/07/22 06:26 PM Re: PlanetPress Database - CASE WHEN not working [Re: Jean-Cédric]
Tom@ECI Offline
OL Newbie

Registered: 04/20/10
Posts: 24
Loc: Fort Worth, Tx
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

Top
#58230 - 03/07/22 06:53 PM Re: PlanetPress Database - CASE WHEN not working [Re: Tom@ECI]
Jean-Cédric Offline
OL Expert

Registered: 10/03/16
Posts: 681
Loc: Québec, Canada
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
_________________________
♪♫♪♫
99 frigging bugs in my code
99 frigging bugs
Take one down
Code around
127 frigging bugs in my code
♪♫♪♫

Top
#58231 - 03/08/22 11:24 AM Re: PlanetPress Database - CASE WHEN not working [Re: Tom@ECI]
Tom@ECI Offline
OL Newbie

Registered: 04/20/10
Posts: 24
Loc: Fort Worth, Tx
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

Top
#58232 - 03/08/22 11:50 AM Re: PlanetPress Database - CASE WHEN not working [Re: Tom@ECI]
Jean-Cédric Offline
OL Expert

Registered: 10/03/16
Posts: 681
Loc: Québec, Canada
Your link doesn't work
_________________________
♪♫♪♫
99 frigging bugs in my code
99 frigging bugs
Take one down
Code around
127 frigging bugs in my code
♪♫♪♫

Top
#58233 - 03/08/22 11:53 AM Re: PlanetPress Database - CASE WHEN not working [Re: Tom@ECI]
Jean-Cédric Offline
OL Expert

Registered: 10/03/16
Posts: 681
Loc: Québec, Canada
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.
_________________________
♪♫♪♫
99 frigging bugs in my code
99 frigging bugs
Take one down
Code around
127 frigging bugs in my code
♪♫♪♫

Top