FLAT SQL to SET SQL

Database
Enthusiast

FLAT SQL to SET SQL

Hi All,

I need your help in converting FLAT LOGIC(Flat SQL) Stored procedure to a SET LOGIC(SET SQL). From what I understand, a Flat logic is a cursor based SQL while SET logic works on sets of data. 

I have read several examples how to convert but it just basic examples. I have this cursor which I need to eliminate but can't figure out how.

 FOR tCursor AS tCursor CURSOR FOR

      select a, b from table1

DO 

 IF POSITION(' = ' IN tCursor.b) > 0  then

Set v_p1 = POSITION(' = ' IN tCursor.b);

Set v_c1 = Substr(tCursor.b,1, v_p1 - 1);

Set v_o = '=';

Set v_v = Substr(tCursor.b, v_p1 + 3, 50);

else

 set v_p1 = POSITION(' <> ' IN tCursor.Criteria);

 set v_c1 = Substr(tCursor.b,1, v_p1 - 1);

 set v_o = '<>';

 set v_v = Substr(tCursor.b,v_p1 + 4, 50);

 END IF;

then another IF- ELSE statement follows which will use the variables set above.

Can anyone tell me tips on how I can set these variables so that I can use them in succeeding if else statements?

I tried this syntax:

select 

a,

b,

case when POSITION(' = ' IN b) > 0

then 

Set v_p1 = POSITION(' = ' IN b);

Set v_c1 = Substr(b,1, v_p1 - 1);

Set v_o = '=';

Set v_v = Substr(b, v_p1 + 3, 50); 

else

set v_p1 = POSITION(' <> ' IN b);

    set v_c1 = Substr(b,1, v_p1 - 1);

set v_o = '<>';

    set v_v = Substr(b,v_p1 + 4, 50);

end;

from table1

to set the variables

but it gives me an error than there is something expecting between the keyword 'THEN' and 'SET'.

I'm not sure if I'm doing it right as I do not have any data yet for the columns of my table. I'm just checking it if it will return any syntax errors.

Thanks in advance!

MC

2 REPLIES
Junior Contributor

Re: FLAT SQL to SET SQL

There's no SET in a SELECT.

SETing a variable translates to assigning an alias to the calculation and then use it within the same level or in a Derived Table, e.g.

SELECT
CASE
WHEN POSITION(' = ' IN tCursor.b) > 0
THEN POSITION(' = ' IN tCursor.b)
ELSE POSITION(' <> ' IN tCursor.b)
END AS v_p1,

SUBSTR(tCursor.b,1, v_p1 - 1) AS v_c1,

SUBSTR(tCursor.b, v_p1 + 3, 50) AS v_v,

CASE
WHEN POSITION(' = ' IN tCursor.b) > 0
THEN '='
ELSE '<>'
END AS v_o

Did you read George Coleman's blog on that topic?

GeorgeColeman's blog

Enthusiast

Re: FLAT SQL to SET SQL

Hi Dieter,

Yes I already did. I'm having a trouble last time, thinking of ways I can set values to my variable that I forgot to use derived tables .

Thank you for helping out :)

MC