Syteline Customer Order Review TSQL COALESCE and FOR XML PATH


Introduction

Here’s one that used a few interesting things, so I thought I’d post it.

Our salesman wanted a report from our Infor Syteline 8 database that he can use to review order pattern by ship to. The example below makes use of 2 functions that are quite nice. The first being coalesce, I’m using this as our address data is full of nulls and I want to format the information as nicely as possible. The second function is within an in-line sub query (which probably should be done as a join at the bottom but hey-ho) and within the sub query I am using “For XML Path” to concatenate the results into a single string.

This query is then rolled into an ssrs report as the dataset

The SQL Code – Coalesce & For XML Path

declare @cust_num as custnumtype = ‘test001’

select

cast(co.order_date as Date) order_date

–, co.cust_seq

, coalesce(ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.addr##2 + ‘,’ + ca.addr##3+ ‘,’ + ca.addr##4+ ‘,’ + ca.city+ ‘,’ + ca.county + ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.addr##2 + ‘,’ + ca.addr##3+ ‘,’ + ca.city+ ‘,’ + ca.county + ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.addr##2 + ‘,’ + ca.city+ ‘,’ + ca.county + ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.city+ ‘,’ + ca.county + ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.addr##2 + ‘,’ + ca.addr##3+ ‘,’ + ca.addr##4+ ‘,’ + ca.city + ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.addr##2 + ‘,’ + ca.addr##3+ ‘,’ + ca.city+ ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.addr##2 + ‘,’ + ca.city+ ‘,’ + ca.zip

,ca.name + ‘,’ + ca.addr##1 + ‘,’ + ca.city+ ‘,’ + ca.zip) as address

,c.phone##2 as phone

, c.contact##2 as contact

,(select

cast(cast(coi2.qty_ordered as integer) as nvarchar(99))+ ‘ x ‘+ coi2.item + ‘ ‘ + coi2.description + ‘,’

from coitem as coi2

left join co as co2 on co2.co_num = coi2.co_num

where co.co_num = co2.co_num

for xml path()) as OrderLines

from co

left join custaddr as ca on ca.cust_num = co.cust_num and co.cust_seq = ca.cust_seq

left join customer as c on c.cust_num = ca.cust_num and c.cust_seq = ca.cust_seq

where co.cust_num = @cust_num

order by order_date desc

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.