none
Incorrect syntax when attempting to use two fields in order to create a parameter in SSRS 2008 RRS feed

  • Question

  • I'm attempting to create a parameter for when jodrtg.fdescnum <> inmastx.fbin1. I want to be able to use this in a dropdown list selection for my report. They'll be one selection for the filtered criteria and another for all of the records to show.  I have tried a number of combinations but keep getting syntax errors near the <or =.I'd be appreciative if anyone can point me in the right direction. I've never tried using two fields to create a single parameter before.
    This what I've been attempting to get as my final result.

    WHERE jodrtg.fdescnum <> inmastx.fbin1 = @MoldPress


    Wednesday, October 24, 2018 1:52 PM

Answers

  • How does this work as far as being able to fill the parameter with all of my values instead of each one by one?

    Sorry I cant understand your scenario

    DO you mean like parameter with two modes as value? like Filtered and Unfiltered. When Filtered is selected you want above condition to be applied otherwise all rows?

    then what you need is to set Available values as Filtered, Unfiltered in parameter properties for your parameter

    Then in your SQL query use a WHERE condition like below

    WHERE jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'


    Yes, this is my question. When I tried what you suggested all I got what was filtered rows. This is the dataset that I am using.

    SELECT
    R,
    Stack,
        RTRIM(MoldNo) as MoldNo,
        Press,
        JobNo,
        Status,
        RelDate,
        StartByDate,
        Priority,
        RTRIM(WH) as WH,
        MoldLoc,
        foperno,
        flschedule,
        RTRIM(WC) as WC,
        fcomp_date,
        flastlab,
        RTRIM(MoldDesc) as MoldDesc,
        Cavitation,
        MoldDims,
        Type,
        fddDueDate,
        fDueDate,
        foperqty,
        fnqty_comp,
        fnqty_togo,
        fnqty_move
    FROM
    (
    SELECT
        jodbom.fbompart as MoldNo, 
        jodrtg.fdescnum AS Press,
        jomast.fjobno as JobNo,
          jomast.fstatus as Status,
        jomast.frel_dt as RelDate,
          jomast.ftduedate as StartByDate,
        jomast.fschdprior as Priority,
        inmastx.flocate1 AS WH,
        inmastx.fbin1 AS MoldLoc,
        jodrtg.foperno as foperno,
        jodrtg.flschedule as flschedule,
        jodrtg.fpro_id as WC,
        jodrtg.fcomp_date as fcomp_date,
        jomast.flastlab as flastlab ,
        inmastx.fdescript AS MoldDesc,
          inmastx.fcusrchr2 AS Cavitation,
        inmastx.fcusrchr3 AS MoldDims,
        jomast.ftype as Type,
        jomast.fddue_date as fddDueDate,
        sorels.fduedate as fDueDate,
        jodrtg.foperqty as foperqty,
        jodrtg.fnqty_comp as fnqty_comp ,
        jodrtg.fnqty_togo as fnqty_togo,
        jodrtg.fnqty_move as fnqty_move,
        ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,
        ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack
    
        FROM jodbom 
        INNER JOIN jomast ON jodbom.fjobno =  jomast.fjobno
        INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno 
        INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno
        LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease 
        
    
    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'
    ) Q
    
    WHERE  R = 1 

    it should be this in WHERE clause

    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and (jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David9501 Thursday, October 25, 2018 1:08 PM
    Thursday, October 25, 2018 7:45 AM

All replies

  • Tha's invalid SQL, you can always use only one compare Operator at a thime, but you can Combine them with bool logic

    WHERE
     jodrtg.fdescnum <> inmastx.fbin1
    AND inmastx.fbin1 = @MoldPress


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 24, 2018 1:58 PM
  • How does this work as far as being able to fill the parameter with all of my values instead of each one by one?
    Wednesday, October 24, 2018 4:05 PM
  • Your question is vague.  Others can help you only when you put down the requirement and the issue you face clearly. What is @MoldPress  parameter used for? Will it contain multiple values from drop down box the user select? If yes,  how ? comma separated, XML or something else?  do you want to compare it against the same column inmastx.fbin1 ?

    Wednesday, October 24, 2018 4:59 PM
  • How does this work as far as being able to fill the parameter with all of my values instead of each one by one?

    Sorry I cant understand your scenario

    DO you mean like parameter with two modes as value? like Filtered and Unfiltered. When Filtered is selected you want above condition to be applied otherwise all rows?

    then what you need is to set Available values as Filtered, Unfiltered in parameter properties for your parameter

    Then in your SQL query use a WHERE condition like below

    WHERE jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, October 24, 2018 5:22 PM
  • How does this work as far as being able to fill the parameter with all of my values instead of each one by one?

    Sorry I cant understand your scenario

    DO you mean like parameter with two modes as value? like Filtered and Unfiltered. When Filtered is selected you want above condition to be applied otherwise all rows?

    then what you need is to set Available values as Filtered, Unfiltered in parameter properties for your parameter

    Then in your SQL query use a WHERE condition like below

    WHERE jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'


    Yes, this is my question. When I tried what you suggested all I got what was filtered rows. This is the dataset that I am using.

    SELECT
    R,
    Stack,
        RTRIM(MoldNo) as MoldNo,
        Press,
        JobNo,
        Status,
        RelDate,
        StartByDate,
        Priority,
        RTRIM(WH) as WH,
        MoldLoc,
        foperno,
        flschedule,
        RTRIM(WC) as WC,
        fcomp_date,
        flastlab,
        RTRIM(MoldDesc) as MoldDesc,
        Cavitation,
        MoldDims,
        Type,
        fddDueDate,
        fDueDate,
        foperqty,
        fnqty_comp,
        fnqty_togo,
        fnqty_move
    FROM
    (
    SELECT
        jodbom.fbompart as MoldNo, 
        jodrtg.fdescnum AS Press,
        jomast.fjobno as JobNo,
          jomast.fstatus as Status,
        jomast.frel_dt as RelDate,
          jomast.ftduedate as StartByDate,
        jomast.fschdprior as Priority,
        inmastx.flocate1 AS WH,
        inmastx.fbin1 AS MoldLoc,
        jodrtg.foperno as foperno,
        jodrtg.flschedule as flschedule,
        jodrtg.fpro_id as WC,
        jodrtg.fcomp_date as fcomp_date,
        jomast.flastlab as flastlab ,
        inmastx.fdescript AS MoldDesc,
          inmastx.fcusrchr2 AS Cavitation,
        inmastx.fcusrchr3 AS MoldDims,
        jomast.ftype as Type,
        jomast.fddue_date as fddDueDate,
        sorels.fduedate as fDueDate,
        jodrtg.foperqty as foperqty,
        jodrtg.fnqty_comp as fnqty_comp ,
        jodrtg.fnqty_togo as fnqty_togo,
        jodrtg.fnqty_move as fnqty_move,
        ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,
        ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack
    
        FROM jodbom 
        INNER JOIN jomast ON jodbom.fjobno =  jomast.fjobno
        INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno 
        INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno
        LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease 
        
    
    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'
    ) Q
    
    WHERE  R = 1 

    Wednesday, October 24, 2018 7:09 PM
  • Hi David9501,

    According to your description, I think you might want to use cascading parameter. You could refer to Visakh16's suggestions. Below is my design:

    I create main dataset like below

    SELECT        Table_long.*
    FROM            Table_long
    WHERE name in (@a2)


    Then insert a Table in it

    Then create another dataset for parameter values(which similar to your above query, but I didn't pass hard code in it)

    SELECT        name
    FROM            (SELECT        name
                              FROM            Table_long
                              WHERE        (category IN (@a1))) AS t

    Then I set @a2 parameter like below(set both default value and available value). You could set @a1 and @a2 to "allow multiple parameter"

     Then you will get the result below(if you pass parameter in @a1, @a2 will fill parameter value automatically)

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 25, 2018 7:27 AM
    Moderator
  • How does this work as far as being able to fill the parameter with all of my values instead of each one by one?

    Sorry I cant understand your scenario

    DO you mean like parameter with two modes as value? like Filtered and Unfiltered. When Filtered is selected you want above condition to be applied otherwise all rows?

    then what you need is to set Available values as Filtered, Unfiltered in parameter properties for your parameter

    Then in your SQL query use a WHERE condition like below

    WHERE jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'


    Yes, this is my question. When I tried what you suggested all I got what was filtered rows. This is the dataset that I am using.

    SELECT
    R,
    Stack,
        RTRIM(MoldNo) as MoldNo,
        Press,
        JobNo,
        Status,
        RelDate,
        StartByDate,
        Priority,
        RTRIM(WH) as WH,
        MoldLoc,
        foperno,
        flschedule,
        RTRIM(WC) as WC,
        fcomp_date,
        flastlab,
        RTRIM(MoldDesc) as MoldDesc,
        Cavitation,
        MoldDims,
        Type,
        fddDueDate,
        fDueDate,
        foperqty,
        fnqty_comp,
        fnqty_togo,
        fnqty_move
    FROM
    (
    SELECT
        jodbom.fbompart as MoldNo, 
        jodrtg.fdescnum AS Press,
        jomast.fjobno as JobNo,
          jomast.fstatus as Status,
        jomast.frel_dt as RelDate,
          jomast.ftduedate as StartByDate,
        jomast.fschdprior as Priority,
        inmastx.flocate1 AS WH,
        inmastx.fbin1 AS MoldLoc,
        jodrtg.foperno as foperno,
        jodrtg.flschedule as flschedule,
        jodrtg.fpro_id as WC,
        jodrtg.fcomp_date as fcomp_date,
        jomast.flastlab as flastlab ,
        inmastx.fdescript AS MoldDesc,
          inmastx.fcusrchr2 AS Cavitation,
        inmastx.fcusrchr3 AS MoldDims,
        jomast.ftype as Type,
        jomast.fddue_date as fddDueDate,
        sorels.fduedate as fDueDate,
        jodrtg.foperqty as foperqty,
        jodrtg.fnqty_comp as fnqty_comp ,
        jodrtg.fnqty_togo as fnqty_togo,
        jodrtg.fnqty_move as fnqty_move,
        ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,
        ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack
    
        FROM jodbom 
        INNER JOIN jomast ON jodbom.fjobno =  jomast.fjobno
        INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno 
        INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno
        LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease 
        
    
    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'
    ) Q
    
    WHERE  R = 1 

    it should be this in WHERE clause

    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and (jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David9501 Thursday, October 25, 2018 1:08 PM
    Thursday, October 25, 2018 7:45 AM
  • How does this work as far as being able to fill the parameter with all of my values instead of each one by one?

    Sorry I cant understand your scenario

    DO you mean like parameter with two modes as value? like Filtered and Unfiltered. When Filtered is selected you want above condition to be applied otherwise all rows?

    then what you need is to set Available values as Filtered, Unfiltered in parameter properties for your parameter

    Then in your SQL query use a WHERE condition like below

    WHERE jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'


    Yes, this is my question. When I tried what you suggested all I got what was filtered rows. This is the dataset that I am using.

    SELECT
    R,
    Stack,
        RTRIM(MoldNo) as MoldNo,
        Press,
        JobNo,
        Status,
        RelDate,
        StartByDate,
        Priority,
        RTRIM(WH) as WH,
        MoldLoc,
        foperno,
        flschedule,
        RTRIM(WC) as WC,
        fcomp_date,
        flastlab,
        RTRIM(MoldDesc) as MoldDesc,
        Cavitation,
        MoldDims,
        Type,
        fddDueDate,
        fDueDate,
        foperqty,
        fnqty_comp,
        fnqty_togo,
        fnqty_move
    FROM
    (
    SELECT
        jodbom.fbompart as MoldNo, 
        jodrtg.fdescnum AS Press,
        jomast.fjobno as JobNo,
          jomast.fstatus as Status,
        jomast.frel_dt as RelDate,
          jomast.ftduedate as StartByDate,
        jomast.fschdprior as Priority,
        inmastx.flocate1 AS WH,
        inmastx.fbin1 AS MoldLoc,
        jodrtg.foperno as foperno,
        jodrtg.flschedule as flschedule,
        jodrtg.fpro_id as WC,
        jodrtg.fcomp_date as fcomp_date,
        jomast.flastlab as flastlab ,
        inmastx.fdescript AS MoldDesc,
          inmastx.fcusrchr2 AS Cavitation,
        inmastx.fcusrchr3 AS MoldDims,
        jomast.ftype as Type,
        jomast.fddue_date as fddDueDate,
        sorels.fduedate as fDueDate,
        jodrtg.foperqty as foperqty,
        jodrtg.fnqty_comp as fnqty_comp ,
        jodrtg.fnqty_togo as fnqty_togo,
        jodrtg.fnqty_move as fnqty_move,
        ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,
        ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack
    
        FROM jodbom 
        INNER JOIN jomast ON jodbom.fjobno =  jomast.fjobno
        INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno 
        INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno
        LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease 
        
    
    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered'
    ) Q
    
    WHERE  R = 1 

    it should be this in WHERE clause

    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 
    and (jodrtg.fdescnum <> inmastx.fbin1 
    OR @ParameterName = 'Unfiltered')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh

    This worked perfect now, thank you for the help.
    Thursday, October 25, 2018 1:08 PM