Re: SQL Syntax Question

Ryan,

I am troubled by the apparent design of the table iv00102. The rules that I
infer are:

1. Primary Key = Itemnmbr, locncode
2. locncode has a... CONSTRAINT locncodeOK CHECK (locncode IN ('', 'MAIN',
'WC-HD01'))
3. ((qtyonhnd-qytalloc) WHERE locncode = '') = (SUM(qtyonhnd-qtyalloc)
WHERE locncode <> '')
4. If 3 is true then locncode = '' rows are duplicate updated with any
changes made to the locncode <> '' rows.
5. Without a code change, another locncode cannot be added

Therefore, something like:

SELECT itemnmbr,
SUM(CASE (locncode) WHEN ('MAIN') THEN (qtyonhand-qtyalloc) ELSE 0)
AS "Main Qty Avail",
SUM(CASE (locncode) WHEN ('WC-HD01') THEN (qtyonhand-qtyalloc) ELSE 0)
AS "WC-HD01 Qty Avail",
SUM(CASE (locncode) WHEN ('') THEN (qtyonhand-qtyalloc) ELSE 0)
AS "Total"
FROM iv00102
GROUP BY itemnmbr

This code depends on so many things being true that I consider it quite
fragile, since any system change that invalidates the points above will
cause this to fail. Consider some redesign, if it is within your job scope.

RLF

"Ryan Mcbee" <RyanMcbee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C81A0B18-296D-4757-AAF4-2D376D190CF0@xxxxxxxxxxxxxxxx
I have the following syntax:
"select itemnmbr, locncode, (qtyonhnd-atyalloc) as "QTY Available Main"
from iv00102 "

The results look like;

Itemnmbr locncode qty available main
121 944-16 90.00000
121 944-16 MAIN -10.00000
121 944-16 WC-HD01 100.00000

I want to create results that look like this

Itemnmbr Main Qty Available WC-HDO1 Qty
Available
Total
121 944-16 -10.00000
100.00000
90.00

Any ideas how to change the syntax to get it this way?

Thanks,

Ryan




.