SQL ALTER TABLE MATHS QUERY

Tags: oracle
By : Gavin
Source: Stackoverflow.com
Question!

I'm trying to use a Maths query ( with a Boolean ) to populate a column in my Oracle SQL Table, but am getting an error 'invalid data type', I've set them up as decimals. Would appreciate any help... Thanks Gavin

ALTER TABLE GS_MRC_EXISTING
ADD IF ( GROSS_LAST + DISC_LAST ) < ( GROSS_THIS + DISC_THIS ) = TRUE THEN DEC_GROSS = ( GROSS_THIS - GROSS_LAST ) 

CREATE TABLE GS_MRC_EXISTING (
CUST_CODE varchar (255),
GROSS_LAST decimal (6,2),
DISC_LAST decimal (6,2),
GROSS_THIS decimal (6,2),
DISC_THIS decimal (6,2),
INC_GROSS decimal (6,2),
INC_DISC decimal (6,2),
DEC_GROSS decimal (6,2),
DEC_DISC decimal (6,2)
)
By : Gavin


Answers

I'm really not entirely sure of your intent, but perhaps you're attempting to define a computed column:

CREATE TABLE GS_MRC_EXISTING (
CUST_CODE varchar (255),
GROSS_LAST decimal (6,2),
DISC_LAST decimal (6,2),
GROSS_THIS decimal (6,2),
DISC_THIS decimal (6,2),
INC_GROSS decimal (6,2),
INC_DISC decimal (6,2),
DEC_GROSS decimal (6,2)
  GENERATED ALWAYS AS (CASE
                         WHEN (GROSS_LAST   DISC_LAST) 


This video can help you solving your question :)
By: admin