Helllo,

I have following query:

IF (@OrgIDs) not in (@Platce)



SELECT TOP 100
    PRI.sym AS [Category]
    ,CR.ref_num as Incident
    ,ORG.org_name as Organizace
    , CR.persid AS ViolationIncidetID
,SRVCAT.SrvCatDesc



,ORGCAT.OrgCatID

    FROM call_req AS [CR] with (nolock)
    LEFT JOIN dbo.pri AS [PRI] ON CR.priority=PRI.enum
    LEFT JOIN dbo.prob_ctg AS CAT ON CR.category = CAT.persid
left outer join srv_desc as SRV on CAT.service_type=SRV.persid    
    LEFT JOIN dbo.ca_contact AS CT ON CR.customer = CT.contact_uuid
    LEFT JOIN dbo.ca_organization AS ORG ON CT.organization_uuid = ORG.organization_uuid
    INNER JOIN data_ware.dbo.OrganizationCategory_v4 AS ORGCAT
    ON CT.organization_uuid = ORGCAT.OrgID and '2014' = cast (ORGCAT.OrgCatID /10000 as int)
    INNER JOIN data_ware.dbo.ServiceCategory_v4 AS SRVCAT
    ON SRV.id = SRVCAT.ServiceID and  '2014' = cast (SRVCAT.SrvCatID /10000 as int)
    left join data_ware.dbo.MUST_OrganizationServiceLink_v2 as TEST on SRVCAT.SrvCatID=TEST.IDsvc

    WHERE  


({fn ifnull(CR.z_close_date,CR.close_date)}>=dbo.AC_timestamp_from_datetime(DATEADD(d, DATEDIFF(d, 0, @Datum_od), 0)) AND {fn ifnull(CR.z_close_date,CR.close_date)}<=dbo.AC_timestamp_from_datetime(DATEADD(d, DATEDIFF(d, 0,@Datum_do)+1, 0)))


AND SRVCAT.SrvCatID IN (@ServiceCategoryIDs)
    AND ORGCAT.OrgCatID IN (@OrgIDs)


and CR.[type] = 'I'
   
else


SELECT TOP 100
    PRI.sym AS [Category]
,CR.ref_num as Incident
    ,ORG.org_name as Organizace
    , CR.persid AS ViolationIncidetID
  ,SRVCAT.SrvCatDesc
,ORGCAT.OrgCatID
,SRVCAT.SrvCatID

    FROM call_req AS [CR] with (nolock)
    LEFT JOIN dbo.pri AS [PRI] ON CR.priority=PRI.enum
    LEFT JOIN dbo.prob_ctg AS CAT ON CR.category = CAT.persid
left outer join srv_desc as SRV on CAT.service_type=SRV.persid    
    LEFT JOIN dbo.ca_contact AS CT ON CR.customer = CT.contact_uuid
    LEFT JOIN dbo.ca_organization AS ORG ON CT.organization_uuid = ORG.organization_uuid
    INNER JOIN data_ware.dbo.OrganizationCategory_v4 AS ORGCAT
    ON CT.organization_uuid = ORGCAT.OrgID and '2014' = cast (ORGCAT.OrgCatID /10000 as int)
    INNER JOIN data_ware.dbo.ServiceCategory_v4 AS SRVCAT
    ON SRV.id = SRVCAT.ServiceID and  '2014' = cast (SRVCAT.SrvCatID /10000 as int)
    left join  data_ware.dbo.MUST_OrganizationServiceLink_v2 as TEST on SRVCAT.SrvCatID=TEST.IDsvc

    WHERE  

({fn ifnull(CR.z_close_date,CR.close_date)}>=dbo.AC_timestamp_from_datetime(DATEADD(d, DATEDIFF(d, 0, @Datum_od), 0)) AND {fn ifnull(CR.z_close_date,CR.close_date)}<=dbo.AC_timestamp_from_datetime(DATEADD(d, DATEDIFF(d, 0,@Datum_do)+1, 0)))


and CR.[type] = 'I'


AND SRVCAT.SrvCatID IN (@ServiceCategoryIDs)

AND (ORGCAT.OrgCatID IN (@OrgIDs) or (/*(@OrgIDs) in (@Platce) and*/ TEST.IDPlatce in (@Platce) and (ORGCAT.OrgCatID not in (select T2.IDorg from data_ware.dbo.MUST_OrganizationServiceLink_v2 as T2 where T2.IDsvc in (@ServiceCategoryIDs)))))

@OrgIDs, @ServiceCategoryIDs, @Platce are multivalue parameter based on queries. @ServiceCategoryIDs is based on @OrgIDS and @Platce is based on both.

