Error: Syntax error: expected something between '(' and the 'select' keyword.

Database
Enthusiast

Error: Syntax error: expected something between '(' and the 'select' keyword.

I have this code below where I’m attempting to combine the CLAIM_PMNT and the CLM_LOC tables together. I need to get a running total column built and am not sure how to do that.

select b.i_sys_clm
,a.c_pym_det as "Pmnt Detail"
,(case
when c_loc in ('09', '10', '11') then '06'
when c_loc = '99' then Null
else c_loc
end) as "Location Code"
,a.d_bgn_pym_det as "Pmnt Date"

,a.d_bgn_pym_det as "From Date"

,a.d_end_pym_det as "To Date"

,a.a_pym_det as "Payment"

,b.d_rls_pym

,c.c_pol

<-------------Next Line Generating Error

, (select sum(sq.a_pym_det) from BCPM_DDBO.VLTC916A_PYD sq where sq.d_bgn_pym_det <= a.d_bgn_pym_det) as "RunningTotal"

--------->

from BCPM_DDBO.VLTC916A_PYD a
left join (select i_sys_clm

,i_sys_pym

,d_rls_pym

from BCPM_DDBO.vltc915a_pym

where d_rls_pym is not null) b

on a.i_sys_pym = b.i_sys_pym

left join (select c_pol

,i_sys_clm

from BCPM_DDBO.vltc900a_clm) c

on b.i_sys_clm = c.i_sys_clm

where c.c_pol not between 'LAC000001' and 'LAC0009999' and

b.i_sys_clm = 36922

Error: Syntax error: expected something between '(' and the 'select' keyword.

4 REPLIES
Senior Apprentice

Re: Error: Syntax error: expected something between '(' and the 'select' keyword.

There's good news and bad news :-)

The bad news:
Teradata still doesn't support Scalar Subqueries within the SELECT list, yet (This will be implemented in TD13)

The good news:
In most cases those Scalar Subqueries are easily rewritten as Outer Joins.
But in your case there's a much better solution using Stabdard SQL OLAP functions:
SUM(sq.a_pym_det) OVER (ORDER BY sq.d_bgn_pym_det ROWS UNBOUNDED PRECEDING) AS "RunningTotal"

Btw, the optimizer will replace those Left Joins with Inner Joins because it will be the same result.

Thus it might be rewritten as:

from BCPM_DDBO.VLTC916A_PYD a
join BCPM_DDBO.vltc915a_pym b
on a.i_sys_pym = b.i_sys_pym
join BCPM_DDBO.vltc900a_clm c
on b.i_sys_clm = c.i_sys_clm
where c.c_pol not between 'LAC000001' and 'LAC0009999' and
b.i_sys_clm = 36922

Dieter
Enthusiast

Re: Error: Syntax error: expected something between '(' and the 'select' keyword.

Thanks a million!!!!

Re: Error: Syntax error: expected something between '(' and the 'select' keyword.

hi all,

i am also receiving the same error in my query. initially,this query was used in a SQL2000 DTS. i tried to run it from Teradata SQL Assistant. here is the simplified version of the query:

SELECT
id,
trans_dt = i.dt,

FROM
table1 i,


been stuck with this for past few days..any help is much appreciated
Enthusiast

Re: Error: Syntax error: expected something between '(' and the 'select' keyword.

Swetha,

try this..

SELECT
id,
i.dt trans_dt,

FROM
table1 i,

cheers:-)