If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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

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

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