Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    37

    Unanswered: Sql to Db2 scripts

    Edit: Just noticed the ansi sql section. Sorry if this should go there.

    My workplace supports as400, but yet, no one seems to know how to convert scripts here, especially when they become more advanced. So far, I have been able to convert 16 scripts from help of online resources. I didn't write the sql scripts (fyi). I know sql, but not the best. So, if any suggestions on how to rewrite the sql to make it cleaner and easier to convert to db2, would be great.

    I am using System I Navigator to verify and create the script on the as400.

    I know (dbo., [, ], as) are not allowed. Parenthesis need to go around the parameters, and language sql, modifies sql data, and result sets go afterwards. I know to declare the cursors. And finish off setting variables and if-statements with a semicolon.



    The main issue with this one is the if-statement and how to create the cursor to work for it:

    Code:
    CREATE PROCEDURE [dbo].[stsiqry_pcmsda_filingcases]
      @iCSCPCODE varchar(30),
      @iSearchFlag varchar(30)
    as
    begin
      --declare @cscpcode varchar(100)
      drop table Tmp_DAI
      drop table Tmp_DAI1
      drop table Tmp_DAI2
    
      --select @cscpcode = CSCPCODE  from CMUSCPP where CSUSER = @iUser
      --print @cscpcode
      
      if @iSearchFlag = 'WEB'
        select a.lastname,a.frstname,a.midname,a.CITATION,convert(varchar,NULLIF(a.CPDOB, '1/1/1753'),101) AS CPDOB,convert(varchar,NULLIF(a.CSARRDT, '1/1/1753'),101) AS CSARRDT,b.warantno,b.casenbr,b.appcode,b.cmscase ,
             b.PROSCODE
        into Tmp_DAI1
        from CMCPTYP a , CMCASEP b
        where a.CSCPCODE= @iCSCPCODE  and a.cptypint = 'DF' and A.appcode in (select appcode from CMAAGYP where appsysid = 'PCMS') 
        and a.appcode = b.appcode and a.cmscase = b.cmscase
        and exists(
          select 1 from CMLIDXP c
    		left outer join cmliexp d  on c.appcode = d.appcode and c.cmscase = d.cmscase and c.casptysq = d.casptysq and c.ltrseq = d.ltrseq 
    		where isnull(d.lieflg2,'N') = 'N' and c.stscde='RFD' and a.appcode=c.appcode and 
    		 (a.cmscase=c.cmscase or c.cmscase=(select mstcseq from CMMCRCP d where a.appcode=d.appcode and a.cmscase=d.cmscase)
    	))
    	order by a.APPCODE
      else
        select a.lastname,a.frstname,a.midname,a.CITATION,convert(varchar,NULLIF(a.CPDOB, '1/1/1753'),101) AS CPDOB,convert(varchar,NULLIF(a.CSARRDT, '1/1/1753'),101) AS CSARRDT,b.warantno,b.casenbr,b.appcode,b.cmscase ,
             b.PROSCODE
        into Tmp_DAI1
        from CMCPTYP a , CMCASEP b
        where a.CSCPCODE= @iCSCPCODE  and a.cptypint = 'DF' and A.appcode in (select appcode from CMAAGYP where appsysid = 'PCMS') 
        and a.appcode = b.appcode and a.cmscase = b.cmscase
        and exists(
          select 1 from CMLIDXP c
    		left outer join cmliexp d  on c.appcode = d.appcode and c.cmscase = d.cmscase and c.casptysq = d.casptysq and c.ltrseq = d.ltrseq 
    		where d.liamt1 = 0 and c.stscde='RFD' and a.appcode=c.appcode and 
    		 (a.cmscase=c.cmscase or c.cmscase=(select mstcseq from CMMCRCP d where a.appcode=d.appcode and a.cmscase=d.cmscase)
    	))
    	order by a.APPCODE	
    
    select t.*,p.ProsLast,isnull(p.ProsFrst,'') as ProsFrst,isnull(p.ProsMid,'') as ProsMid 
    into Tmp_DAI2
    from Tmp_DAI1 t left outer join PTPROSP p 
    on t.PROSCODE=p.PROSCODE
    
    select t.*,isnull(c.CPChgDsc,'') as CPChgDsc
    into Tmp_DAI
    from Tmp_DAI2 t left outer join CMCPCHP c
    on t.appcode = c.appcode and t.cmscase = c.cmscase
    where c.PM46gA = 'Y'
    
    select * from Tmp_DAI
    
    
    end

    The second issue is the set of variables inside a select statement. Db2 doesn't like it apparently.

    Code:
    Select @warantno = Ltrim(Rtrim(WARANTNO)),@casenbr = Ltrim(Rtrim(CASENBR)) From CMCASEP Where APPCODE = @iAppcode and CMSCASE = @iCmscase

    Any help would be appreciated. Thanks.
    Last edited by zaryk; 01-30-12 at 16:08.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    You *never* need "@" in front of SQL PL variables.
    They must all be declared, though, in the "declaration section" (top of the procedure body).
    And of course add ";" to end SQL statements inside the procedure body.

    And "Select warantno = Ltrim..... FROM ..." will become something like "SELECT Ltrim... INTO warantno FROM ...".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that all select statements in the procedure could be combined into one select statement
    and temporary tables might be not necessary.

    (1) I compared first select statement and second select statement in if statement.
    The difference seems a predicate in exists subselect, like...
    where isnull(d.lieflg2 , 'N') = 'N'
    and
    where d.liamt1 = 0

    If my guess was right,
    both select statements might be able to be combined into one select statement by modifying like...
    Code:
               where
                 (   @iSearchFlag =  'WEB'
                     AND isnull(d.lieflg2 , 'N') = 'N'
                  OR @iSearchFlag <> 'WEB'
                     AND d.liamt1  = 0
                 )
    (2) The following select statements could be conbined into one, like...
    Code:
    select *
     from  (
           select t.*
                , isnull(c.CPChgDsc , '') as CPChgDsc
            from  (
                  select t.*
                       , p.ProsLast
                       , isnull(p.ProsFrst , '') as ProsFrst
                       , isnull(p.ProsMid  , '') as ProsMid 
                   from  Tmp_DAI1 t
                   left  outer join
                         PTPROSP  p 
                     on  t.PROSCODE = p.PROSCODE
                  ) t
            /*left  outer join*/INNER JOIN   /* because: where c.PM46gA = 'Y' */
                  CMCPCHP c
              on  t.appcode = c.appcode
              and t.cmscase = c.cmscase
            where c.PM46gA = 'Y'
           ) t
    And further simplified, like...
    Code:
    select t.*
         , p.ProsLast
         , isnull(p.ProsFrst , '') as ProsFrst
         , isnull(p.ProsMid  , '') as ProsMid 
         , isnull(c.CPChgDsc , '') as CPChgDsc
     from  Tmp_DAI1 t
     left  outer join
           PTPROSP  p 
       on  t.PROSCODE = p.PROSCODE
     INNER JOIN
           CMCPCHP c
       on  t.appcode = c.appcode
       and t.cmscase = c.cmscase
       AND c.PM46gA = 'Y'

    (3) Combine (1) and (2) into one select statement.

    isnull and convert are not supported in DB2.
    I replced them by IFNULL and CHAR(... , USA).

    Code:
    select a.lastname
         , a.frstname
         , a.midname
         , a.CITATION
         , CHAR( DATE( NULLIF(a.CPDOB   , '1/1/1753') ) , USA ) AS CPDOB
         , CHAR( DATE( NULLIF(a.CSARRDT , '1/1/1753') ) , USA ) AS CSARRDT
         , b.warantno
         , b.casenbr
         , b.appcode
         , b.cmscase
         , b.PROSCODE
         , p.ProsLast
         , IFNULL(p.ProsFrst , '') as ProsFrst
         , IFNULL(p.ProsMid  , '') as ProsMid 
         , IFNULL(c.CPChgDsc , '') as CPChgDsc
    
     from  CMCPTYP a
     INNER JOIN 
           CMCASEP b
       ON  a.appcode = b.appcode
       and a.cmscase = b.cmscase
     left  outer join
           PTPROSP  p 
       on  b.PROSCODE = p.PROSCODE
     INNER JOIN
           CMCPCHP c
       on  b.appcode = c.appcode
       and b.cmscase = c.cmscase
       AND c.PM46gA = 'Y'
    
     WHERE a.CSCPCODE = iCSCPCODE
       and a.cptypint = 'DF'
       and A.appcode in (
           select appcode
            from  CMAAGYP
            where appsysid = 'PCMS'
           )
       AND exists (
           select 1
            from  CMLIDXP c
            left  outer join
                  cmliexp d
              on  c.appcode  = d.appcode
              and c.cmscase  = d.cmscase
              and c.casptysq = d.casptysq
              and c.ltrseq   = d.ltrseq 
            where
              (       iSearchFlag =  'WEB'
                  AND IFNULL(d.lieflg2 , 'N') = 'N'
               OR     iSearchFlag <> 'WEB'
                  AND d.liamt1  = 0
              )
              and c.stscde = 'RFD'
              and a.appcode = c.appcode
              and
              (   a.cmscase = c.cmscase
               or c.cmscase = (
                  select mstcseq
                   from  CMMCRCP d
                   where a.appcode = d.appcode
                     and a.cmscase = d.cmscase
                  ) /* c.cmscase = ( */
              )
           ) /* exists ( */
    ;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (4) If a sample code in (3) was right,
    the procedure might be replaced by a view.

    The advantages of views over procedures are possibility of fully utilization of declarative language nature of SQL and it's set oriented operations.

    For example:
    (1) Add more conditions to choose rows before fetching the rows by adding the conditions in where clause.
    (2) Join(or through subqueries) with other tables without using cursor and loop.
    (3) Calcurate and include summary/total data(rows) with detaile data(rows) in one select statement by using GROUP BY GROUPING SETS/ROLLUP/CUBE clause.
    (4) and more...


    Example of a view:
    Code:
    CREATE VIEW stsiqry_pcmsda_filingcases AS
    select a.lastname
         , a.frstname
         , a.midname
         , a.CITATION
         , CHAR( DATE( NULLIF(a.CPDOB   , '1/1/1753') ) , USA ) AS CPDOB
         , CHAR( DATE( NULLIF(a.CSARRDT , '1/1/1753') ) , USA ) AS CSARRDT
         , b.warantno
         , b.casenbr
         , b.appcode
         , b.cmscase
         , b.PROSCODE
         , p.ProsLast
         , IFNULL(p.ProsFrst , '') as ProsFrst
         , IFNULL(p.ProsMid  , '') as ProsMid 
         , IFNULL(c.CPChgDsc , '') as CPChgDsc
         , a.CSCPCODE     /* Added column */
         , f.SearchFlag   /* Added column */
     from  (
           SELECT 'WEB' FROM sysibm.sysdummy1 UNION ALL
           SELECT ''    FROM sysibm.sysdummy1
           ) f(SearchFlag)
     INNER JOIN
           CMCPTYP a
       ON  0=0
     INNER JOIN 
           CMCASEP b
      ON  a.appcode = b.appcode
      and a.cmscase = b.cmscase
     left  outer join
           PTPROSP  p 
       on  b.PROSCODE = p.PROSCODE
     INNER JOIN
           CMCPCHP c
       on  b.appcode = c.appcode
       and b.cmscase = c.cmscase
       AND c.PM46gA = 'Y'
    
     WHERE a.cptypint = 'DF'
       and A.appcode in (
           select appcode
            from  CMAAGYP
            where appsysid = 'PCMS'
           )
       AND exists(
           select 1
            from  CMLIDXP c
            left  outer join
                  cmliexp d
              on  c.appcode  = d.appcode
              and c.cmscase  = d.cmscase
              and c.casptysq = d.casptysq
              and c.ltrseq   = d.ltrseq 
            where
              (       SearchFlag =  'WEB'
                  AND IFNULL(d.lieflg2 , 'N') = 'N'
               OR     SearchFlag <> 'WEB'
                  AND d.liamt1  = 0
              )
              and c.stscde = 'RFD'
              and a.appcode = c.appcode
              and
              (   a.cmscase = c.cmscase
               or c.cmscase =(
                  select mstcseq
                   from  CMMCRCP d
                   where a.appcode = d.appcode
                     and a.cmscase = d.cmscase
                  )
              )
           )
    ;
    A sample usage of the view:
    Code:
    SELECT spf.*
     FROM  stsiqry_pcmsda_filingcases spf
     WHERE spf.CSCPCODE   = iCSCPCODE
       AND spf.SearchFlag = iSearchFlag
     ORDER BY
           appcode
    ;
    Last edited by tonkuma; 01-31-12 at 01:12. Reason: Add ORDER BY to sample usage of the view. Replace convert and isnull functions by CHAR(... , USA) and IFNULL functions.

  5. #5
    Join Date
    Jan 2012
    Posts
    37

    Wow

    Thanks for the responses,

    This is way more than what I had expected to get back. I will definitely take into account what has been mentioned. You both are awesome.

Tags for this Thread

Posting Permissions

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