Joined: 29 Mar 2005 Posts: 34 Topics: 18 Location: Canada
Posted: Tue Jul 08, 2008 12:25 am Post subject: Selecting MAXIMUM and Minimum Value in DB2
Hi all ,
I need a single row having the maximum of UNIT_COST and Minimum of UNIT_COST.
As I have two select clause. I am getting two seperate rows. Please helkp me in thsi regard.
Code:
SELECT F.CU_ITEM_NUMBER
, F.TU_SUFFIX
, MIN(F.PO_UNIT_COST)
, MAX(F.PO_UNIT_COST)
FROM WW.TPOHEAD E ,
WW.TPOLINE F ,
WW.CORP_ITEM A,
WW.CORP_ITEM_DESC B,
WW.TSTATCU C,
GROUP BY
F.CU_ITEM_NUMBER ,
F.TU_SUFFIX
UNION ALL
SELECT E.CU_ITEM_NUMBER
, E.TU_SUFFIX
, MIN(E.PO_UNIT_COST)
, MAX(E.PO_UNIT_COST)
FROM WW.TPOLINE E
, WW.TPODELV F ,
WW.CORP_ITEM A,
WW.CORP_ITEM_DESC B,
WW.TSTATCU C,
WW.TSTAVTU D
GROUP BY
E.CU_ITEM_NUMBER ,
E.TU_SUFFIX
WITH UR;
_________________ For any type of complex problems there will be multiple easiest solutions
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Tue Jul 08, 2008 1:50 am Post subject:
well you can continue to critique posts to this thread, or you can show us the complete sql. _________________ Dick Brenholtz
American living in Varel, Germany
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Tue Jul 08, 2008 11:42 am Post subject:
You need to nest your SELECT, and SELECT the MIN and MAX from that. I'm using DB2 V8 for z/OS. Here's what it looked like when I set it up using a temp table:
Code:
SELECT T_ITEM_NBR
,MIN(T_MIN_UNIT_COST)
,MAX(T_MAX_UNIT_COST)
FROM (
SELECT CU_ITEM_NBR AS T_ITEM_NBR
,MIN(CU_UNIT_COST) AS T_MIN_UNIT_COST
,MAX(CU_UNIT_COST) AS T_MAX_UNIT_COST
FROM SESSION.JS_ITEM_TBL1
GROUP BY CU_ITEM_NBR
UNION
SELECT CU_ITEM_NBR AS T_ITEM_NBR
,MIN(CU_UNIT_COST) AS T_MIN_UNIT_COST
,MAX(CU_UNIT_COST) AS T_MAX_UNIT_COST
FROM SESSION.JS_ITEM_TBL2
GROUP BY CU_ITEM_NBR
) AS TMP_TBL
GROUP BY T_ITEM_NBR
ORDER BY 1
FOR FETCH ONLY WITH UR;
Joined: 29 Mar 2005 Posts: 34 Topics: 18 Location: Canada
Posted: Tue Jul 08, 2008 5:40 pm Post subject:
HI,
My Query is
Code:
SELECT F.CU_ITEM_NUMBER
, F.TU_SUFFIX
, MIN(F.PO_UNIT_COST)
, MAX(F.PO_UNIT_COST)
FROM WW.TPOHEAD E ,
WW.TPOLINE F ,
WW.CORP_ITEM A,
WW.CORP_ITEM_DESC B,
WW.TSTATCU C,
WW.TSTAVTU D
WHERE E.WAREHOUSE_NO LIKE('1%')
AND E.DATE_RAISED = CURRENT DATE
AND E.DELIVERY_LOC_SW = 'N'
AND F.WAREHOUSE_NO = E.WAREHOUSE_NO
AND F.PURCHASE_ORDER_NO = E.PURCHASE_ORDER_NO
AND F.CU_ITEM_NUMBER = C.REF_NO
AND F.TU_SUFFIX = D.SUFFIX
AND F.QTU_SUFFIX = ' '
AND F.DELIVERY_LOC_SW = 'N'
AND F.PO_UNIT_COST = 0
AND A.SUBSYSTEM_TYPE = 'P'
AND A.ITEM_ID = B.ITEM_ID
AND C.REF_NO = B.ITEM_ID
AND D.REF_NO = B.ITEM_ID
AND B.DESC_NO = 3
AND B.DESC_SUB_NO = 0
AND C.STATE_CODE = '01'
AND D.STATE_CODE = C.STATE_CODE
AND ((C.DELIST_DATE IS NULL AND
C.RELIST_DATE IS NULL ) OR
(C.DELIST_DATE IS NOT NULL AND
C.RELIST_DATE < CURRENT DATE))
AND ((D.DELIST_DATE IS NULL AND
D.RELIST_DATE IS NULL ) OR
(D.DELIST_DATE IS NOT NULL AND
D.RELIST_DATE < CURRENT DATE))
GROUP BY
F.CU_ITEM_NUMBER ,
F.TU_SUFFIX
UNION ALL
SELECT E.CU_ITEM_NUMBER
, E.TU_SUFFIX
, MIN(E.PO_UNIT_COST)
, MAX(E.PO_UNIT_COST)
FROM WW.TPOLINE E
, WW.TPODELV F ,
WW.CORP_ITEM A,
WW.CORP_ITEM_DESC B,
WW.TSTATCU C,
WW.TSTAVTU D
WHERE E.CU_ITEM_NUMBER = C.REF_NO
AND E.TU_SUFFIX = D.SUFFIX
AND E.QTU_SUFFIX = ' '
AND E.DELIVERY_LOC_SW = 'Y'
AND E.PO_UNIT_COST = 0
AND E.WAREHOUSE_NO LIKE ('1%')
AND F.WAREHOUSE_NO = E.WAREHOUSE_NO
AND F.PURCHASE_ORDER_NO = E.PURCHASE_ORDER_NO
AND F.PO_LINE_NO = E.PO_LINE_NO
AND F.EXP_DEL_DATE = CURRENT DATE
-- AND F.DELIVERY_LOCATION = :DCLTPODELV.DELIVERY
AND A.SUBSYSTEM_TYPE = 'P'
AND A.ITEM_ID = B.ITEM_ID
AND C.REF_NO = B.ITEM_ID
AND D.REF_NO = B.ITEM_ID
AND B.DESC_NO = 3
AND B.DESC_SUB_NO = 0
AND C.STATE_CODE = '01'
AND D.STATE_CODE = C.STATE_CODE
AND ((C.DELIST_DATE IS NULL AND
C.RELIST_DATE IS NULL ) OR
(C.DELIST_DATE IS NOT NULL AND
C.RELIST_DATE < CURRENT DATE))
AND ((D.DELIST_DATE IS NULL AND
D.RELIST_DATE IS NULL ) OR
(D.DELIST_DATE IS NOT NULL AND
D.RELIST_DATE < CURRENT DATE))
GROUP BY
E.CU_ITEM_NUMBER ,
E.TU_SUFFIX
WITH UR;
*************
UNION ALL
SELECT QTY_ORDERED
, PO_UNIT_COST
FROM TPOHEAD A
, TPOLINE B
, TPODELV C
WHERE A.DELIVERY_LOC_SW = 'Y'
AND A.WAREHOUSE_NO = :DCLTPOHEAD.WAREHOUS
AND A.WAREHOUSE_NO = B.WAREHOUSE_NO
AND B.PURCHASE_ORDER_NO = A.PURCHASE_ORDER_NO
AND B.CU_ITEM_NUMBER = :DCLTPOLINE.CU-ITEM-
AND B.TU_SUFFIX = :DCLTPOLINE.TU-SUFFI
AND B.QTU_SUFFIX = :DCLTPOLINE.QTU-SUFF
AND B.DELIVERY_LOC_SW = 'N'
AND B.PO_UNIT_COST = 0
AND C.WAREHOUSE_NO = A.WAREHOUSE_NO
AND C.WAREHOUSE_NO = B.WAREHOUSE_NO
AND C.PURCHASE_ORDER_NO = A.PURCHASE_ORDER_NO
AND C.PURCHASE_ORDER_NO = B.PURCHASE_ORDER_NO
AND C.PO_LINE_NO = 0
AND C.EXP_DEL_DATE = :DCLTPODELV.EXP-DEL-
AND C.DELIVERY_LOCATION = :DCLTPODELV.DELIVERY
*******
SELECT DISTINCT
-- WAREHOUSE_NO
CU_ITEM_NUMBER
, TU_SUFFIX
-- , START_TIMESTAMP
, MIN(STD_ISSUE_COST)
, MIN(SELL_PRICE)
, MIN(GROSS_PROFIT)
FROM WW.TISSUE ,
WW.CORP_ITEM A,
WW.CORP_ITEM_DESC B,
WW.TSTATCU C,
WW.TSTAVTU D
WHERE
START_TIMESTAMP <= CURRENT TIMESTAMP
AND END_TIMESTAMP >= CURRENT TIMESTAMP
AND QTU_SUFFIX = ' '
AND WAREHOUSE_NO LIKE('1%')
AND CU_ITEM_NUMBER = C.REF_NO
AND TU_SUFFIX = D.SUFFIX
AND A.SUBSYSTEM_TYPE = 'P'
AND A.ITEM_ID = B.ITEM_ID
AND C.REF_NO = B.ITEM_ID
AND D.REF_NO = B.ITEM_ID
AND B.DESC_NO = 3
AND B.DESC_SUB_NO = 0
AND C.STATE_CODE = '01'
AND D.STATE_CODE = C.STATE_CODE
AND ((C.DELIST_DATE IS NULL AND
C.RELIST_DATE IS NULL ) OR
(C.DELIST_DATE IS NOT NULL AND
C.RELIST_DATE < CURRENT DATE))
AND ((D.DELIST_DATE IS NULL AND
D.RELIST_DATE IS NULL ) OR
(D.DELIST_DATE IS NOT NULL AND
D.RELIST_DATE < CURRENT DATE))
GROUP BY
CU_ITEM_NUMBER
, TU_SUFFIX
-- , START_TIMESTAMP
-- , STD_ISSUE_COST
-- , SELL_PRICE
WITH UR;
********
SELECT B.CU_ITEM_NUMBER
, B.TU_SUFFIX
,PO_UNIT_COST
,PO_UNIT_COST
FROM WW.TPOHEAD A
, WW.TPOLINE B
, WW.TPODELV C
WHERE A.DELIVERY_LOC_SW = 'Y'
AND A.WAREHOUSE_NO = B.WAREHOUSE_NO
AND B.PURCHASE_ORDER_NO = A.PURCHASE_ORDER_NO
AND B.CU_ITEM_NUMBER = '124011'
AND B.QTU_SUFFIX = ' '
AND B.DELIVERY_LOC_SW = 'N'
AND B.PO_UNIT_COST = 0
AND C.WAREHOUSE_NO = A.WAREHOUSE_NO
AND C.WAREHOUSE_NO = B.WAREHOUSE_NO
AND C.PURCHASE_ORDER_NO = A.PURCHASE_ORDER_NO
AND C.PURCHASE_ORDER_NO = B.PURCHASE_ORDER_NO
AND A.DATE_RAISED = CURRENT DATE
AND C.PO_LINE_NO = 0
AND A.WAREHOUSE_NO LIKE('1%')
WITH UR;
SELECT B.CU_ITEM_NUMBER
, B.TU_SUFFIX
,MAX(PO_UNIT_COST) MAX_COST
,MIN(PO_UNIT_COST) MIN_COST
FROM WW.TPOHEAD A
, WW.TPOLINE B
-- , WW.TPODELV C
WHERE A.DELIVERY_LOC_SW = 'Y'
AND A.WAREHOUSE_NO = B.WAREHOUSE_NO
AND B.PURCHASE_ORDER_NO = A.PURCHASE_ORDER_NO
AND B.QTU_SUFFIX = ' '
AND B.DELIVERY_LOC_SW = 'N'
AND B.PO_UNIT_COST = 0
--AND C.WAREHOUSE_NO = A.WAREHOUSE_NO
--AND C.WAREHOUSE_NO = B.WAREHOUSE_NO
--AND C.PURCHASE_ORDER_NO = A.PURCHASE_ORDER_NO
--AND C.PURCHASE_ORDER_NO = B.PURCHASE_ORDER_NO
AND A.DATE_RAISED = CURRENT DATE
--AND C.PO_LINE_NO = 0
AND A.WAREHOUSE_NO LIKE('2%')
GROUP BY B.CU_ITEM_NUMBER
, B.TU_SUFFIX
ORDER BY 1
WITH UR;
**********
SELECT DISTINCT CU_ITEM_NUMBER
, TU_SUFFIX
FROM WW.TISSUE
WHERE CU_ITEM_NUMBER IN (
'102867',
'102876',
'102877',
'102878',
'102879',
'102880',
'102881',
'102882',
'102883',
'102884',
'102885',
'102886')
AND START_TIMESTAMP <= CURRENT TIMESTAMP
AND END_TIMESTAMP >= CURRENT TIMESTAMP
AND QTU_SUFFIX = ' '
WITH UR;
SELECT DISTINCT
C.REF_NO
FROM
WW.CORP_ITEM A,
WW.CORP_ITEM_DESC B,
WW.TSTATCU C,
WW.TSTAVTU D
WHERE
A.SUBSYSTEM_TYPE = 'P'
AND A.ITEM_ID = B.ITEM_ID
AND C.REF_NO = B.ITEM_ID
AND D.REF_NO = B.ITEM_ID
AND B.DESC_NO = 3
AND B.DESC_SUB_NO = 0
AND C.STATE_CODE = '01'
AND D.STATE_CODE = C.STATE_CODE
AND ((C.DELIST_DATE IS NULL AND
C.RELIST_DATE IS NULL ) OR
(C.DELIST_DATE IS NOT NULL AND
C.RELIST_DATE < CURRENT DATE))
AND ((D.DELIST_DATE IS NULL AND
D.RELIST_DATE IS NULL ) OR
(D.DELIST_DATE IS NOT NULL AND
D.RELIST_DATE < CURRENT DATE))
WITH UR;
_________________ For any type of complex problems there will be multiple easiest solutions
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
