Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

select only modified column name  based on the status

Posted on 2016-11-13
15
Medium Priority
?
68 Views
Last Modified: 2016-11-16
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
0
Comment
Question by:priyanka velusamy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41886311
SELECT id,
       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
1
 

Author Comment

by:priyanka velusamy
ID: 41887214
Thank you .
0
 

Author Comment

by:priyanka velusamy
ID: 41887254
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
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.

 

Author Comment

by:priyanka velusamy
ID: 41887319
AND prev_status = 'V'(because of this i am getting prev_status is invalid identifier error .
can we assign like that as u gave.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41887369
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?
0
 

Author Comment

by:priyanka velusamy
ID: 41887519
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41887730
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'
0
 

Author Comment

by:priyanka velusamy
ID: 41887755
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 .
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41887782
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

Open in new window



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

Open in new window



you can't reference tab1, tab2, or tab3 outside of the parentheses
1
 

Author Comment

by:priyanka velusamy
ID: 41887787
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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41887793
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 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 AND tab1.id = tab3.id)
 WHERE status = 'N' AND prev_status = 'V'
0
 

Author Comment

by:priyanka velusamy
ID: 41887799
THANK YOU .
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41887808
you're welcome, if you don't need anything else, please close the question.
0
 

Author Comment

by:priyanka velusamy
ID: 41889343
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41889673
please post a new question with full example of input data and expected output.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

722 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