Select Failed 3706: Syntax error: expected something between the word 'sasnref' and the word 'EXCEPTION'

Analytics

Select Failed 3706: Syntax error: expected something between the word 'sasnref' and the word 'EXCEPTION'

I am troubling over this error as I am not sure what the cause might be. Any help would be greatly appreciated.

select sasnref.trx_date, count(OCS.SERVED_MOBILE_NUMBER)

from

(

select sasn.trx_date,  sasn.SERVED_MOBILE_NUMBER

from

(

SELECT trx_date, SERVED_MOBILE_NUMBER, APN, SUM( VOLUME_UPLINK) uplink,SUM(VOLUME_DOWNLINK) downlink

FROM EDWIN_V.SASN_FACT_UDR_DAILY_201305

where CAST((trx_date (format 'yyyymmdd')) as char(8)) =  '20130501' 

group by  trx_date , SERVED_MOBILE_NUMBER, APN

) sasn inner join

(

SELECT execution_time,  count(*) num_trx,  APPS_ID, SERVED_MOBILE_NUMBER, REQ_REGION, REQ_HOME,  CHARGING_ID

FROM EDWIN_V.REFLEX_FACT_201305

where apps_id='fsd' and STATUS_FLOW_REQUEST='OK00' and wording_add not like '%GIFT%' and execution_status = 'S'  and charging_id='flash2k'

and cast( EXECUTION_TIME as date format 'yyyymmdd') = '20130501'

group by  APPS_ID, SERVED_MOBILE_NUMBER, REQ_REGION, REQ_HOME, CHARGING_ID, execution_time

) reflex

on  sasn.SERVED_MOBILE_NUMBER = reflex.SERVED_MOBILE_NUMBER and CAST((reflex.execution_time (format 'yyyymmdd')) as char(8)) = CAST((sasn.trx_date (format 'yyyymmdd')) as char(8))

where (uplink + downlink) > 5242880

) sasnref EXCEPTION JOIN 

(

SELECT CAST((trx_date (format 'yyyymmdd')) as char(8)) trx_date, SERVED_MOBILE_NUMBER

FROM EDWIN_V.FACT_OCS_201305

where CAST((trx_date (format 'yyyymmdd')) as char(8)) = '20130501'

group by CAST((trx_date (format 'yyyymmdd')) as char(8)), SERVED_MOBILE_NUMBER

)  OCS

on  sasnref.trx_date = OCS.trx_date and sasnref.SERVED_MOBILE_NUMBER = OCS.SERVED_MOBILE_NUMBER

group by sasnref.trx_dat

result: Select Failed 3706: Syntax error: expected something between the word 'sasnref' and the word 'EXCEPTION'

2 REPLIES
N/A

Re: Select Failed 3706: Syntax error: expected something between the word 'sasnref' and the word 'EXCEPTION'

Did you work with DB2 before?

EXCEPTION JOIN is no Standard SQL (unless it was recently introduced).

When you need data from the 2nd table you can rewrite it using a LEFT JOIN plus a WHERE join_column_from_right_table IS NULL.

In your case it's probably better to use a NOT EXISTS.

Some additional remarks:

trx_date is a timestamp? Then better CAST(trx_date as DATE) instead of a formatted CHAR(8) for the join condition, you'll loose any existing statistics on that column and if it's a partitioning column, there's no partition elimination anymore.

And depending on your release you might also consider WHERE trx_date >= timestamp  '2013-05-01 00:00:00'  and trx_date < timestamp  '2013-05-02 00:00:00'

Moving the "where (uplink + downlink) > 5242880" in the Derived Table as "HAVING (uplink + downlink) > 5242880" might also result in a better plan

Dieter

Re: Select Failed 3706: Syntax error: expected something between the word 'sasnref' and the word 'EXCEPTION'

Thank You Dieter... its work :)