When I run report with only one value in @OrgIDS and multivalues in other parameters then reports run correctly. When I have more then one value in @OrgIDS parameter then report doesn't run and I get following error message

An expression of non-boolean type specified in a context where a condition is expected, near ','. Incorrect syntax near the keyword 'else'.

Don't you know what to do? I am looking forward for your ideas.

Thanks

Lukas

what is the datatype of this parameter.. @OrgIDs?? and what is the datatype of the other parameters?

Try simplifying your query to see which part is breaking, I'm not even sure which part you are asking about.

Is it the bit you have commented out?  (/*(@OrgIDs) in (@Platce) and*/

I cant even see that working,

Say @OrgIDs you have selected 1,2,3,4,5

@Platce you have selected 1,2,3

it would look like, select * from table where 1,2,3,4,5 in 1,2,3, that's not even valid sql

@OrgIDs is int

@ServiceCategoryIDs is int

@Platce is int

@Datum_od is datetime

@Datum_do is datetime

This part of script (/*(@OrgIDs) in (@Platce) and*/) shouldn't be there.

I think that problem is at begining: IF (@OrgIDs) not in (@Platce)

But I don't know how to work around it.

What is the purpose of the If statement?  Does the query work if you remove it?

Again I don't think that would be valid TSQL and  it would fail because your if statement would be doing this:

if 1,2,3,4,5 not in 1,2,3,

The multi valued parameter filter should be implemented as below

....

AND  ',' + @ServiceCategoryIDs + ',' LIKE '%,' + CAST(SRVCAT.SrvCatID AS varchar(20)) + ',%'

AND ',' + @OrgIDs + ',' LIKE '%,' + CAST(ORGCAT.OrgCatID AS varchar(20)) + ',%'

 or (',' + @Platce + ',' like '%,' + cast(TEST.IDPlatce as VARCHAR(20)) + ',%'
and (ORGCAT.OrgCatID not in (select T2.IDorg from data_ware.dbo.MUST_OrganizationServiceLink_v2 as T2 where  
',' + @ServiceCategoryIDs + ',' LIKE '%,' + CAST(T2.IDsvc as varchar(20)) + ',%'))))

As I wrote above: When I run report with only one value in @OrgIDS and multivalues in other parameters (@ServiceCategoryIDs, @Platce)   then reports run correctly. When I have more then one value in @OrgIDS parameter then report doesn't run and I get error message

As I wrote above: When I run report with only one value in @OrgIDS and multivalues in other parameters (@ServiceCategoryIDs, @Platce)   then reports run correctly. When I have more then one value in @OrgIDS parameter then report doesn't run and I get error message

Did you try the way i suggested?

then it would work fine

....

AND  ',' + @ServiceCategoryIDs + ',' LIKE '%,' + CAST(SRVCAT.SrvCatID AS varchar(20)) + ',%'

AND ',' + @OrgIDs + ',' LIKE '%,' + CAST(ORGCAT.OrgCatID AS varchar(20)) + ',%'

 or (',' + @Platce + ',' like '%,' + cast(TEST.IDPlatce as VARCHAR(20)) + ',%'
and (ORGCAT.OrgCatID not in (select T2.IDorg from data_ware.dbo.MUST_OrganizationServiceLink_v2 as T2 where  
',' + @ServiceCategoryIDs + ',' LIKE '%,' + CAST(T2.IDsvc as varchar(20)) + ',%'))))

Hi, I just tried it, but it is almost the same. Report runs correctly with only one value in @OrgIDs and only one vylue in @ServiceCategoryIDs.

Previously it runs also with more than one value in @ServiceCategyIDs.

if statement 'if 1 not in (1,2,3,4,5)' and 'if 1 not in ("1,2,3,4,5")' is not equal.

First if statement correct result bring to you, but second statement is not.

I suggest... 

    AND ORGCAT.OrgCatID IN (@OrgIDs)

     AND ( CASE  WHEN  @OrgIDs NOT IN @Platce  

            THEN

               (ORGCAT.OrgCatID IN (@OrgIDs) ELSE TEST.IDPlatce in (@Platce) and (ORGCAT.OrgCatID not in (select T2.IDorg from data_ware.dbo.MUST_OrganizationServiceLink_v2 as T2 where T2.IDsvc in (@ServiceCategoryIDs))

           ELSE 1:1

         END ) ...

• Edited by daeyoung.kim 2 hours 35 minutes ago

Hi, I just tried it, but it is almost the same. Report runs correctly with only one value in @OrgIDs and only one vylue in @ServiceCategoryIDs.

Previously it runs also with more than one value in @ServiceCategyIDs.

Nope it will work fine so far as @ServiceCategyIDs etc are multi valued parameters with values passed as <value1>,<value2>,.. format and SrvCatID field has the individual values stored in