This might be a very specific issue, but, at the moment, CDPOS table shows the changes as follows:
As you can observe, line 2 and 3 have the same OBJECTID, CHANGENR, DATE and TIME. Therefore I would like to keep them in a single row - expected result:
Does anyone have a suggestion how to achieve this?
Perhaps creating a temporary table to store some values and then update it with the value from the other row. But not sure how to get there.
Thank you in advance! 👋
Hi Salma,
you can try it by using LEAD.
e.g:
SELECT
OBJECTID
,CHANGENR
,VALUE_NEW#
,VALUE_OLD
,UDATE
,UTIME
,LEAD(VALUE_OLD, 1) OVER (PARTITION BY CDPOS.OBJECTID, CDPOS.CHANGENR ORDER BY CDHDR.UDATE, CDHDR.UTIME) AS "next_value_old"
FROM CDPOS
INNER JOIN CDHDR ON
CDPOS.OBJECTLCAS = CDHDR.OBJECTCLAS
AND CDPOS.OBJECTID = CDHDR.OBJECTID
AND CDPOS.CHANGENR = CDHDR.CHANGENR
Hi Salma,
you can try too this vertica query on your CDPOS table:
SELECT
OBJECTID,
CHANGENR;
LISTAGG(VALUE_NEW) as VALUE_NEW,
LISTAGG(VALUE_OLD) as VALUE_OLD,
DATE,
TIME,
EVENTTIME
FROM CDPOS
GROUP BY (OBJECTID,CHANGENR, DATE,TIME,EVENTTIME)
Hi Salma,
you can try too this vertica query on your CDPOS table:
SELECT
OBJECTID,
CHANGENR;
LISTAGG(VALUE_NEW) as VALUE_NEW,
LISTAGG(VALUE_OLD) as VALUE_OLD,
DATE,
TIME,
EVENTTIME
FROM CDPOS
GROUP BY (OBJECTID,CHANGENR, DATE,TIME,EVENTTIME)
Thank you!
It looks like this though:
How could I get rid of the ',X'? Perhaps with a REPLACE()?
you are welcome!
yes you can do it with Replace() or if you have always the Pattern that you need the first character before the comma you can add split_part(LISTAGG(VALUE_NEW) , ',',1) as VALUE_NEW
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.