Error SQL0303N while updation of Decimal field through cursor

harsha_james
harsha_james used Ask the Experts™
on
Problem background:-  I need to populate VFMINSTR.EXCHRATE(tablename.col name) from BOTMFXINSTR.CONTRARATEDESC. Fkey relation bet both table exists on VFMINSTRKY = BOTMFXINSTRKY.
Datatype VFMINSTR.EXCHRATE is Decimal(28,10) and BOTMFXINSTR.CONTRARATEDESC is varchar(35).
Sample value of BOTMFXINSTR.CONTRARATEDESC are
aaaa@11.5555000000
bbbb@11.5555000000
I need to extract string after @ and convert it to decimal and store it to VFMINSTR.EXCHRATE.
I have written following code and getting error as
"SQL0303N  A value cannot be assigned to a host variable in the SELECT, VALUES,
or FETCH statement because the data types are not compatible.  SQLSTATE=42806"

CREATE PROCEDURE StringModifications
	DYNAMIC RESULT SETS 1
	MODIFIES SQL DATA	
	
P1: BEGIN	
	DECLARE strA VARCHAR(35);
	DECLARE pos INT;
	DECLARE strALen INT;
	DECLARE StrB VARCHAR(35);
	DECLARE SQLCODE INTEGER DEFAULT 0;
	DECLARE MYSQLCODE INT;
 
		DECLARE cursor1 CURSOR FOR 
			    SELECT VFMINSTRKY, EXCHRATE
			      FROM SONEDBA.VFMINSTR X
			      WHERE EXISTS
				(SELECT *
				   FROM SONEDBA.BOTMFXINSTR Y
				   WHERE X.VFMINSTRKY = Y.BOTMFXINSTRKY AND
				   LENGTH(CONTRARATEDESC) >0 )
			    FOR UPDATE OF EXCHRATE;
 
 
	OPEN cursor1;
	SET MYSQLCODE = SQLCODE;
 
 
	WHILE (MYSQLCODE = 0) DO
		FETCH cursor1 INTO strA;
		SET MYSQLCODE = SQLCODE;
		
		SET pos = POSSTR(strA, '@');	--5
 
		IF pos > 0 THEN
						
				SET strALen = LENGTH(strA);	--7
						
				SET StrB = RIGHT(strA, strALen - pos);
 
				UPDATE SONEDBA.VFMINSTR 
				SET EXCHRATE = CAST(StrB AS DECIMAL(28,10)) 
				WHERE CURRENT OF cursor1;
		END IF;
		
	END WHILE;
 
CLOSE cursor1;
END P1@

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
i think you have a problem with your fetch
your cursor select list contains 2 columns but the fetch statement only fetches into a single variable

Author

Commented:
Yes, My whole Declare cursor statement is wrong. I have corrected it to
DECLARE cursor1 CURSOR FOR
    SELECT CONTRARATEDESC
      FROM SONEDBA.BOTMFXINSTR X
      WHERE EXISTS
      (SELECT *   FROM SONEDBA.VFMINSTR Y
         WHERE Y.VFMINSTRKY = X.BOTMFXINSTRKY AND
                        LENGTH(X.CONTRARATEDESC) >0 )
     FOR UPDATE;
Now how can i update VFMINSTR.EXCHRATE using this cursor.
you can't
you can only update the table from which the cursor is selecting

you will need to perform something like this

open cursor
fetch
loop
  update
  fetch
end loop
close cursor
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I declared cursor like this:-
DECLARE cursor1 CURSOR FOR
    SELECT X.CONTRARATEDESC,Y.EXCHRATE  FROM SONEDBA.BOTMFXINSTR X, SONEDBA.VFMINSTR Y
     WHERE Y.VFMINSTRKY = X.BOTMFXINSTRKY AND
         LENGTH(X.CONTRARATEDESC) >0
    FOR UPDATE;
But Error I got during compilation of SP is
SQL0511N  The FOR UPDATE clause is not allowed because the table specified by
the cursor cannot be modified.  LINE NUMBER=14.  SQLSTATE=42829
I think issue is select statment for multiple table columns with for update clause
And for fetching :- FETCH cursor1 INTO strA,ExRate;


so remove the for update:

DECLARE cursor1 CURSOR FOR
    SELECT X.CONTRARATEDESC,Y.EXCHRATE  FROM SONEDBA.BOTMFXINSTR X, SONEDBA.VFMINSTR Y
     WHERE Y.VFMINSTRKY = X.BOTMFXINSTRKY AND
         LENGTH(X.CONTRARATEDESC) >0 ;

Author

Commented:
But my problem is i need to update EXCHRATE , if FOR UPDATE is removed it will not allow me to update EXCHRATE.
My issue is can i use cursor for above problem i.e. select cursor statment contains columns from two tables and I need to update value for one table from them.
you can't do that
a for update cursor can have only a single table in the from clause
why do you think you have to use the for update cursor?
just use regular updates

Author

Commented:
Thank you for suggesting this,
I defined cursor like
DECLARE cursor1 CURSOR FOR
      SELECT X.CONTRARATEDESC,Y.EXCHRATE,Y.VFMINSTRKY
                      FROM SONEDBA.BOTMFXINSTR X, SONEDBA.VFMINSTR Y
       WHERE Y.VFMINSTRKY = X.BOTMFXINSTRKY AND
       LENGTH(X.CONTRARATEDESC) >0 ;

And for Fetch i used FETCH cursor1 INTO strA,ExRate,VfmKy;

And for update
               UPDATE SONEDBA.VFMINSTR
      SET EXCHRATE = CAST(StrB AS DECIMAL(28,10))
      WHERE VFMINSTRKY = VfmKy;
And now it is working correctly.
Thank you very much.
you are welcome
Hi Harsha,

Also, the cursor selects EXCHRATE from VFMINSTR.  It would appear that you want to select the string CONTRARATEDESC from BOTMFXINSTR.

This update can be run as a single query (SQL).  It's not necessary to write an entire store procedure to manage this.  An example is below.  The query verifies that the value to be converted is numeric, though it doesn't check for multiple decimal points.  If that's an issue in your data it's easy to add to the query.

Kent

--
--  Simple query
--
 
MERGE INTO sonedba.vfminstr t0
USING
(
  SELECT botmfxinstr,
    double (substr (position, position ('@', contraratedes) + 1)) exchrate
  FROM sonedba.botmfxinstr
) t1
ON t0.vfminstrky = t1.botmfxinstr
WHEN MATCHED THEN
  UPDATE set t0.exchrate = t1.exchrate;
  
--
-- Same query with data integrity checks
--
 
MERGE INTO sonedba.vfminstr t0
USING
(
  SELECT botmfxinstr,
    case when position ('@', contraratedesc) > 0 then
      case when translate (substr (position, position ('@', contraratedes) + 1), '           ', '0123456789.') = '' then
        double (substr (position, position ('@', contraratedes) + 1)) 
      else NULL
      end
    else NULL
    end exchrate
  FROM sonedba.botmfxinstr
) t1
ON t0.vfminstrky = t1.botmfxinstr
WHEN MATCHED THEN
  UPDATE set t0.exchrate = t1.exchrate

Open in new window

You folks did a lot of work while I was away....

 :)

Author

Commented:
Thank you very much KDO,
As i m new to Database Programming, today I learned two things first is CURSORS from Mr. Momi and second MERGE from you.
Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial