Hello Mendix OQL gurus, This is the query I am trying to use for my report data set: Select CASE a.Status WHEN 'ClosedWon' THEN sum(a.BookingValue) ELSE 0 END won_amount, CASE a.Status WHEN 'ClosedWon' THEN 0 ELSE sum(a.BookingValue) END lost_amount, sum(a.BookingValue) total_amount, CASE a.Status WHEN 'ClosedWon' THEN count(1) ELSE 0 END won_count, CASE a.Status WHEN 'ClosedWon' THEN 0 ELSE count(1) END lost_count, count(1) total_count FROM SPXSalesManagerPersona.MyAccountManager AS sm JOIN Administration.Account AS useracct ON useracct/Name = sm.UserId JOIN SPXMain.Opportunity AS a ON a/SPXMain.OpportunityLeader/Administration.Account/Name = useracct/Name WHERE sm/SPXSalesManagerPersona.MyAccountManager_Account = '[%CurrentUser%]' AND a.Status != 'Open' When I generate the report, I get a runtime error. Caused by: java.sql.SQLSyntaxErrorException: unexpected token: INNER : line: 10 in statement [SELECT CASE "a"."status" WHEN ? THEN SUM("a"."bookingvalue") ELSE 0 END AS "won_amount", CASE "a"."status" WHEN ? THEN 0 ELSE SUM("a"."bookingvalue") END AS "lost_amount", SUM("a"."bookingvalue") AS "total_amount", CASE "a"."status" WHEN ? THEN COUNT(1) ELSE 0 END AS "won_count", CASE "a"."status" WHEN ? THEN 0 ELSE COUNT(1) END AS "lost_count", COUNT(1) AS "total_count" FROM "spxsalesmanagerpersona$myaccountmanager" "sm" INNER JOIN "spxsalesmanagerpersona$myaccountmanager_account" "c1spxsalesmanagerpersona$myaccountmanager_account" ON "c1spxsalesmanagerpersona$myaccountmanager_account"."spxsalesmanagerpersona$myaccountmanagerid" = "sm"."id" INNER JOIN "administration$account" "acct" INNER JOIN "system$user" "dj2system$user" ON "dj2system$user"."name" = "sm"."userid" INNER JOIN "spxmain$opportunity" "a" INNER JOIN "spxmain$opportunityleader" "a5spxmain$opportunityleader" ON "a5spxmain$opportunityleader"."spxmain$opportunityid" = "a"."id" INNER JOIN "administration$account" "j3administration$account" ON "j3administration$account"."id" = "a5spxmain$opportunityleader"."administration$accountid" INNER JOIN "system$user" "dj4system$user" ON "dj4system$user"."name" = "dj2system$user"."name" WHERE "c1spxsalesmanagerpersona$myaccountmanager_account"."administration$accountid" = 12666373951979728 AND ("a"."status" != ? OR "a"."status" IS NULL)] Line 10 is : INNER JOIN "administration$account" "acct" . Administration.Account is a specialization of System.User, so I could use that in the query. However, when I do that, I get the same error, but on a different line: Caused by: java.sql.SQLSyntaxErrorException: unexpected token: INNER : line: 11 in statement [SELECT CASE "a"."status" WHEN ? THEN SUM("a"."bookingvalue") ELSE 0 END AS "won_amount", CASE "a"."status" WHEN ? THEN 0 ELSE SUM("a"."bookingvalue") END AS "lost_amount", SUM("a"."bookingvalue") AS "total_amount", CASE "a"."status" WHEN ? THEN COUNT(1) ELSE 0 END AS "won_count", CASE "a"."status" WHEN ? THEN 0 ELSE COUNT(1) END AS "lost_count", COUNT(1) AS "total_count" FROM "spxsalesmanagerpersona$myaccountmanager" "sm" INNER JOIN "spxsalesmanagerpersona$myaccountmanager_account" "c1spxsalesmanagerpersona$myaccountmanager_account" ON "c1spxsalesmanagerpersona$myaccountmanager_account"."spxsalesmanagerpersona$myaccountmanagerid" = "sm"."id" INNER JOIN "system$user" "useracct" ON "useracct"."name" = "sm"."userid" INNER JOIN "spxmain$opportunity" "a" INNER JOIN "spxmain$opportunityleader" "a4spxmain$opportunityleader" ON "a4spxmain$opportunityleader"."spxmain$opportunityid" = "a"."id" INNER JOIN "administration$account" "j2administration$account" ON "j2administration$account"."id" = "a4spxmain$opportunityleader"."administration$accountid" INNER JOIN "system$user" "dj3system$user" ON "dj3system$user"."name" = "useracct"."name" WHERE "c1spxsalesmanagerpersona$myaccountmanager_account"."administration$accountid" = 12666373951979728 AND ("a"."status" != ? OR "a"."status" IS NULL)] Line 11 is: "spxmain$opportunity" "a" INNER JOIN "spxmain$opportunityleader" How do I get around this?
asked
Sunder Iyer
1 answers
0
I think the aggregate functions should be around the case statements.