Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    33

    Unanswered: SQL Optimization - Better way to combine SQLs into one

    Hi I have multile SQLs for insert/updates for different types of vales in same table... Below is the example.. Can anyone let me know the best way to combine all this SQLs into one...May be with example.

    --Insert/Update for TOA=1

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT ID,RID,B.TOA,T_CATSEQ,T_AGSTAT,T_AGEDATE,T_AGINDST AT,T_AGINDPCODE,
    T_AR_FNAME,T_AR_MNAME,T_AR_LNAME,T_AR_LINE1,T_AR_L INE2,T_AR_CITY,
    T_AR_STATE,T_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B
    WHERE A.T_CATSEQ=B.CATSEQ AND RID NOT IN (SELECT RID FROM DB2INST3.EMA));

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT C.ID,C.RID,B.TOA,T_CATSEQ,T_AGSTAT,T_AGEDATE,T_AGI NDSTAT,T_AGINDPCODE,
    T_AR_FNAME,T_AR_MNAME,T_AR_LNAME,T_AR_LINE1,T_AR_L INE2,T_AR_CITY,
    T_AR_STATE,T_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B,DB2INST3.EMA C
    WHERE A.T_CATSEQ=B.CATSEQ AND A.RID=C.RID AND B.TOA=C.TOA);

    --Insert/Update for TOA=2

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT ID,RID,B.TOA,M_CATSEQ,M_AGSTAT,M_AGEDATE,M_AGINDST AT,M_AGINDPCODE,
    M_AR_FNAME,M_AR_MNAME,M_AR_LNAME,M_AR_LINE1,M_AR_L INE2,M_AR_CITY,
    M_AR_STATE,M_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B
    WHERE A.M_CATSEQ=B.CATSEQ AND RID NOT IN (SELECT RID FROM DB2INST3.EMA));

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT C.ID,C.RID,B.TOA,M_CATSEQ,M_AGSTAT,M_AGEDATE,M_AGI NDSTAT,M_AGINDPCODE,
    M_AR_FNAME,M_AR_MNAME,M_AR_LNAME,M_AR_LINE1,M_AR_L INE2,M_AR_CITY,
    M_AR_STATE,M_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B,DB2INST3.EMA C
    WHERE A.M_CATSEQ=B.CATSEQ AND A.RID=C.RID AND B.TOA=C.TOA);

    --Insert/Update for TOA=3

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT ID,RID,B.TOA,M2_CATSEQ,M2_AGSTAT,M2_AGEDATE,M2_AGI NDSTAT,M2_AGINDPCODE,
    M2_AR_FNAME,M2_AR_MNAME,M2_AR_LNAME,M2_AR_LINE1,M2 _AR_LINE2,M2_AR_CITY,
    M2_AR_STATE,M2_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B
    WHERE A.M2_CATSEQ=B.CATSEQ AND RID NOT IN (SELECT RID FROM DB2INST3.EMA));

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT C.ID,C.RID,B.TOA,M2_CATSEQ,M2_AGSTAT,M2_AGEDATE,M2 _AGINDSTAT,M2_AGINDPCODE,
    M2_AR_FNAME,M2_AR_MNAME,M2_AR_LNAME,M2_AR_LINE1,M2 _AR_LINE2,M2_AR_CITY,
    M2_AR_STATE,M2_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B,DB2INST3.EMA C
    WHERE A.M2_CATSEQ=B.CATSEQ AND A.RID=C.RID AND B.TOA=C.TOA);

    --Insert/Update for TOA=4

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT ID,RID,B.TOA,F_CATSEQ,F_AGSTAT,F_AGEDATE,F_AGINDST AT,F_AGINDPCODE,
    F_AR_FNAME,F_AR_MNAME,F_AR_LNAME,F_AR_LINE1,F_AR_L INE2,F_AR_CITY,
    F_AR_STATE,F_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B
    WHERE A.F_CATSEQ=B.CATSEQ AND RID NOT IN (SELECT RID FROM DB2INST3.EMA));

    INSERT INTO sourceschema.AGTAB
    (ID,RID,TOA,CATSEQ,AGSTAT,AGENDDT,AGINDSTAT,AGINDP CODE,AR_FNAME,AR_MNAME,AR_LNAME,AR_LINE1,AR_LINE2,
    AR_CITY,AR_STATE,AR_ZIP,REDET_DATE)
    (SELECT C.ID,C.RID,B.TOA,F_CATSEQ,F_AGSTAT,F_AGEDATE,F_AGI NDSTAT,F_AGINDPCODE,
    F_AR_FNAME,F_AR_MNAME,F_AR_LNAME,F_AR_LINE1,F_AR_L INE2,F_AR_CITY,
    F_AR_STATE,F_AR_ZIP,A.REDET_DATE
    FROM sourceschema.DATA_IVR A, sourceschema.A_REFERENCE B,DB2INST3.EMA C
    WHERE A.F_CATSEQ=B.CATSEQ AND A.RID=C.RID AND B.TOA=C.TOA);

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Stupid question: is there a particular reason why you are asking the same question as in this thread again? http://www.dbforums.com/showthread.p...67#post6266767
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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