Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Can this be done using Oracle?

    I have a query:

    Code:
    SELECT a.loc
         , a.item
         , a.p_abc
      FROM stsc.sku a
         , stsc.item b
         , stsc.loc c
     WHERE a.p_abc IN ('A','B')
       AND b.p_stkclass IN ('010', '010A')
       AND a.p_distchan = 'PDCUS'
       AND a.item = b.item
       AND a.loc = c.loc
       AND c.p_dlrgrp = 'MHC'
       AND a.loc IN (SELECT loc     
      				   FROM stsc.loc
                      WHERE p_dlrgrp = 'MHC' 
                        AND p_mdistatuscode = 'LIVE')  
    ORDER BY a.loc
           , a.item
    that returns data like this:
    Code:
    LOC	ITEM	      P_ABC
    A875	40X1233	        B
    A875	A1-1205X2728	A
    A875	A1-1205Y2729	A
    A875	A1-1205Z2730	A
    A875	A1199P1394	A
    A876	A1-1205X2728	A
    A876	A1-1205Y2729	B
    A876	A1-1205Z2730	B
    A876	A1199P1394	B
    A877	A1-1205X2728	B
    A877	A1-1205Y2729	A
    A877	A1199P1394	B
    C520	1244Q1473	B
    C520	2208X440	B
    C520	A1-1205A2731	A
    Here's what I want to do. If P_ABC = 'A' then run this query:

    Code:
    SELECT ROUND((CEIL((SUM (ROUND (a.totfcst)) - (b.oh + NVL(c.qty,0))) /  d.p_pdc_spq) * d.p_pdc_spq) * b.p_dlrnet) qty 
        FROM stsc.dfutoskufcst a
    	   , stsc.sku b
    	   , stsc.schedrcpts c
    	   , stsc.item d
       WHERE a.skuloc = 'THE LOC FROM THE MAIN QUERY'
         AND a.item = 'THE ITEM FROM THE MAIN QUERY'
         AND a.startdate = ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1)
         AND a.item = b.item
    	 AND a.skuloc = b.loc
    	 AND b.item = c.item(+)
    	 AND b.loc = c.loc(+)
    	 AND a.item = d.item					   
    GROUP BY a.item
           , a.skuloc
    	   , b.oh
    	   , c.qty
    	   , d.p_pdc_spq
    	   , b.p_dlrnet
    and then place the QTY in the results like this:

    [CODE]
    Code:
    LOC	ITEM	      P_ABC       QTY
    A875	40X1233	        B         480
    A875	A1-1205X2728	A         847
    If it's 'A' then run a different query and update the qty as above. I have no idea if this can be done.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can this be done using Oracle?
    Yes, using PL/SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Just put the second query into the from clause of the first query and then outer join to it. You will also have to add the condition to the subquery so that b.p_abc='A'.

    Alan

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > FROM stsc.sku a
    > , stsc.item b
    > , stsc.loc c
    You should remove stsc.item b & stsc.loc c out of the FROM clause, 'cuz neither returns an fields to the SELECT clause.
    Performance will improve if you do so.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •