Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Can you advice me on how to write a SQL statement?

Can you help me to write an SQL statement for the below scenario?

Main file : record number, customer code
Tempo file: record number
correct customer code

I need a SQL statement that will be able to update the main file using record numbers in both files?


Wouldn't it be great if one could simply type:

 Update mainFile
        set customerCode = tempoFile.customerCode
                from tempoFile
                     where mainFile.recordNumber = tempoFile.recordNumber 

I have seen such SQL syntax somewhere on the Web but unfortunately, it is not available on the iSeries 400 at this stage. I am afraid some programming is required. I have managed to do the following. I am sure there are better ways but here it goes:

  Create procedure myLib/testit ( ) language SQL
        begin
            declare realCustomer char (10);
            declare modifiedRow char (10);
            DECLARE not_found CONDITION FOR SQLSTATE '02000';
            DECLARE at_end INT DEFAULT 0;    
       
            declare c1 cursor for select tempfile.customer, permfile.recordNumber          
                                from tempfile, permfile where tempfile.recordNumber= permfile.recordNumber;  
            DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
 
            open c1;
            fetch c1into realCustomer, modifiedRow;
 
            WHILE at_end = 0 DO                       
                                update permfile set customer = realCustomer where recordNumber = modifiedRow;
                                fetch c1 into realCustomer, modifiedRow;
            END WHILE;
            CLOSE c1;
        END                  

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close