SQL Select Maximum Time

[mohdhaniff]

Hi,

I'm having data with multiple records in database and wish to extract out as single records by using maximum time stamp known as PPN_TM but the formula not work.

Can anybody help me to figure out the right script:

Set Server = 'DSSBDW';

ODBC CONNECT TO MYIMBDW;

SQL select * from (

SQL SELECT
A.AR_ID,
B.SHD_DT,
B.PYMT_RMN_NBR,
B.AVY_TP_ID,
B.PPN_TM,

left(B.PPN_TM,10) as Date

FROM
$(Server).AR A
LEFT jOIN $(Server).AR_AVY_SHD B ON A.AR_ID=B_.AR_ID
(SQL SELECT Max(PPN_TM) maxPPN_TM, AR_ID FROM DSSBDW.AR_AVY_SHD GROUP BY AR_ID) F on A.AR_ID = F.AR_ID

WHERE
B.PPN_TM >= '2017-03-15-10.44.34.290169') as X where PPN_TM=maxPPN_TM;

Regards

[sushil353]

Hi,

Check out the below condition:

Where

B.PPN_TM >= '2017-03-15-10.44.34.290169') and PPN_TM = max(PPN_TM);

[mohdhaniff]

Thanks Sushil,

I still getting below error.

SQL##f - SqlState: 42601, ErrorCode: 4294967192, ErrorMsg: [IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token "AR" was found following "from (    SQL SELECT".  Expected tokens may include:  "(".  SQLSTATE=42601

May be my below script also contain error:

SQL select * from (

SQL SELECT
A.AR_ID,

or somewhere here:

(SQL SELECT Max(PPN_TM) maxPPN_TM, AR_ID FROM DSSBDW.AR_AVY_SHD GROUP BY AR_ID) F on A.AR_ID = F.AR_ID

[sasiparupudi1]

TempTable1:

SQL SELECT

A.AR_ID,

B.SHD_DT,

B.PYMT_RMN_NBR,

B.AVY_TP_ID,

B.PPN_TM,

left(B.PPN_TM,10) as Date

FROM

$(Server).AR A

LEFT jOIN $(Server).AR_AVY_SHD B ON A.AR_ID=B_.AR_ID;

Left Join(TempTable1)

SQL SELECT

    Max(PPN_TM) maxPPN_TM,

    AR_ID

FROM DSSBDW.AR_AVY_SHD GROUP BY AR_ID) F on A.AR_ID = F.AR_ID

WHERE

B.PPN_TM >= '2017-03-15-10.44.34.290169');

FinalTabel:

NoConcatenate LOAD

*

Resident TempTable1

where PPN_TM=maxPPN_TM;

Drop Table TempTable1;

Please check if the left function in left(B.PPN_TM,10) is supported by the database

hth

Sas