subselect "An unexpected token "Count" error 1

[RustyAfro]

I need to run a select within a select, but encounter an unexpected token error. Below is a very simplified version of the SQL statement. The actual statment has many joins to other tables and I cannot join to the comment table as it multiplies the rows excessively. I suppose I could do a group by, but this syntax works in other databases, so I don't understand why it is failing in DB2?

Here is the SQL:

SELECT PROPOSAL.PROPOSAL_ID,
(SELECT Count(c.PROPOSAL_ID) FROM PRED."COMMENT" c WHERE c.PROPOSAL_ID=PROPOSAL.PROPOSAL_ID) AS CountComments

FROM PRED.PROPOSAL PROPOSAL

I'm fairly new to db2 syntax and would really appreciate any help. Thanks ;-)

Joel

 

[RustyAfro]

Is there anyone able to help with this problem?

 

[LKBrwnDBA]

Try this:

SELECT PROPOSAL.PROPOSAL_ID, CountComments
  FROM PRED.PROPOSAL PROPOSAL
LEFT JOIN (
     SELECT PROPOSAL_ID, Count(c.PROPOSAL_ID)  AS CountComments FROM PRED."COMMENT" 
      GROUP BY PROPOSAL_ID) c
  ON c.PROPOSAL_ID=PROPOSAL.PROPOSAL_ID

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MarcLodge]

Is the sql that you have omitted too complicated to allow a GROUP BY?

This would seem the most obvious path to take.

SELECT PROPOSAL.PROPOSAL_ID, Count(c.PROPOSAL_ID)
FROM PRED.PROPOSAL PROPOSAL
    ,PRED."COMMENT" c
WHERE c.PROPOSAL_ID=PROPOSAL.PROPOSAL_ID
GROUP BY PROPOSAL.PROPOSAL_ID

 

[RustyAfro]

LK, thanks for the sample, that is exactly what i was trying to do. My final code looked liked this for the join:

LEFT  JOIN (SELECT cs.PROPOSAL_ID, Count(cs.PROPOSAL_ID)  AS COUNT_COMMENTS FROM PRED."COMMENT" cs
      GROUP BY cs.PROPOSAL_ID) c
  ON c.PROPOSAL_ID=PROPOSAL.PROPOSAL_ID

Marc, I tried group by as well and it worked, but it was a lot harder than the sub-select because of the sheer amount of unique tables. Here it is below. I wonder though about performance...they both seem to run about equally fast, though I can't say for sure yet. Any feedback on which one would be faster (group by or sub select)?

 SELECT "PROPOSAL"."PROPOSAL_ID" AS PR_PROPOSAL_ID_Int,
CAST("PROPOSAL"."PROPOSAL_ID" AS VarChar(20)) AS PR_PROPOSAL_ID_Str,
"PROPOSAL"."PROPOSAL_DESC" AS PR_PROPOSAL_DESC , 
"PROPOSAL"."GROUP_ID" AS PR_GROUP_ID, 
"PROPOSAL"."FTE_EMP_COUNT" AS PR_FTE_EMP_COUNT , 
"PROPOSAL"."SIC_CD" AS PR_SIC_CD , 
"PROPOSAL"."INSUREDS_COUNT" AS PR_INSUREDS_COUNT , 
"PROPOSAL"."RATE_DATE" AS PR_RATE_DATE , 
"PROPOSAL"."SOLD_DATE" AS PR_SOLD_DATE , 
"PROPOSAL"."SALES_FU_STS_CODE" AS PR_SALES_FU_CODE , 
"PROPOSAL"."COV_EFFECTIVE_DATE" AS PR_COV_EFF_DATE , 
PROPOSAL.LEAD_UW_NOTIFGRPID AS PR_UW_NOTIFGRPID , 
PROPOSAL.EXP_AVAILABLE_IND as PR_RATE_METHOD,
DATE(PROPOSAL.CREATE_TMSTMP) AS PR_CREATE_TMSTMP ,

COALESCE(PROPOSAL.SALES_FU_DATE, PROPOSAL.RATE_DATE) AS PR_RECVD_DATE,

