Post reply

Help with Boolean check box combinations

  • June 17, 2021 at 3:20 am

    #3897265

    Hi SQL Experts,

    I have a Store Procedure with snippets of code listed below. with the exception of the Id all other parameters represent a check box, when I select individual check box I get the correct results, but when I select multiple check boxes I get no result.

    example:  If I check the first check box (@Incomplete_Requisition = 1 AND Pom.Status = 0) I get 5 records where all Pom.Status = 0

    If I check the second box (@Submitted_Requisition = 1 AND Pom.Status = 2) I get 10 records where all Pom.Status = 2

    When I check both check boxes I get no results when I should be getting 15 records, can some one please show me how to code the where statement to get the correct results..

    Thanks you.

    @userid AS int,

    @Incomplete_Requisition AS Bit = 0,

    @Submitted_Requisition AS Bit = 0,

    @Fully_Approved_Requisition AS Bit = 0,

    @Future_Fully_Approve_Requisition AS Bit = 0,

    @Current_Cycle_PO AS Bit = 0,

    @Future_Cycle_PO AS Bit = 0,

    @Cancelled_Closed_PO AS Bit = 0

    Select * From Master as pom

    WHERE

    Pom.UserID = @ID

    AND (@Incomplete_Requisition = 0 OR (@Incomplete_Requisition = 1 AND Pom.Status = 0))

    AND (@Submitted_Requisition = 0 OR (@Submitted_Requisition = 1 AND Pom.Status = 2))

    AND (@Fully_Approved_Requisition = 0 OR (@Fully_Approved_Requisition = 1 AND Pom.Status = 4))

    AND (@Future_Fully_Approve_Requisition = 0 OR (@Future_Fully_Approve_Requisition = 1 AND Pom.Status = 94))

    AND (@Future_Cycle_PO = 0 OR (@Future_Cycle_PO = 1 AND Pom.Status IN (95,98)))

    AND (@Current_Cycle_PO = 0 OR (@Current_Cycle_PO = 1 AND Pom.Status IN (5,6,7,8)))

    AND (@Cancelled_Closed_PO = 0 OR (@Cancelled_Closed_PO = 1 AND pom.Status IN (100,110)))

  • June 17, 2021 at 3:26 am

    #3897266

    Aren't checkbox values either 1 0r 0?

  • June 17, 2021 at 11:37 am

    #3897417

    If I'm understanding this could this be done in the WHERE clause with a bunch of OR conditions.

    select * 
    From Master as pom
    WHERE Pom.UserID = @userid
          AND ((@Incomplete_Requisition = 1 AND Pom.Status = 0)
            OR (@Submitted_Requisition = 1 AND Pom.Status = 2)
            OR (@Fully_Approved_Requisition = 1 AND Pom.Status = 4)
            OR (@Future_Fully_Approve_Requisition = 1 AND Pom.Status = 94)
            OR (@Future_Cycle_PO = 1 AND Pom.Status IN (95,98))
            OR (@Current_Cycle_PO = 1 AND Pom.Status IN (5,6,7,8))
            OR (@Cancelled_Closed_PO = 1 AND pom.Status IN (100,110)));

    Alternately, you could declare a temporary table, insert statuses based on checkbox values, and then join to the physical table.

    declare @pom        table(status_val        int unique not null);

    if @Incomplete_Requisition = 1 
        insert @pom(status_val) values (0);
    if @Submitted_Requisition = 1 
        insert @pom(status_val) values (2);
    if @Fully_Approved_Requisition = 1 
        insert @pom(status_val) values (4);
    if @Future_Fully_Approve_Requisition = 1 
        insert @pom(status_val) values (94);
    if @Current_Cycle_PO = 1 
        insert @pom(status_val) values (95),(98);
    if @Future_Cycle_PO = 1 
        insert @pom(status_val) values (5),(6),(7),(8);
    if @Cancelled_Closed_PO = 1 
        insert @pom(status_val) values (100),(110);

    Select * 
    From Master pom
         join @pom p on pom.Status=p.status_val
    WHERE Pom.UserID = @userid;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • June 17, 2021 at 2:27 pm

    #3897479

    Thank you really appreciate the response. it work great, and I see where I was over thinking the WHERE Clause..

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply