Query Fails in OLEDB but not in ODBC

[RustyAfro]

Hard to explain this one, but I had the below query working ok in an OLEDB data source but when I added a few more joins it failed. After editing the command, the query would look like it was running but then pop up the window that asks to map fields that are missing. Strangely enough no fields where there to map, just a data provider named "command" (it is actually called "main"). It like the query ran but didn't return any data or any fields.

I was able to get the query to work in Access using ODBC so on a hunch tried this one in an ODBC query in Crystal as well and it worked.

The problem is that all the objects in the report are linked to the OLEDB query and I can't figure out how to switch them to the ODBC query. It's a very complex report as you can tell from the very long query and rebuilding it would be a nightmare... any advice?

Crystal XI R2
DB2 Database

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,
PROPOSAL_SCENARIO.PROP_SCENARIO_NAME AS PS_SCENARIO_DESC,

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,    

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

COALESCE(c.COUNT_COMMENTS, 0) AS CM_COUNT,
CAST(COMMENTS.OBJECT_ID AS VarChar(20)) AS CM_SC_LINK,

CASE WHEN COMMENT_TYPE.COMM_TYPE_NUM = 9 THEN 
CAST(PROPOSAL_SCENARIO.PROP_SCENARIO_ID AS VarChar(20))
END as CM_EXCEPTION_SCS,

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(USERS.LAST_NAME)||', '||RTRIM(USERS.FIRST_NAME)||' '||USERS.MIDDLE_INITIAL, 'N/A')) 
    When 'Mouse, Mickey'   THEN 'South'
    When 'REG SOUTH Work Queue' THEN 'South'
    Else 'North'
END AS ASSC_UW_REGION,

COALESCE(NOTIF_GROUP.NOTIF_GRP_DESC, COALESCE(RTRIM(USERS.LAST_NAME)||', '||RTRIM(USERS.FIRST_NAME)||' '||USERS.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,

A1.ANSWER_TEXT AS SALES_RFQ_RCVD,
A2.ANSWER_TEXT AS SALES_DATE_ND,
A3.ANSWER_TEXT AS SALES_DATE_RLS

 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.NOTIF_GROUP NOTIF_GROUP ON PROPOSAL.LEAD_UW_NOTIFGRPID=NOTIF_GROUP.NOTIF_GRP_NUM
LEFT OUTER JOIN PRED.USER USERS ON PROPOSAL.LEAD_UW_OPID=USERS.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   OPERATOR_PROPOSAL2.OPID  = AM.OPID
LEFT OUTER JOIN PRED.FARE_USER FARE_USER2 ON AM.OPID = FARE_USER2.OPID
LEFT OUTER JOIN PRED.DESCRIPTION DESCRIPTION2 ON FARE_USER2.OPER_TYPE_NUM = DESCRIPTION2.DESCRIPTION_NUM
 
LEFT OUTER JOIN PRED.COMMENT COMMENTS ON PROPOSAL.PROPOSAL_ID = COMMENTS.PROPOSAL_ID 
AND COMMENTS.OBJECT_ID = PROPOSAL_SCENARIO.PROP_SCENARIO_ID 
LEFT OUTER JOIN PRED.COMMENT_TYPE COMMENT_TYPE ON COMMENTS.COMM_TYPE_NUM = COMMENT_TYPE.COMM_TYPE_NUM AND COMMENT_TYPE.COMM_TYPE_NUM = 9

LEFT OUTER JOIN PRED.ANSWER_SET AN1 on AN1.GROUPING_NUM  = PROPOSAL.PROPOSAL_ID AND AN1.STATUS_IND   <> 'X'
LEFT OUTER JOIN PRED.ANSWER a1 ON a1.ANSWER_SET_NUM = AN1.ANSWER_SET_NUM AND SUBSTR(a1.QUESTION_TEXT, 1, 15)  = 'Date RFQ receiv'

LEFT OUTER JOIN PRED.ANSWER_SET AN2 on AN2.GROUPING_NUM  = PROPOSAL.PROPOSAL_ID AND AN2.STATUS_IND   <> 'X'
LEFT OUTER JOIN PRED.ANSWER a2 ON a2.ANSWER_SET_NUM = AN2.ANSWER_SET_NUM AND SUBSTR(a2.QUESTION_TEXT, 1, 15)  = 'Date proposal n'

LEFT OUTER JOIN PRED.ANSWER_SET AN3 on AN3.GROUPING_NUM  = PROPOSAL.PROPOSAL_ID AND AN3.STATUS_IND   <> 'X'
LEFT OUTER JOIN PRED.ANSWER a3 ON a3.ANSWER_SET_NUM = AN3.ANSWER_SET_NUM AND SUBSTR(a3.QUESTION_TEXT, 1, 15)  = 'Date proposal m'

LEFT OUTER 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

WHERE  
DATE(PROPOSAL.RATE_DATE)>={?Start_Date}
AND 
Date(PROPOSAL.RATE_DATE)<={?End_Date}
AND
CODE_LOOKUP.CODE_TYPE = 'PO'  
AND
PROPOSAL_SCENARIO.PROP_SCEN_STATUS <> 'U'
AND

(
COALESCE(NOTIF_GROUP.NOTIF_GRP_DESC, COALESCE(RTRIM(USERS.LAST_NAME)||', '||RTRIM(USERS.FIRST_NAME)||' '||USERS.MIDDLE_INITIAL, 'N/A')) = '{?UW_Name}'
OR
'*' = '{?UW_Name}'
)

AND

(
CASE COALESCE(NOTIF_GROUP.NOTIF_GRP_DESC, COALESCE(RTRIM(USERS.LAST_NAME)||', '||RTRIM(USERS.FIRST_NAME)||' '||USERS.MIDDLE_INITIAL, 'N/A')) 
    When 'Mouse, Mickey'   THEN 'South'
    When 'REG SOUTH Work Queue' THEN 'South'
    Else 'North'
END = '{?UW_Region}'
OR
'*' = '{?UW_Region}'
)

 

[satinsilhouette]

Did you verify that you had the latest OLEDB drivers for your database that you are querying? And are all the above functions supported by that database?

Thanks so much!
satinsilhouette

 

[synapsevampire]

Try copying and pasting the query into a Command Object in the ODBC environment, you'll see it listed as Add Command under the data source.

You'll also need to create and apply the parameters within the Command screen.

-k