CASE WHEN PROPOSAL.EXP_AVAILABLE_IND = 'Y' THEN 10
          WHEN COALESCE(MONTH(PROPOSAL.SALES_FU_DATE), MONTH(PROPOSAL.RATE_DATE))
 >=1 and COALESCE(MONTH(PROPOSAL.SALES_FU_DATE), MONTH(PROPOSAL.RATE_DATE)) <8 THEN 3 
ELSE 2
END as PR_DaysAllowed,

CAST("PROPOSAL_SCENARIO"."PROP_SCENARIO_ID" AS VarChar(20)) AS PS_SCENARIO_ID_Str,

CASE PROPOSAL_SCENARIO.PROP_SCEN_STATUS
  WHEN 'A' THEN 'Approved'
  WHEN 'D' THEN 'Disapproved'
  WHEN 'P' THEN 'Pended'
  WHEN 'U' THEN 'Unrated'
  ELSE  "PROPOSAL_SCENARIO"."PROP_SCEN_STATUS" 
END AS PS_Status,

DATE(PROPOSAL_SCENARIO.STATUS_DATE) AS PS_STATUS_DATE,    

1 AS PS_Count_Scenarios,

COUNT(COMMENTS.PROPOSAL_ID) AS COUNT_COMMENTS,

COALESCE(DAYS(PROPOSAL.SALES_FU_DATE), MONTH(PROPOSAL.RATE_DATE)) - COALESCE(DAYS(PROPOSAL.SALES_FU_DATE), MONTH(PROPOSAL.RATE_DATE)) as PS_Count_UWDays,

"RATING_POOL"."SHORT_DESC" AS RatePool, 
"CODE_LOOKUP"."DESCRIPTION" AS ProdDesc,
CODE_LOOKUP.CHARACTER_CODE  as ProdType,

CASE COALESCE("NOTIF_GROUP"."NOTIF_GRP_DESC", COALESCE(RTRIM("USER".LAST_NAME)||', '||RTRIM("USER".FIRST_NAME)||' '||"USER".MIDDLE_INITIAL, 'N/A')) 
    When 'Mouse, Mickey'   THEN 'South'
    Else 'North'
END AS ASSC_UW_REGION,

COALESCE("NOTIF_GROUP"."NOTIF_GRP_DESC", COALESCE(RTRIM("USER".LAST_NAME)||', '||RTRIM("USER".FIRST_NAME)||' '||"USER".MIDDLE_INITIAL, 'N/A')) As ASSC_UW_Name,
COALESCE(RTRIM(AE.LAST_NAME)||', '||RTRIM(AE.FIRST_NAME)||' '||AE.MIDDLE_INITIAL, 'N/A') As Assc_AE_NAME,
COALESCE(DESCRIPTION.DESCRIPTION_DESC,'N/A') AS Assc_AE_TYPE,  
COALESCE(RTRIM(AM.LAST_NAME)||', '||RTRIM(AM.FIRST_NAME)||' '||AM.MIDDLE_INITIAL, 'N/A') As Assc_AM_NAME,
COALESCE(DESCRIPTION2.DESCRIPTION_DESC,'N/A') AS Assc_AM_TYPE

 FROM   
PRED.PROPOSAL PROPOSAL
INNER JOIN PRED.PROPOSAL_SCENARIO PROPOSAL_SCENARIO ON 		PROPOSAL.PROPOSAL_ID=PROPOSAL_SCENARIO.PROPOSAL_ID AND  PROPOSAL.SIMULATED_RATE_IND = 'N' AND PROPOSAL_SCENARIO.PROP_SCEN_STATUS <>  'U' 

INNER JOIN "PRED"."RATING_POOL" "RATING_POOL" ON "PROPOSAL"."RATING_POOL"="RATING_POOL"."RATING_POOL"

INNER JOIN "PRED"."PROPOSAL_PROD_TYPE" "PROPOSAL_PROD_TYPE" ON 		
"PROPOSAL"."PROPOSAL_ID"="PROPOSAL_PROD_TYPE"."PROPOSAL_ID"
INNER JOIN "PRED"."CODE_LOOKUP" "CODE_LOOKUP" ON
"PROPOSAL_PROD_TYPE"."PRODUCT_OFFERING"="CODE_LOOKUP"."CHARACTER_CODE"

