priyanka velusamy
asked on
select only modified column name based on the status
I had this question after viewing sql query to compare two or three rows of same id with different version of same table and find the modified column name.
xample:
id version quantity datemodified securityvalue price Status
45 1 690 28/oct/2016 8 100 N
45 2 690 28/oct/2016 5 100 V
45 3 445 28/oct/2016 6 100 N
45 4 445 28/oct/2016 5 400 N
OUTPUT
ID VERSION AMENDMENT DEATILS
45 3 QTE,SECURITY VALUE (COMPARED WITH PREVIOUS 2ND VERSION( WITH V TO N COMPARISON )
PLEASE HELP ME TO SOLVE THIS .WHENEVER STATUS CHANGES FROM V TO N I NEED THE MODIFIED COLUMN NAME
xample:
id version quantity datemodified securityvalue price Status
45 1 690 28/oct/2016 8 100 N
45 2 690 28/oct/2016 5 100 V
45 3 445 28/oct/2016 6 100 N
45 4 445 28/oct/2016 5 400 N
OUTPUT
ID VERSION AMENDMENT DEATILS
45 3 QTE,SECURITY VALUE (COMPARED WITH PREVIOUS 2ND VERSION( WITH V TO N COMPARISON )
PLEASE HELP ME TO SOLVE THIS .WHENEVER STATUS CHANGES FROM V TO N I NEED THE MODIFIED COLUMN NAME
ASKER
Thank you .
ASKER
Already we are checking for quantity != prev_qty THEN 'QTY' .then why again in condition we are checking
AND (quantity != prev_qty OR securityvalue != prev_security OR price != prev_price).Can u please tel me
AND (quantity != prev_qty OR securityvalue != prev_security OR price != prev_price).Can u please tel me
ASKER
AND prev_status = 'V'(because of this i am getting prev_status is invalid identifier error .
can we assign like that as u gave.
can we assign like that as u gave.
the check in the where clause is to only return rows where at least one of those columns changes.
The check in the case is to display which columns change.
I tested the query I posted. The syntax is valid.
If you're getting an invalid identifier error, did you change the query? If so, what is the code you ran?
The check in the case is to display which columns change.
I tested the query I posted. The syntax is valid.
If you're getting an invalid identifier error, did you change the query? If so, what is the code you ran?
ASKER
select id, version,
rtrim(case when version>1 and quantity!=prev_quantity then 'qty-' end ||
case when version>1 and securityvalue!=prev_val then 'val-' end ||
case when version>1 and price!=prev_price then 'price-' end,'-') changes
from (
select id, version, quantity, securityvalue, price ,
lag(status) OVER (partition by .id ,datemodified order by version asc) prev_status,
lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
lag(price) over(partition by id,datemodified order by version asc) prev_price
from tab1
WHERE status='N'
AND prev_status='V'
)
now i am getting invalid error because i am using aliase name to assingn
rtrim(case when version>1 and quantity!=prev_quantity then 'qty-' end ||
case when version>1 and securityvalue!=prev_val then 'val-' end ||
case when version>1 and price!=prev_price then 'price-' end,'-') changes
from (
select id, version, quantity, securityvalue, price ,
lag(status) OVER (partition by .id ,datemodified order by version asc) prev_status,
lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
lag(price) over(partition by id,datemodified order by version asc) prev_price
from tab1
WHERE status='N'
AND prev_status='V'
)
now i am getting invalid error because i am using aliase name to assingn
select id, version,
rtrim(case when version>1 and quantity!=prev_quantity then 'qty-' end ||
case when version>1 and securityvalue!=prev_val then 'val-' end ||
case when version>1 and price!=prev_price then 'price-' end,'-') changes
from (
select id, version, quantity, securityvalue, price,status,
lag(status) OVER (partition by .id ,datemodified order by version asc) prev_status,
lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
lag(price) over(partition by id,datemodified order by version asc) prev_price
from tab1
)
WHERE status='N'
AND prev_status='V'
rtrim(case when version>1 and quantity!=prev_quantity then 'qty-' end ||
case when version>1 and securityvalue!=prev_val then 'val-' end ||
case when version>1 and price!=prev_price then 'price-' end,'-') changes
from (
select id, version, quantity, securityvalue, price,status,
lag(status) OVER (partition by .id ,datemodified order by version asc) prev_status,
lag(quantity) over(partition by id,datemodified order by version asc) prev_quantity,
lag(securityvalue) over(partition by id,datemodified order by version asc) prev_val,
lag(price) over(partition by id,datemodified order by version asc) prev_price
from tab1
)
WHERE status='N'
AND prev_status='V'
ASKER
like price,quantity,i have to fetch many columns from different table on join condition . like
select id, version, quantity, securityvalue, price,status,
lag(status) OVER (partition by tab1.id ,tab1.datemodified order by version asc) prev_status,
lag(tab2.quantity) over(partition by tab1.id,tab1.datemodified order by version asc) prev_quantity,
lag(tab3.securityvalue) over(partition by tab1.id,tab1.datemodified order by version asc) prev_val,
lag(tab1.price) over(partition by id,datemodified order by version asc) prev_price
from tab1,tab2,tab3
where tab1.id=tab2.id
tab1.id=tab3.id
)status='N'
prev_status=V'---- gives prev_status invalid identifier
now it gives error also if i do like:
lag(tab3.securityvalue) over(partition by tab1.id,tab1.datemodified order by version asc) prev_val,
lag(tab1.price) over(partition by id,datemodified order by version asc) prev_price
from tab1,tab2,tab3 ) where tab1.id=tab2.id
and tab1.id=tab3.id
status='N'
prev_status=V'
please guide me .
select id, version, quantity, securityvalue, price,status,
lag(status) OVER (partition by tab1.id ,tab1.datemodified order by version asc) prev_status,
lag(tab2.quantity) over(partition by tab1.id,tab1.datemodified order by version asc) prev_quantity,
lag(tab3.securityvalue) over(partition by tab1.id,tab1.datemodified order by version asc) prev_val,
lag(tab1.price) over(partition by id,datemodified order by version asc) prev_price
from tab1,tab2,tab3
where tab1.id=tab2.id
tab1.id=tab3.id
)status='N'
prev_status=V'---- gives prev_status invalid identifier
now it gives error also if i do like:
lag(tab3.securityvalue) over(partition by tab1.id,tab1.datemodified order by version asc) prev_val,
lag(tab1.price) over(partition by id,datemodified order by version asc) prev_price
from tab1,tab2,tab3 ) where tab1.id=tab2.id
and tab1.id=tab3.id
status='N'
prev_status=V'
please guide me .
select id, version, quantity, securityvalue, price,status,
lag(status) OVER (partition by tab1.id ,tab1.datemodified order by version asc) prev_status,
lag(tab2.quantity) over(partition by tab1.id,tab1.datemodified order by version asc) prev_quantity,
lag(tab3.securityvalue) over(partition by tab1.id,tab1.datemodified order by version asc) prev_val,
lag(tab1.price) over(partition by id,datemodified order by version asc) prev_price
from tab1,tab2,tab3
where tab1.id=tab2.id
tab1.id=tab3.id
)status='N'
prev_status=V'---- gives prev_status invalid identifier
you don't have a where clause
lag(tab3.securityvalue) over(partition by tab1.id,tab1.datemodified order by version asc) prev_val,
lag(tab1.price) over(partition by id,datemodified order by version asc) prev_price
from tab1,tab2,tab3 ) where tab1.id=tab2.id
and tab1.id=tab3.id
you can't reference tab1, tab2, or tab3 outside of the parentheses
ASKER
sorry i missed where class to mention in code but i used it.
if i put where class outside for status checking,i cannot reference tables.
or viceversa .i have to reference condition of both the tables(where tab1.id=tab2.id
and tab1.id=tab3.id) and also should check for status.
if i put where class outside for status checking,i cannot reference tables.
or viceversa .i have to reference condition of both the tables(where tab1.id=tab2.id
and tab1.id=tab3.id) and also should check for status.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANK YOU .
you're welcome, if you don't need anything else, please close the question.
ASKER
ABOVE code is running fine .along with it i have put condition it should generate only for flows in event(2,3)
the output is giving 10 lines of output for same flow ,i modified quantity,price,security inSAME TIME but it is individually create flows
example:
id eventflow amendmentdetails
45 2 quotation
45 2 QUOTATION , PRICE
45 2 QUOTATION PRICE SECURITYVALUE
45 3 QUOTATION
45 3 QUOTATION PRICE
45 3 QUOTATION ,PRICE,SECURITY VALUE
45 3 EMPTYBOX
I NEED
42 2 QUOTATION PRICE SECURITYVALUE
45 3 QUOTATION ,PRICE,SECURITY VALUE
Can somebody help me please .
Thanks in advance.
the output is giving 10 lines of output for same flow ,i modified quantity,price,security inSAME TIME but it is individually create flows
example:
id eventflow amendmentdetails
45 2 quotation
45 2 QUOTATION , PRICE
45 2 QUOTATION PRICE SECURITYVALUE
45 3 QUOTATION
45 3 QUOTATION PRICE
45 3 QUOTATION ,PRICE,SECURITY VALUE
45 3 EMPTYBOX
I NEED
42 2 QUOTATION PRICE SECURITYVALUE
45 3 QUOTATION ,PRICE,SECURITY VALUE
Can somebody help me please .
Thanks in advance.
please post a new question with full example of input data and expected output.
version,
RTRIM(
CASE WHEN quantity != prev_qty THEN 'QTY' || ',' END
|| CASE WHEN securityvalue != prev_security THEN 'SECURITY VALUE' || ',' END
|| CASE WHEN price != prev_price THEN 'PRICE' END,
','
)
amendment_details
FROM (SELECT id,
version,
quantity,
datemodified,
securityvalue,
price,
status,
LAG(status) OVER(PARTITION BY id ORDER BY version) prev_status,
LAG(quantity) OVER(PARTITION BY id ORDER BY version) prev_qty,
LAG(securityvalue) OVER(PARTITION BY id ORDER BY version) prev_security,
LAG(price) OVER(PARTITION BY id ORDER BY version) prev_price
FROM yourtable t)
WHERE status = 'N'
AND prev_status = 'V'
AND (quantity != prev_qty OR securityvalue != prev_security OR price != prev_price)
I've made the assumption all of the comparison fields are non-null.
if that's not true, then you'll need to use NVL or COALESCE on the values
for example...
CASE WHEN nvl(quantity,-1) != nvl(prev_qty,-1) THEN 'QTY' || ',' END