topic Re: SQL Select Maximum Time in QlikView

SQL Select Maximum Time

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: SQL Select Maximum Time

sushil353 — Thu, 16 Mar 2017 04:49:14 GMT

Hi,

Check out the below condition:

Where

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

Re: SQL Select Maximum Time

mohdhaniff — Thu, 16 Mar 2017 05:00:51 GMT

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

Re: SQL Select Maximum Time

sasiparupudi1 — Thu, 16 Mar 2017 07:31:45 GMT

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