3706: Syntax error: expected something between ')' and ','. For derived

Analytics
N/A

3706: Syntax error: expected something between ')' and ','. For derived

Hi TD newbie here,
am getting the 3706: Syntax error: expected something between ')' and ','.
for the following query

SELECT P.SRCSYS_CODE,
P.SUP_NM,
L.SRC_LOC_CD,
L.LOC_NAME,
D.DEST,
COUNT(ITM_NUM),
COUNT(ORD_NUM),
COUNT(ORD_LN)
FROM (SELECT ORD_NUM,
ORD_LN,
ORD_LN_ST,
ITM_NUM,
ORD_QTY,
LOC_NAME AS DEST
FROM ORD_DET A, LOC B
WHERE A.LOC_ID = B.LOC_ID
AND A.ORD_LN_ST = 'CLOSED') D,
ORD O, PAR P, LOC L
WHERE D.ORD_NUM = O.ORD_NUM
AND O.PAR_ID = P.PAR_ID
AND O.LOC_ID = L.LOC_ID
GROUP BY 1,2,3,4,5 ;

am not sure where the problem is?
any help appreciated.
18 REPLIES

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Try this (assuming the counts that are from the drived table).

SELECT P.SRCSYS_CODE,
P.SUP_NM,
L.SRC_LOC_CD,
L.LOC_NAME,
D.DEST,
COUNT(D.ITM_NUM),
COUNT(D.ORD_NUM),
COUNT(D.ORD_LN)
FROM (SELECT ORD_NUM,
ORD_LN,
ORD_LN_ST,
ITM_NUM,
ORD_QTY,
LOC_NAME AS DEST
FROM ORD_DET A, LOC B
WHERE A.LOC_ID = B.LOC_ID
AND A.ORD_LN_ST = 'CLOSED')AS D,
ORD O, PAR P, LOC L
WHERE D.ORD_NUM = O.ORD_NUM
AND O.PAR_ID = P.PAR_ID
AND O.LOC_ID = L.LOC_ID
GROUP BY 1,2,3,4,5 ;
N/A

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Nope,
did not work... same error as before

Re: 3706: Syntax error: expected something between ')' and ','. For derived

OK, what about
SELECT P.SRCSYS_CODE,
P.SUP_NM,
L.SRC_LOC_CD,
L.LOC_NAME,
D.DEST,
COUNT(D.ITM_NUM),
COUNT(D.ORD_NUM),
COUNT(D.ORD_LN)
FROM (SELECT ORD_NUM,
ORD_LN,
ORD_LN_ST,
ITM_NUM,
ORD_QTY,
LOC_NAME AS DEST
FROM ORD_DET A, LOC B
WHERE A.LOC_ID = B.LOC_ID
AND A.ORD_LN_ST = 'CLOSED')AS D
inner join ORD O
on D.ORD_NUM = O.ORD_NUM
inner join PAR P
on D.ORD_NUM = O.ORD_NUM
inner join LOC L
on O.LOC_ID = L.LOC_ID
GROUP BY 1,2,3,4,5 ;
N/A

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Nope still the same.
I think it has something to do with the derived table.
Have not been able work on this today, but will keep trying.

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Hi,
what about defining alias for each field of the derived table???
I haven't tested your query, but I would use correct aliasing in using derived tables as a general behaviour....

Bye,
TDUser
Teradata Employee

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Is your query perhaps being submitted in some manner that requires a name be assigned to each field? In other words, do you need

COUNT(ITM_NUM) C1,
COUNT(ORD_NUM) C2,
COUNT(ORD_LN) C3

Also, are you really intending to simply count non-null occurrences, or did you want COUNT(DISTINCT ...)?
N/A

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Thanks for the replies guys.
Correct aliasing seems to have fixed the problem.

Thanks again.

Re: 3706: Syntax error: expected something between ')' and ','. For derived

Hi, I donot know your exact problem definition , but some how simulated one case like you and tested it its working fine. below is the template i used.
I guess you need to verify the place of joinging conditions and as well as correct aliasing.

SELECT
A.col1,
sq.col1,
count(sq.col2),
count(A.col2),
count(E.col1)
FROM
Tab A inner join
(SELECT
C.col1, C.col2
FROM
Tab C, Tab D
WHERE
C.col1=D.col1 AND
C.col3='C') sq on (A.col=sq.col1)
inner join Tab E on (A.col=E.col)
group by 1,2

Thanks,
UParida

Re: 3706: Syntax error: expected something between ')' and ','. For derived

hi can u please tell me the query to update a column in table based upon joining with 1 or more tables(with inner join) in teradata

Thanks,

Yoga