By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

ERROR 3612 - multi level group by clause

P: n/a
Hi... Can someone please help me? Im trying to run a report using the query
below...
SELECT [Account] & "_" & [BrokerCode] AS [Group],
dbo_SDB_ABM0270.KYKMEIKT,
dbo_viewPOMS_RegRep.[Custodian Name],
dbo_viewPOMS_RegRep.Account,
dbo_viewPOMS_RegRep.AcctName,
dbo_viewPOMS_RegRep.Side,
IIf((Select Count(*) from dbo_SDB_ABM0070 ABM70 where (ABM70.JHKISNCD =
dbo_viewPOMS_RegRep.Identifier) and (LEN(TRIM(ABM70.TSOMGRCD)) < 12)) 1,
null,
(Select Top 1 IIf(Left([ABM70.TSOMGRCD],3)="000",Mid([ABM70.TSOMGRCD],4),
[ABM70.TSOMGRCD]) from dbo_SDB_ABM0070 ABM70 where (ABM70.JHKISNCD =
dbo_viewPOMS_RegRep.Identifier) and (LEN(TRIM(ABM70.TSOMGRCD)) < 12))) AS
InternalCode,

dbo_viewPOMS_RegRep.JPTradeDate,
dbo_viewPOMS_RegRep.BrokerName,
dbo_viewPOMS_RegRep.ReasonCode

FROM dbo_viewPOMS_RegRep LEFT JOIN dbo_SDB_ABM0270 ON dbo_viewPOMS_RegRep.
dbo_tblPOMS_AdminFeed_Dat023 = dbo_SDB_ABM0270.KYKCD
WHERE (((dbo_viewPOMS_RegRep.JPTradeDate)=CDate(Format([ENTER JP TRADE DATE],
"0000-00-00"))));

However I keep getting the error. If I remove the :
IIf((Select Count(*) from dbo_SDB_ABM0070 ABM70 where (ABM70.JHKISNCD =
dbo_viewPOMS_RegRep.Identifier) and (LEN(TRIM(ABM70.TSOMGRCD)) < 12)) 1,
null,
(Select Top 1 IIf(Left([ABM70.TSOMGRCD],3)="000",Mid([ABM70.TSOMGRCD],4),
[ABM70.TSOMGRCD]) from dbo_SDB_ABM0070 ABM70 where (ABM70.JHKISNCD =
dbo_viewPOMS_RegRep.Identifier) and (LEN(TRIM(ABM70.TSOMGRCD)) < 12))) AS
InternalCode

which checks duplicate data then it works. Reason I have to put the above so
that if it detects a duplicate data it will show null in the report.

Please help.
Thank you.

--
Message posted via http://www.accessmonster.com

Feb 19 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.