RE: Run SQL Scripts - '+++++' in results

Glenn, yes, it is the Commission column that's giving me fits. And yes I have tried it without any Casts; that's actually where I started from.

I took your suggestion of starting from the bottom and doing a Select * or Select cte_Comms.*. That didn't really show me anything - all the supporting data looks fine.

Nor does working it from the top down - all the data looks fine to me until I get to the actual Commission column.

What's really driving me nuts is that the problem doesn't occur on every row. I'll keep banging my head up against this brick wall.

Thanks
Bob

-----Original Message-----
From: Glenn Gundermann

Hi Bob,

Which column exactly is causing the problem? Is it this one?
cast(cte_Comms.Commission as char(50) ccsid 37) as "Commission", Have you tried it without the cast?

When I have troubles with a cte and have to troubleshoot, I sometimes start at the top and sometimes start from the bottom.

Eg. when starting from the bottom:
- Replace the 12 columns in the last select with *.
- Replace the 12 columns in the last select with cte_Comms.*
- continue working up

Eg. when starting from the top:
- after the 1st cte: select cte_GrossSales.* ;
- if that looks okay, after the 2nd cte: select cte_Returns.* ;
- etc.

This is one of those things that it would be nice to be on the same system to troubleshoot.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144


On 1 November 2016 at 09:02, Bob Cagle <bcagle@xxxxxxxxxxx> wrote:

This script is calculating Gross Sales, Returns, Customer Discounts,
Net Sales, and Commissions. The problem column is the Commissions.
I've tried casting it to CCSID 37 with no change in the results.

This is pulling data from JD Edwards, which stores amounts as 15,0 and
dates as Julian, so that's why there's some odd division going on and
Julian data range selections.

Here it is:

with cte_GrossSales as (
Select sdpa8 as Customer, sdlitm as Item, sum(sduorg) as
GrossQty, sum(sdaexp/100) as GrossSales, sum(sdecst/100) as Cost
from lynkdta.f42119
where sddcto not in ('CO', 'CI', 'ST') and
sdlnty <> 'F' and
sdpa8 <> 846 and
sddgl between 116245 and 116274
group by sdpa8, sdlitm
order by sdpa8, sdlitm
),

cte_Returns as (
Select sdpa8 as Customer, sdlitm as Item, sum(sduorg) as
ReturnQty, sum(sdaexp/100) as Returns
from lynkdta.f42119
where sddcto in ('CO', 'CI') and
sdlnty <> 'F' and
sdpa8 <> 846 and
sddgl between 116245 and 116274
group by sdpa8, sdlitm
order by sdpa8, sdlitm
),

cte_DiscountRate as (
Select OAAN8 as Customer, sum(OADISC) as DiscountRate
from lynkdta.Mrgnex01pf
group by OAAN8
order by OAAN8
),

cte_Discount as (
Select cte_GrossSales.Customer,
cte_GrossSales.Item,
sum(cte_GrossSales.GrossSales *
cte_DiscountRate.DiscountRate/100) as Discount
from cte_GrossSales
left join cte_DiscountRate on cte_GrossSales.Customer=cte_
DiscountRate.Customer
group by cte_GrossSales.Customer, cte_GrossSales.Item
order by cte_GrossSales.Customer, cte_GrossSales.Item
),

cte_NetSales as (
Select cte_GrossSales.Customer,
cte_GrossSales.Item,
sum(cte_GrossSales.GrossSales -
(case when cte_Returns.Returns is null then 0 else
abs(cte_Returns.Returns) end) -
(case when cte_Discount.Discount is null then 0
else cte_Discount.Discount end)) as NetSales
from cte_GrossSales
left join cte_Returns on cte_GrossSales.Customer=cte_Returns.Customer
and
cte_GrossSales.Item=cte_Returns.Item
left join cte_Discount on cte_GrossSales.Customer=cte_Discount.Customer
and
cte_GrossSales.Item=cte_
Discount.Item
group by cte_GrossSales.Customer, cte_GrossSales.Item
order by cte_GrossSales.Customer, cte_GrossSales.Item
),

cte_CommRate as (
Select A5AN8 as Customer, (A5CMR1 + A5CMR2)/1000 as CommRate
from lynkdta.F0301
order by A5AN8
),

cte_Comms as (
Select cte_NetSales.Customer,
cte_NetSales.Item,
sum(cte_NetSales.NetSales *
(cte_CommRate.CommRate/100)) as Commission
from cte_NetSales
left join cte_CommRate on cte_NetSales.Customer=cte_
CommRate.Customer
group by cte_NetSales.Customer, cte_NetSales.Item
order by cte_NetSales.Customer, cte_NetSales.Item
)

select cte_GrossSales.Customer as "Customer",
cte_GrossSales.Item as "Item",
cte_GrossSales.GrossQty as "Gross Qty",
cast(cte_GrossSales.GrossSales as dec(9,2)) as "Gross Sales",
cte_Returns.ReturnQty as "Return Qty",
cast(cte_Returns.Returns as dec(9,2)) as "Returns",
cast(cte_DiscountRate.DiscountRate as dec(5,2)) as
"Discount Percent",
cast(cte_Discount.Discount as dec(9,2)) as "Discount",
cast(cte_NetSales.NetSales as dec(9,2)) as "Net Sales",
cast(cte_CommRate.CommRate as dec(5,2)) as "Comm Rate",
cast(cte_Comms.Commission as char(50) ccsid 37) as
"Commission",
cast(cte_GrossSales.Cost as dec(9,2)) as "Cost"

from cte_GrossSales
left join cte_Returns on cte_GrossSales.Customer=cte_Returns.Customer
and
cte_GrossSales.Item=cte_Returns.Item
left join cte_DiscountRate on cte_GrossSales.Customer=cte_
DiscountRate.Customer
left join cte_Discount on cte_GrossSales.Customer=cte_Discount.Customer
and
cte_GrossSales.Item=cte_Discount.Item
left join cte_NetSales on cte_GrossSales.Customer=cte_NetSales.Customer
and
cte_GrossSales.Item=cte_NetSales.Item
left join cte_CommRate on cte_GrossSales.Customer=cte_CommRate.Customer
left join cte_Comms on cte_GrossSales.Customer=cte_Comms.Customer and
cte_GrossSales.Item=cte_Comms.Item
where cte_GrossSales.GrossSales <> 0 and cte_CommRate.CommRate <> 0
order by cte_GrossSales.Customer, cte_GrossSales.Item


Thanks

Bob Cagle
IT Manager
Lynk