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