Search code examples
sqldb2ibm-midrangerpgle

Logic to prepare SQL statements dynamically

So i have a requirement where I need to read through records of all records of a file and insert them into another file if they meet a set of rules which are described in another table as shown below..

enter image description here

A record after it has been read from the first file has to meet all the sequences of at least one Rule to make it eligible to be written into the Second table.

For example once a record is read from CAR file, the rules below have to be checked till all sequences of atleast one rule set is satisfied. For this I was planning to Create a dynamic SQL program something of this sort. But this does not work as Prepared SQL does not support host variables.

enter image description here

If any body can suggest or provide any guidance on how to create SQL statemtns dynamically and check if records satisfy the required rules for them to be entered into the second file, it would be great

So basically what I am looking for is once I select a field from a table, how do I store it somehere to do further validation and checking.

Update

:

Based on the intelligent advice from Danny117, I have come up with the below code:

H Option(*NoDebugIO:*SrcStmt)                                    
D RULEDS        E DS                  EXTNAME(RULESTABLE)        
D MAXRUL          S              1  0                            
D MAXSEQ          S              1  0                            
D STMT            S            512                               
D WHERESTMT       S            512    INZ('')                    
D FullSqlStmt     S            512    INZ('')                    
D RULINDEX        S              1  0 INZ(1)                     
D SEQINDEX        S              1  0 INZ(1)                     
D APOS            C                   CONST('''')                
  /Free                                                          
    Exec SQL SELECT MAX(RULENO)INTO :MAXRUL FROM RULESTABLE;     
    Exec SQL DECLARE RULCRS CURSOR FOR SELECT * FROM RULESTABLE; 
    Exec SQL OPEN RULCRS;                                        
    Exec SQL FETCH RULCRS INTO :RULEDS;                          
    DoW (Sqlcod = 0 AND RULINDEX <= MAXRUL);                     
      Exec SQL SELECT MAX(SEQNO) INTO :MAXSEQ FROM RULESTABLE    
      WHERE RULENO=:RULINDEX ;                                   
      DoW (SEQINDEX <= MAXSEQ);                                  
   If (Position <> '');                                     
     Field = 'SUBSTR('+%Trim(Field)+','+%Trim(Position)+',' 
     +'1'+')';                                              
   EndIf;                                                   
   WhereStmt = %Trim(WhereStmt) + ' ' + %Trim(field)+ ' ' + 
   %Trim(condition) + ' ' + APOS + %Trim(Value) + APOS;     

   If (SeqIndex < MaxSeq);                                  
     WhereStmt = %Trim(WhereStmt) + ' AND ';                
   EndIf;                                                   


   Exec SQL FETCH NEXT FROM RULCRS INTO :RULEDS;            
   SeqIndex = SeqIndex + 1;                                 
 EndDo;                                                     
 FullSqlStmt = %Trim('INSERT INTO ITMRVAT SELECT * +        
 FROM ITMRVA WHERE '+ %Trim(WhereStmt));                    
 Exec SQL Prepare InsertStmt from :FullSqlStmt;             
 Exec SQL EXECUTE InsertStmt;                               
  RulIndex = RulIndex + 1;
EndDo;

This produces SQL statement as shown below which is what I want. Now let me go ahead and look at the other parts of the code. 

 > EVAL FullSqlStmt                                                       
  FULLSQLSTMT =                                                          
            ....5...10...15...20...25...30...35...40...45...50...55...60 
       1   'INSERT INTO ITMRVAT SELECT * FROM ITMRVA WHERE STID = 'PLD' '
      61   'AND ENGNO LIKE '%415015%' AND SUBSTR(ENGNO,1,1) = 'R' AND SU'
     121   'BSTR(ENGNO,5,1) = 'Y'                                       '
     181   '                                                            '
     241   '                                                            '
     301   '                                                            '
     361   '                                                            '
     421   '                                                            '
     481   '                                '

But the issue is now as I mentioned in my comment to Danny, how to handle if a new rule involving second table is specified..

Solution

  • You have to translate the rules into a join statement or a where clause. The join statement is more complex so go that route.

    If you were smart (and you are) consider saving the rules as a SQL clause that you can join or use in a where clause. Its infinitely flexible this way a more modern design.

    rule 1 / car.year = 1990 and car.engno like '%43243%' and substring(car.vin,12,1) = 'X'

    eval statement = 
insert into sometable
Select car.* from car  
join sysibm.sysdummy1 
on car.year = 1990 
and car.engno lile '%43243%'

    ...etc on to rule 2 starting with "OR"

    or car.year = PLD
and car.engno like '%1234%'

    ...etc other rules starting with "OR"

    exec immediate statement