Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    8

    Unanswered: SQL Stored Procedure problem

    Hi,

    Let me start by saying I am an Oracle newbie and any help would be greatly appreciated.

    I inherited most of this code. Its main function is to add the pallet field and group by source_id. The only way I can come up to do this is below. The problem is that with all the MAX functions in the aggregate the incidental columns like source name screw up the rest of the aggregate. Is there another way to write this where it will sum pallets and group by source_id and still accomplish the pallet 300 and pallet_type criteria.

    Thanks in advance,

    John

    Code:
    CREATE OR REPLACE PROCEDURE SP_PALLETOVER300 AS
    BEGIN
    DECLARE CURSOR c1 IS
    
    SELECT a.source_id
         , b.source_name
         , a.pallet 
         , b.city 
         , b.state 
         , b.pallet_type 
         , b.dest_id 
         , b.dest_name
         , b.contact_fname
         , b.contact_lname
         , b.contact_phone1
         , b.contact_email
         , b.dest_fname
         , b.dest_lname
         , b.dest_phone1
         , b.dest_email
    FROM ( 
           SELECT SUM(pm.pallet) AS pallet
                , pm.source_id
           FROM glg01.pallet_master pm
              , glg01.pallet_master_contact pc
           WHERE pm.source_id = pc.source_id(+)
             AND pallet_type = 'WHITE'
           HAVING sum(pm.pallet) > 300
    	   GROUP BY pm.source_id
         ) a
       , ( 
           SELECT DISTINCT pm.source_id as source_id 
                , MAX(pm.source_name) as source_name
                , MAX(pm.city) as city 
                , MAX(pm.state) as state 
                , pm.pallet_type 
                , MAX(pm.dest_id) as dest_id
                , MAX(pm.dest_name) as dest_name
                , MAX(pc.contact_fname) as contact_fname
                , MAX(pc.contact_lname) as contact_lname
                , MAX(pc.contact_phone1) as contact_phone1
                , MAX(pc.contact_email) as contact_email
                , MAX(pc.dest_fname) as dest_fname
                , MAX(pc.dest_lname) as dest_lname
                , MAX(pc.dest_phone1) as dest_phone1
                , MAX(pc.dest_email) as dest_email
           FROM glg01.pallet_master pm
              , glg01.pallet_master_contact pc
           WHERE pm.source_id = pc.source_id
             AND pallet_type = 'WHITE'
           GROUP BY pm.source_id
           	      , pm.PALLET_TYPE 
         ) b	
    WHERE a.source_id = b.source_id
    ORDER BY source_id;
    BEGIN
       DELETE FROM PALLET_OVER_300;
       COMMIT;
       FOR c1_rec IN C1 LOOP
    		INSERT INTO PALLET_OVER_300
    		(PALLET,
    		SOURCE_ID,
    		SOURCE_NAME,
    		CITY,
    		STATE,
    		PALLET_TYPE,
    		DEST_ID,
    		DEST_NAME,
    		CONTACT_FNAME,
    		CONTACT_LNAME,
    		CONTACT_PHONE1,
    		CONTACT_EMAIL,
    		DEST_FNAME,
    		DEST_LNAME,
    		DEST_PHONE1,
    		DEST_EMAIL)
    		VALUES
    		(c1_rec.PALLET,
    		c1_rec.SOURCE_ID,
    		c1_rec.SOURCE_NAME,
    		c1_rec.CITY,
    		c1_rec.STATE,
    		c1_rec.PALLET_TYPE,
    		c1_rec.DEST_ID,
    		c1_rec.DEST_NAME,
    		c1_rec.CONTACT_FNAME,
    		c1_rec.CONTACT_LNAME,
    		c1_rec.CONTACT_PHONE1,
    		c1_rec.CONTACT_EMAIL,
    		c1_rec.DEST_FNAME,
    		c1_rec.DEST_LNAME,
    		c1_rec.DEST_PHONE1,
    		c1_rec.DEST_EMAIL);
       END LOOP;
       COMMIT;
    
    END;
     EXCEPTION
            WHEN NO_DATA_FOUND THEN
    	        NULL;
    	WHEN OTHERS THEN
    		ROLLBACK;
    		RAISE;
    END SP_PALLETOVER300;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there another way to write this where it will sum pallets and group by >source_id and still accomplish the pallet 300 and pallet_type criteria.
    HUH?
    I guess I can't read.
    EXACTLY what are the " pallet 300 and pallet_type criteria."?
    You admit the SQL does not accomplish what you need, so why post it & confuse folks?
    The best way to develop "complex" SQL is to construct "simple" & tested SQL pieces before trying to combine them.
    One inelegant way to handle MAX values, is to populate a GTT with the "max" rows first, then join the GTT against the original table(s).
    HTH & YMMV!
    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
    Jan 2004
    Posts
    492
    Never understand your attitude quite honestly - Only you can pick apart every sentence somebody writes.

    It is QUITE apparent that the pallet 300 criteria refers to the line in inline view a (HAVING sum(pm.pallet) > 300).

    And could it be that pallet_type is taken from b (AND pallet_type = 'WHITE')? SUCH A STRETCH OF THE IMAGINATION I KNOW!

    For me, when someone announces themselves as a newcomer, I am a lot less likely to jump down their throat at a vague entry. Not sure why its the opposite with you?

    Now onto the post-

    Not exactly sure why you cannot combine the inline views into 1 - but, you could also try putting the max function on the outside - so for inline view b, take out the max functions, and then apply the max to the main select statement.

    Just a stab in the dark
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Never understand your attitude quite honestly - Only you can pick apart every sentence somebody writes.
    Ambiguous specifications results in code not producing desired results;
    because "desired" can be subjective.
    Without clear, concise & complete objectives, the odds of successful impementation on the 1st attempt are greatly reduced.
    If you subcribe to the s/w development philosophy of - Ready, Fire, AIM,
    then I feel sorry for your manager & company.
    I much prefer to have a fighting chance of delivering 100% functioning code on the 1st delivery even if I spend 50% of my time nailing down the specifications.

    HTH & YMMV
    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.

  5. #5
    Join Date
    Sep 2004
    Posts
    8
    I like ready, fire, aim. Especially since business people rarely know what they want. Anyway, I figured what I'm looking to do and will just write a select statement based on a select list.

    Thanks for your help ss659. Anacedent I think you need a nap.

    Quote Originally Posted by anacedent
    >Never understand your attitude quite honestly - Only you can pick apart every sentence somebody writes.
    Ambiguous specifications results in code not producing desired results;
    because "desired" can be subjective.
    Without clear, concise & complete objectives, the odds of successful impementation on the 1st attempt are greatly reduced.
    If you subcribe to the s/w development philosophy of - Ready, Fire, AIM,
    then I feel sorry for your manager & company.
    I much prefer to have a fighting chance of delivering 100% functioning code on the 1st delivery even if I spend 50% of my time nailing down the specifications.

    HTH & YMMV

Posting Permissions

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