All Forums Database
mcfabrero 6 posts Joined 09/13
22 Oct 2014

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


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



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


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:




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


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


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



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!




dnoeth 4628 posts Joined 11/04
22 Oct 2014

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.

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


mcfabrero 6 posts Joined 09/13
23 Oct 2014

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 :)

You must sign in to leave a comment.