LEFT OUTER JOIN PRED."COMMENT" COMMENTS ON COMMENTS.PROPOSAL_ID=PROPOSAL.PROPOSAL_ID

LEFT OUTER JOIN "PRED"."NOTIF_GROUP" "NOTIF_GROUP" ON "PROPOSAL"."LEAD_UW_NOTIFGRPID"="NOTIF_GROUP"."NOTIF_GRP_NUM"
LEFT OUTER JOIN "PRED"."USER" "USER" ON "PROPOSAL"."LEAD_UW_OPID"="USER"."OPID"

LEFT OUTER JOIN PRED.OPERATOR_PROPOSAL OPERATOR_PROPOSAL ON  PROPOSAL.PROPOSAL_ID = OPERATOR_PROPOSAL.PROPOSAL_ID  AND OPERATOR_PROPOSAL.SEQUENCE_NUM = 1  
LEFT OUTER JOIN PRED.USER AE ON  AE.OPID = OPERATOR_PROPOSAL.OPID  
LEFT OUTER JOIN PRED.FARE_USER FARE_USER ON FARE_USER.OPID = AE.OPID  
LEFT OUTER JOIN PRED.DESCRIPTION DESCRIPTION ON DESCRIPTION.DESCRIPTION_NUM = FARE_USER.OPER_TYPE_NUM 

LEFT OUTER JOIN PRED.OPERATOR_PROPOSAL OPERATOR_PROPOSAL2 ON  PROPOSAL.PROPOSAL_ID = OPERATOR_PROPOSAL2.PROPOSAL_ID  AND OPERATOR_PROPOSAL2.SEQUENCE_NUM = 2  
LEFT OUTER JOIN PRED.USER AM ON  AM.OPID = OPERATOR_PROPOSAL2.OPID  
LEFT OUTER JOIN PRED.FARE_USER FARE_USER2 ON FARE_USER2.OPID = AM.OPID  
LEFT OUTER JOIN PRED.DESCRIPTION DESCRIPTION2 ON DESCRIPTION2.DESCRIPTION_NUM = FARE_USER2.OPER_TYPE_NUM 
 
WHERE  ((DATE(PROPOSAL.RATE_DATE)>={?Start_Date} AND Date(PROPOSAL.RATE_DATE)<{?End_Date})) AND
CODE_LOOKUP.CODE_TYPE = 'PO' 

GROUP BY
PROPOSAL.PROPOSAL_ID, 
PROPOSAL.PROPOSAL_DESC, 
PROPOSAL.GROUP_ID, 
PROPOSAL.FTE_EMP_COUNT, 
PROPOSAL.SIC_CD, 
PROPOSAL.INSUREDS_COUNT,
PROPOSAL.RATE_DATE, 
PROPOSAL.SOLD_DATE,
PROPOSAL.SALES_FU_STS_CODE,
PROPOSAL.COV_EFFECTIVE_DATE, 
PROPOSAL.LEAD_UW_NOTIFGRPID,
PROPOSAL.EXP_AVAILABLE_IND,
PROPOSAL.CREATE_TMSTMP,
PROPOSAL.SALES_FU_DATE, 
PROPOSAL.RATE_DATE,
PROPOSAL.EXP_AVAILABLE_IND, 
PROPOSAL.SALES_FU_DATE, 
PROPOSAL.RATE_DATE,

PROPOSAL_SCENARIO.PROP_SCENARIO_ID,
PROPOSAL_SCENARIO.PROP_SCEN_STATUS,
PROPOSAL_SCENARIO.STATUS_DATE,    

RATING_POOL.SHORT_DESC, 

CODE_LOOKUP.DESCRIPTION,
CODE_LOOKUP.CHARACTER_CODE,

NOTIF_GROUP.NOTIF_GRP_DESC,

USER.LAST_NAME,
USER.FIRST_NAME,
USER.MIDDLE_INITIAL, 

AE.LAST_NAME,
AE.FIRST_NAME,
AE.MIDDLE_INITIAL, 
DESCRIPTION.DESCRIPTION_DESC,

AM.LAST_NAME,
AM.FIRST_NAME,
AM.MIDDLE_INITIAL,
DESCRIPTION2.DESCRIPTION_DESC