November 18, 2010 at 5:41 am
Hi,
in one my SSRS project, I am using maulti value parameters for various paramaters.
The problw is that for three of the MVP (multi valued Parameter) I am getting error as:
Query Execution failed for dataset '%s' incorrect syntax near ','
This is not the case when I am selecting only one value from the list for these three Paramateres. Regardless to say that I am already using IN in place = operator.
the Paramaters in question are Marital Status, Visa Status and Disability Status.
The below is the T/SQL I have written in order to populate my Dataset.
Any help will be much appreciated.
SELECTRD.NEC_ID AS 'NEC Number',
CD.First_Name AS 'First Name',
CD.Middle_Name AS 'Middle Name',
CD.Last_Name AS 'Surname',
CONVERT(VARCHAR(10),DATEDIFF(YY, CD.DOB, GETDATE())) + '.' + CONVERT(VARCHAR(10), DATEDIFF(MM, CD.DOB, GETDATE()) - DATEDIFF(YY, CD.DOB, GETDATE()) * 12) AS 'Age',
DVM.DIV_NAME AS 'Division',
PM.Pro_Name AS 'Province',
DM.Dist_Name AS 'District',
AD.ORG_PLC_Village AS 'Town',
GM.Description AS 'Sex',
AD.ORG_PLC_Village AS 'Residential_Address',
PD.Email AS 'Email ID',
PD.Prime_Phone AS 'Primary Phone',
PD.Mobile AS 'Mobile Number',
RD.Registration_Center AS 'Registration Center',
CONVERT(VARCHAR(10), RD.Registration_Date, 101) AS 'Date Of Registration',
CASE RD.Issued_ID_Card WHEN 1 THEN 'Issued' ELSE 'Pending' END AS 'ID Card Status',
CASE MSD.Marital_Status_ID WHEN 1 THEN 'Single'
WHEN 2 THEN 'Married'
WHEN 3 THEN 'Divorced'
WHEN 4 THEN 'Widower'
WHEN 5 THEN 'Others'
ELSE 'Not Available'
END AS 'Marital Status',
CASE DD.DISABILITY_ID WHEN 1 THEN 'Not-Disabled'
WHEN 2 THEN 'Illiterate(English)'
WHEN 3 THEN 'Visual Impairment'
WHEN 4 THEN 'Physical Impairment'
WHEN 5 THEN 'Mental Impairment'
WHEN 6 THEN 'Hearing Loss'
WHEN 7 THEN 'Others'
END AS 'Disability',
CASE CPD.VISA_STATUS WHEN 1 THEN 'Active'
WHEN 2 THEN 'Inactive'
END AS 'Visa Status',
CASE CPD.availability WHEN 1 THEN CPD.Issuing_Country ELSE 'Passport Not Available' END AS 'Passport Status'
FROM t_Cust_Details AS CD INNER JOIN
t_Cust_Registration_Details AS RD ON CD.CUST_ID = RD.CUST_ID INNER JOIN
t_Gender_Master AS GM ON CD.GENDER_CODE = GM.GENDER_CODE INNER JOIN
t_Cust_Phone_Details AS PD ON CD.CUST_ID = PD.CUST_ID INNER JOIN
t_Cust_Address_Details AS AD ON CD.CUST_ID = AD.CUST_ID INNER JOIN
t_District_Master AS DM ON AD.ORG_PLC_DIS_ID = DM.DIST_ID INNER JOIN
t_Province_Master AS PM ON DM.PRO_ID = PM.PRO_ID INNER JOIN
t_Division_Master AS DVM ON DVM.DIV_ID = PM.DIV_ID INNER JOIN
t_Cust_Marital_Status_Detail AS MSD ON MSD.Cust_ID=CD.CUST_ID INNER JOIN
t_Disability_Details AS DD ON DD.CUST_ID = CD.CUST_ID INNER JOIN
t_Cust_Passport_Details CPD ON CPD.CUST_ID = CD.CUST_ID
WHERE RD.Issued_ID_CARD IN (CASE @pIdCardStatus
WHEN 'ISSUED' THEN 1
WHEN 'PENDING' THEN 0
WHEN 'ALL' THEN RD.Issued_ID_CARD
END)
AND MSD.MARITAL_STATUS_ID IN (CASE @pMaritalStatus
WHEN 'Single' THEN 1
WHEN 'Married' THEN 2
WHEN 'Divorced' THEN 3
WHEN 'Widower' THEN 4
WHEN 'Others' THEN 5
ELSE 5
END)
AND DD.DISABILITY_ID IN (CASE @pDisabilityStatus
WHEN 'No' THEN 1
WHEN 'Illiterate(English)' THEN 2
WHEN 'Visual Impairment' THEN 3
WHEN 'Physical Impairment' THEN 4
WHEN 'Mental Impairment' THEN 5
WHEN 'Hearing Loss' THEN 6
WHEN 'Others' THEN 7
END)
AND CPD.VISA_STATUS IN (CASE @pVisaStatus
WHEN 'Active' THEN 1
WHEN 'Inactive' THEN 2
END)
AND GM.Description IN (@pGender)
AND PM.Pro_Name IN (@pProvince)
AND DM.Dist_Name IN (@pDistrict)
AND DVM.DIV_NAME IN (@pDivision)
November 18, 2010 at 5:54 am
the parameters are probably transfered as a csv string.
so '1,4,2,6'
x in ('1,4,2,6') where x is an integer will give an error as it tries to convert '1,4,2,6' to an integer.
You need to either convert the values to a collection (table) or use dynamic sql.
Just had a closer look and you will have something like
case 'ISSUED,PENDING' when ...
which will never match any of the values.
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 6:16 am
nigelrivett (11/18/2010)
the parameters are probably transfered as a csv string.so '1,4,2,6'
x in ('1,4,2,6') where x is an integer will give an error as it tries to convert '1,4,2,6' to an integer.
You need to either convert the values to a collection (table) or use dynamic sql.
Just had a closer look and you will have something like
case 'ISSUED,PENDING' when ...
which will never match any of the values.
I doubt this...as the rest of my parameters are working perfectly fine. They are also Multi Valued..but only three are causing me this problem...so I believe the problem is somewhe else..
November 18, 2010 at 6:33 am
November 19, 2010 at 3:35 am
Reo (11/18/2010)
Not of much help...I am using a direct query (not SP) so difficult to use in my condition..
Can someone suggest if I have a Case Statement in WHERE clause like this:
WHERE RD.Issued_ID_CARD IN (CASE @pIdCardStatus
WHEN 'ISSUED' THEN 1
WHEN 'PENDING' THEN 0
WHEN 'ALL' THEN RD.Issued_ID_CARD END)
How can I make use of the split Function..my paramater is multivalue
I appreciate the quick response from anybody.
November 19, 2010 at 5:11 am
after debugging it further, I found out that the eal query its building at run time for the problematic casestatement is as below:
WHERE (RD.Issued_ID_CARD IN (CASE N'ISSUED',N'PENDING',N'ALL'
WHEN 'ISSUED' THEN 1
WHEN 'PENDING' THEN 0
WHEN 'ALL' THEN RD.Issued_ID_CARD END))
Any Idea on how can we fix this...if somebody already crossed this road before..?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply