Sql to Db2 scripts

zaryk · #1 (**permalink**) 01-30-12, 14:45

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.

Peter.Vanroose · #2 (**permalink**) 01-30-12, 17:09

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 ...".

tonkuma · #3 (**permalink**) 01-30-12, 20:09

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 ( */
;

tonkuma · #4 (**permalink**) 01-30-12, 23:17

(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
;

zaryk · #5 (**permalink**) 01-31-12, 08:34

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech