All Forums Analytics
dsad 4 posts Joined 05/06
09 May 2006
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 querySELECT 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 DESTFROM ORD_DET A, LOC BWHERE A.LOC_ID = B.LOC_IDAND A.ORD_LN_ST = 'CLOSED') D,ORD O, PAR P, LOC LWHERE D.ORD_NUM = O.ORD_NUMAND O.PAR_ID = P.PAR_IDAND O.LOC_ID = L.LOC_IDGROUP BY 1,2,3,4,5 ;am not sure where the problem is?any help appreciated.

10 May 2006

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 DESTFROM ORD_DET A, LOC BWHERE A.LOC_ID = B.LOC_IDAND A.ORD_LN_ST = 'CLOSED')AS D,ORD O, PAR P, LOC LWHERE D.ORD_NUM = O.ORD_NUMAND O.PAR_ID = P.PAR_IDAND O.LOC_ID = L.LOC_IDGROUP BY 1,2,3,4,5 ;

dsad 4 posts Joined 05/06
10 May 2006

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

10 May 2006

OK, what aboutSELECT 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 DESTFROM ORD_DET A, LOC BWHERE A.LOC_ID = B.LOC_IDAND A.ORD_LN_ST = 'CLOSED')AS D inner join ORD Oon D.ORD_NUM = O.ORD_NUMinner join PAR Pon D.ORD_NUM = O.ORD_NUMinner join LOC Lon O.LOC_ID = L.LOC_IDGROUP BY 1,2,3,4,5 ;

dsad 4 posts Joined 05/06
10 May 2006

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.

TDUser-781 35 posts Joined 12/04
11 May 2006

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

Fred 1096 posts Joined 08/04
11 May 2006

Is your query perhaps being submitted in some manner that requires a name be assigned to each field? In other words, do you needCOUNT(ITM_NUM) C1,COUNT(ORD_NUM) C2,COUNT(ORD_LN) C3Also, are you really intending to simply count non-null occurrences, or did you want COUNT(DISTINCT ...)?

dsad 4 posts Joined 05/06
11 May 2006

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

Developer 3 posts Joined 05/06
11 May 2006

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.col2FROM Tab C, Tab DWHERE 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,2Thanks,UParida

yogaraj 1 post Joined 06/12
26 Jun 2012

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 

Thanks,
Yoga

4rajbij 2 posts Joined 06/13
22 Jun 2013

Hi all, I am facing this issue. I have been working for so long, still no solution on horizon. Can anyone please help me.
I have the following query.
select
lth.AccountingMonth_Id
,lth.Calendar_Id
from FND_FPER_WORKDB.COMM_loadtrackinghistory as lth) as A
join
(select PS.Transaction_Cd,
,PS.Source_Cd
,PS.Status_Cd
,PS.Policy_Id
,PS.TransactionReason_Cd
from
FND_CCML_STDDB_COM1.PP_PolicyStatus as PS) as B
ON PS.transaction_ts= lth.lastprocessed_dt
and source_cd='ABCD'
and lth.flow_Cd ='ABCD_jack'
join (select Policy.Agent_Id,
Policy.BasePolicy_Id
,Policy.PolicyBusinessKey_Id
from FND_fper_db_com1.Basepolicy Policy) as C
ON PS.Policy_Id = Policy.Policy_Id
AND PS.Source_Cd = Policy.Source_Cd
AND PS.Transaction_Ts >= Policy.Transaction_Ts
AND PS.Transaction_Ts < Policy.Revision_Ts
 
when I run this, I get an error saying syntax error: expected something between the word 'lth' and ')'. can anyone please shed some lights on this. Thank you!

bij

dnoeth 4628 posts Joined 11/04
23 Jun 2013

Is this the actual query?
There's no opening parenthesis for ") as A", simply remove it.
 
Dieter

Dieter

vigneshnathan23 2 posts Joined 05/14
08 May 2014

Hi TD,
 
I have created the table
 
Empid  location
888    bom
999    che
777    aem
444    ams
222    ban
333   ahm
666   aem
555   mas
111   che
 
from this table i need a location ban in the table as the first record and remaining records in the alphabatical order how can i write the query for this pls help me out

Qaisar Kiani 337 posts Joined 11/05
09 May 2014
SELECT * FROM TBL
WHERE LOCATION = 'BAN'
UNION ALL
SELECT * FROM TBL
WHERE LOCATION <> 'BAN'
ORDER BY LOCATION ASC

 
 

dnoeth 4628 posts Joined 11/04
09 May 2014

@Qaisar: there's no ORDER BY for individual SELECTs in a UNION :-)

SELECT * FROM tab
ORDER BY 
   CASE WHEN location = 'ban' then 0 else 1 END
  ,location

@vigneshnathan23: Please cretae a new topic for new questions.

Dieter

vigneshnathan23 2 posts Joined 05/14
11 May 2014

Hi TD,
Thanks for the reply i got the output.
Now i have another table in which i need only a female record and it should not have any males in the same section or same grade.
Table as follows,
gender      section       grade
M                  A             1
F                   B             2
F                   A              3
M                  C             3
M                  D              4
F                   D             3
F                   E              5
 

dnoeth 4628 posts Joined 11/04
12 May 2014

Hi vigneshnathan23,.
this should work:

QUALIFY MAX(gender) OVER (PARTITION BY section) = 'F'
    AND MAX(gender) OVER (PARTITION BY grade) = 'F'

2nd time: Please create a new topic for new questions.

Dieter

You must sign in to leave a comment.