Solved

DB2 SQL SYNTAX - Referencing columns from within a trigger

Posted on 2009-04-10
23
908 Views
Last Modified: 2012-06-27
Hi, I'm creating a trigger to calcuate a sale price from a cost price and the logic works as expected.  However I need to gross profit % to determinded but the value in the GP column of my PROD_INFO table.

Please see below for my code

Thanks in advance
Working code with static value:

BEGIN ATOMIC

DECLARE @REMAINDER DECIMAL(8,2) DEFAULT NULL;

SET @REMAINDER = 100 - 70;

UPDATE PROD_INFO

SET SALE_PRICE = (COST / @REMAINDER) * 100;

END
 

I have tried the below which results in A "not valid in the context where it is used" error;

BEGIN ATOMIC

DECLARE @REMAINDER DECIMAL(8,2) DEFAULT NULL;

SET @REMAINDER = 100 - PROD_INFO.GP;

UPDATE PROD_INFO

SET SALE_PRICE = (COST / @REMAINDER) * 100;

END

Open in new window

0
Comment
Question by:taylor99
  • 11
  • 8
  • 4
23 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 24115542
Hi Taylor,

In the SET statement you reference PROD_INFO.GP.  That data item doesn't exist at that point, but you can do the same thing with a SELECT statement.

Your use of '@' symbols suggests that this isn't really a DB2 question, but I'll offer a DB2 answer.  It won't work in SQL Server.


SELECT 100 - PROD_INFO.GP INTO @remainder FROM PROD_INFO;


Good Luck,
Kent


0
 

Author Comment

by:taylor99
ID: 24116149
Thanks Kent but I now get the below error
An unexpected token "REMAINDER" was found
when using
BEGIN ATOMIC
DECLARE REMAINDER DECIMAL(8,2) DEFAULT NULL;
SELECT 100 - PROD_INFO.GP INTO REMAINDER FROM PROD_INFO;
UPDATE PROD_INFO
SET SALE_PRICE = (COST / REMAINDER) * 100;
END

Sorry for the confusion with the @ I assumed it was a standard naming convention for variables.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24116200
Hi taylor,

You'll need all the appropriate header clauses.

CREATE TRIGGER mytrigger
NO CASCADE BEFORE INSERT ON mytable
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL

Can you post your entire trigger?

Thanks,
Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24121000
try

BEGIN ATOMIC
DECLARE REMAINDER DECIMAL(8,2) DEFAULT NULL;

SELECT 100 - PROD_INFO.GP INTO :REMAINDER FROM PROD_INFO;

UPDATE PROD_INFO
SET SALE_PRICE = (COST / :REMAINDER) * 100;
END
0
 

Author Comment

by:taylor99
ID: 24121262
Hi Kent,

The full trigger look as below, I have not included the REFERENCING NEW line as the results do not have to be row specific in this case.

CREATE TRIGGER GENRES."mytrigger"
AFTER  UPDATE OF COST ON PROD_INFO  
FOR EACH ROW  
MODE DB2SQL

BEGIN ATOMIC
DECLARE REMAINDER DECIMAL(8,2) DEFAULT NULL;
SELECT 100 - PROD_INFO.GP INTO REMAINDER FROM PROD_INFO;
UPDATE PROD_INFO
SET SALE_PRICE = (COST / REMAINDER) * 100;
END

Thanks
0
 

Author Comment

by:taylor99
ID: 24121265
Hi Momi,

I get an  "unexpected token" error when I try as above with the :

Thanks
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24122161
and what is the full error message you receive when you try your version?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24122178
Hi Taylor,

I suspect that the REFERENCING NEW clause may be the issue.  I've not built any triggers where I didn't specifically label items as old or new, so I can't attest to the behavior in your trigger when you don't specifically reference an item as pre- or post-change.

Qualifying the column with the table name is redundant.  Try the query below where the item is qualified by NEW, and not the table name.

Kent


CREATE TRIGGER GENRES."mytrigger"

AFTER  UPDATE OF COST ON PROD_INFO  

REFERENCING NEW AS n

FOR EACH ROW  

MODE DB2SQL
 

BEGIN ATOMIC

DECLARE REMAINDER DECIMAL(8,2) DEFAULT NULL;

SELECT 100 - n.GP INTO REMAINDER FROM PROD_INFO;

UPDATE PROD_INFO

SET n.SALE_PRICE = (COST / REMAINDER) * 100;

END

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
ID: 24122198
Hi Momi,

If his trigger is SQL based, the colon is illegal.  The colon is a pre-processor flag for COBOL and other non-SQL languages.


Kent
0
 

Author Comment

by:taylor99
ID: 24122362
Hi Kent,  unfortunately I'm still hitting an error when usig the code as above.  

Error:
[IBM][CLI Driver][DB2/NT] SQL0104N  An unexpected token
"REMAINDER" was found following "n.GP INTO".  
Expected tokens may include:  "<space>".  LINE NUMBER=5.  
SQLSTATE=42601

Explanation:
A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

0
 
LVL 45

Expert Comment

by:Kdo
ID: 24122413
Well, can you perform the entire calculation on a single line?  There may be a rounding difference in the final result.

BEGIN ATOMIC
UPDATE PROD_INFO
  SET n.SALE_PRICE = (n.COST / (100 - n.GP)) * 100;
END


Kent
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 24122414
try

CREATE TRIGGER GENRES."mytrigger"
AFTER  UPDATE OF COST ON PROD_INFO  
REFERENCING NEW AS n
FOR EACH ROW  
MODE DB2SQL
 
