Sequential Numbers - SQL (2)

MERGE INTO ps_sf_acctg_ln
USING (SELECT ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt,seqnum, sf_line_nbr) sf_line_nbr, ROWID rid FROM ps_sf_acctg_ln) SOURCE
ON (ps_sf_acctg_ln.ROWID = SOURCE.rid)
WHEN MATCHED THEN UPDATE SET ps_SF_ACCTG_LN.SF_LINE_NBR = SOURCE.sf_line_nbr;
commit;

Sequential Numbers - SQL

This will creating a running sequence number for each unique combination of RUN_DT and SEQNUM; NEW_SF_LINE_NBR

SELECT
run_dt,
seqnum,
ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt, seqnum) new_sf_line_nbr
FROM ps_sf_acctg_ln

Using DateTime Fields in SQR - Gotcha #343

This will return unexpected results:

AND A.CLASS_PRICE_DTTM <= $RC_FromDate

This will return expected results:

AND A.CLASS_PRICE_DTTM <= to_date($RC_FromDate, 'DD-MON-YYYY')

Where $RC_FromDate is a variable populated from a Date field on your Run Control page.

DATETIMESTAMP Field - Inserting into in SQR or SQL

UPDATE TABKLE_X
set LAST_UPDATE_DTTM = to_timestamp($SysDateTime, 'DD-MON-YYYY_HH:MI:SS.FF6_AM')

...something about version 9 :S