DB2 SQL SYNTAX - Referencing columns from within a trigger

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

taylor99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
taylor99Author Commented:
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
Kent OlsenDBACommented:
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

momi_sabagCommented:
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
taylor99Author Commented:
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
taylor99Author Commented:
Hi Momi,

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

Thanks
0
momi_sabagCommented:
and what is the full error message you receive when you try your version?
0
Kent OlsenDBACommented:
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
Kent OlsenDBACommented:
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
taylor99Author Commented:
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
Kent OlsenDBACommented:
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
momi_sabagCommented:
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
taylor99Author Commented:
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
taylor99Author Commented:
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
Kent OlsenDBACommented:
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
taylor99Author Commented:
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
taylor99Author Commented:
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
Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
momi_sabagCommented:
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
taylor99Author Commented:
Thanks Kent
0
taylor99Author Commented:
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
Kent OlsenDBACommented:
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
taylor99Author Commented:
Thanks again Kent, I'm sure I'll have another question or two soon.

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.