BEGIN ATOMIC
DECLARE REMAINDER DECIMAL(8,2) DEFAULT NULL;
SELECT 100 - n.GP INTO REMAINDER FROM n;
UPDATE PROD_INFO
SET SALE_PRICE = (COST / REMAINDER) * 100
where record_id in (select record_id from n)
END
0
 

Author Comment

by:taylor99
ID: 24122965
Kent, when I the code below

BEGIN ATOMIC
UPDATE PROD_INFO
  SET n.SALE_PRICE = (n.COST / (100 - n.GP)) * 100;
END

I get

[IBM][CLI Driver][DB2/NT] SQL0206N  "SALE_PRICE" is not
valid in the context where it is used.  LINE NUMBER=3.  
SQLSTATE=42703

There seems to be an issue with the "n" as when I try without it the SALE_PRICE column is updated but obviously by obmitting the n all rows are then updated.  Any idea why this would happen?
0
 

Author Comment

by:taylor99
ID: 24122985
Momi, whe I try your code i get the same error as previously.

[IBM][CLI Driver][DB2/NT] SQL0104N  An unexpected token
"REMAINDER" was found following "0 - n.GP INTO".  
Expected tokens may include:  "<space>".  LINE NUMBER=4.  
SQLSTATE=42601

In this case could there be an issue with the variable declaration?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24123059
Hi Taylor,

Let's be sure I understand.  :)

SALE_PRICE is a column in table PROD_INFO?

You are using the REFERENCING NEW AS N clause?


Kent
0
 

Author Comment

by:taylor99
ID: 24123256
the answer to both question is yes, I've double checked to be sure as it seem very strange that it's resulting in an error.  The full trigger is as below.  The strange this is that if I remove the n prefix from n.SALE_PRICE the trigger works, proving the column / table names are correct and REFERENCING NEW AS n clause is working as the rest of the calculation is processed properly.

CREATE TRIGGER GENRES."mytrigger" AFTER  UPDATE OF COST ON PROD_INFO  
REFERENCING  NEW AS n  
FOR EACH ROW  
MODE DB2SQL
BEGIN ATOMIC
UPDATE PROD_INFO
SET n.SALE_PRICE = (n.COST / (100 - n.GP)) * 100;
END
0
 

Author Comment

by:taylor99
ID: 24123304
I've tried to narrow the problem down and even if I try something simple such as the below gives me the same error.

CREATE TRIGGER FATDUCK."mytrigger" NO CASCADE BEFORE  UPDATE OF COST ON PROD_INFO  REFERENCING  OLD AS o  NEW AS newr  
FOR EACH ROW  
MODE DB2SQL
BEGIN ATOMIC
UPDATE PROD_INFO
SET newr.GP = 1;
END

Error: [IBM][CLI Driver][DB2/NT] SQL0206N  "GP" is not valid in
the context where it is used.  LINE NUMBER=3.  SQLSTATE=42703

I've also tried other columns in the table but see the same issue
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 24123360
Bingo.

We're all chasing out tails on a simple syntax error.  :(

UPDATE isn't necessary within the Trigger.  You started with the correct modification syntax, but were using SQL Server variable names.

Try the code below.  :)


Kent

CREATE TRIGGER FATDUCK."mytrigger" NO CASCADE BEFORE  UPDATE OF COST ON PROD_INFO  REFERENCING  OLD AS o  NEW AS newr  

FOR EACH ROW  

MODE DB2SQL

BEGIN ATOMIC

SET newr.SALE_PRICE = (newr.COST / (100 - newr.GP)) * 100;

END

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24124704
the problem is not in the trigger at all
in db2, you are not allowed to use a table alias in the set cluase of an update statement
even if you try to run
update mytable t
set t.my_column = 1

you will get an sql error,
it is not related to the fact that you are using a trigger or a variable
0
 

Author Closing Comment

by:taylor99
ID: 31568885
Thanks Kent
0
 

Author Comment

by:taylor99
ID: 24124859
Thanks allot for your help guys.  I have another related query Id appreciate your input on too, just to gain a greater understanding of how DB2 works.

When I originally tried to create this calculation within DB2 I tried (I'm ignoring syntax a bt here)

PERCENT = PROD_INFO.GP / 100
REMAINDER = 1  PERCENT
SALE_PRICE = COST / REMAINDER

Which should give the following results using an example where PROD_INFO = 70% and COST = 1.50

PERCENT = 0.7
REMAINDER = 0.3
SALE_PRICE = 5.00

However my variables did not seem to be capable of storing decimals correctly and would substitute any figure < 1 with 0.  I thought the DECLARE REMAINDER DECIMAL(8,2) DEFAULT NULL; line would resolve this but apparently not.  Could this behaviour be caused by another setting within DB2?

Many thanks again
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24124868
Hi Taylor,

The underlying mathematics doesn't necessarily work like you might think or want.  When values are stored in intermediate variables, the precision of those variables comes into play.  DECIMAL(8,2) is a poor choice for storing a percentage.  1% is really 000000.01.  1.5% is also 000000.01 as there aren't enough digits to the right of the decimal place to record the half-percent.

When in doubt, us floats or double for the mathematics.  You can always enforce precision on the final result.


Kent
0
 

Author Comment

by:taylor99
ID: 24124903
Thanks again Kent, I'm sure I'll have another question or two soon.

Cheers
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now