?
Solved

DB2 SQL SYNTAX - Referencing columns from within a trigger

Posted on 2009-04-10
23
Medium Priority
?
964 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 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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
 
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 46

Expert Comment

by:Kent Olsen
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 46

Accepted Solution

by:
Kent Olsen earned 2000 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 46

Expert Comment

by:Kent Olsen
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

850 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