Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: Case Statement to Show Null, Not Null, Both Values

    I'm creating a report for use in an ERP system using Crystal Reports. The sort on the form requires a value to be sorted by all values that are Null, Not Null, or to display All values. I created a Case Statement that works for 1 and 0 values, but I don't know if it will work to return Null and Not Null values.

    The working code for 1 & 0's:

    --Create Variable
    @BOMComplete InfoBarType = NULL

    --Declare
    @t_BOMComplete SMALLINT

    --SET
    SET @t_BOMComplete = CASE WHEN @BOMComplete = 'Y'
    THEN 1
    ELSE 0
    END

    --WHERE
    (CASE WHEN @BOMComplete = 'B' THEN 1
    ELSE
    CASE WHEN item.Uf_BOMCompleteChkBx = @t_BOMComplete
    THEN 1
    ELSE 0
    END END) <> 0


    The values in the item.Uf_BOMCompleteChkBx columns are all 1 or 0. The 'Y' and 'B' values are the values passed from the form indicating Yes or Both.

    My question is, can I do the same with Dates sorting by viewing only values that are Null(Yes), Not Null(No), or Both.


    SET @t_ItemEnteredYNB =
    CASE WHEN @ItemEnteredYNB = 'Y'
    THEN --(RETURN ONLY NOT NULL VALUES)--
    ELSE --(RETURN NULL VALUES??)--
    END

    (CASE WHEN @ItemEnteredYNB = 'B'
    THEN --(RETURN ALL VALUES)--
    ELSE
    CASE WHEN item.Uf_ItemEntered = @t_ItemEnteredYNB
    THEN 1
    ELSE 0
    END END) <> 0

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    CASE WHEN @ItemEnteredYNB = 'Y' or isnull(@ItemEnteredYNB,'') != ''
    THEN --(RETURN ONLY NOT NULL VALUES)--
    ELSE --(RETURN NULL VALUES??)--
    END

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    IF @ItemEnteredYNB = 'Y'
      BEGIN
        SELECT *
        FROM   your_table
        WHERE  some_field IS NOT NULL
      END
    ELSE IF @ItemEnteredYNB = 'N'
      BEGIN
        SELECT *
        FROM   your_table
        WHERE  some_field IS NULL
      END
    ELSE
      BEGIN
        SELECT *
        FROM   your_table
      END
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2009
    Posts
    2
    So far this is what the Select portion of the SP looks like. It's not working in the form the way it is, but I think it has something to do with the Crystal Report. So does this look about right??


    SELECT
    Item,
    Uf_ItemEntered,
    description,
    Uf_ToolingJobChkBx,
    Uf_EngineeringDataEnd,
    Uf_PrintAssignment,
    Uf_PrintGeneratedChkBx,
    Uf_PrintGenerated,
    UF_PrintCustApproval,
    UF_PrintCustApprovalChkBx,
    UF_PrintSentToCust,
    UF_BOMCompleteChkBx,
    UF_ItemPpapChkBx,
    UF_ItemAttribEntered,
    product_code,
    p_m_t_code

    FROM dbo.item


    WHERE item between @StartingItem and @EndingItem
    AND ISNULL(Uf_ItemEntered,' ') between @StartingItemEntered and @EndingItemEntered
    AND ISNULL(Uf_EngineeringDataEnd, '') between @StartingEngineeringDataComp and @EndingEngineeringDataComp
    AND ISNULL(Uf_PrintGenerated, '') between @StartingPrintGenerated and @EndingPrintGenerated
    AND ISNULL(Uf_PrintSentToCust, '') between @StartingSentCustApproval and @EndingSentCustApproval
    AND ISNULL(Uf_PrintCustApproval, '') between @StartingPrintCustomerApproval and @EndingPrintCustomerApproval
    AND product_code between @StartingProductCode and @EndingProductCode
    AND p_m_t_code = @Source
    AND (CASE WHEN @PrintAssignment IS NULL THEN 1 ELSE CASE WHEN item.Uf_PrintAssignment = @PrintAssignment THEN 1 ELSE 0 END END) <>0

    --YES NO BOTH CHECKBOXES REQUIRE THIS ARGUEMENT
    AND (CASE WHEN @ToolingJob = 'B' THEN 1 ELSE CASE WHEN item.Uf_ToolingJobChkBx = @t_ToolingJobChkBx THEN 1 ELSE 0 END END) <> 0
    AND (CASE WHEN @PPAP = 'B' THEN 1 ELSE CASE WHEN item.Uf_ItemPpapChkBx = @t_ItemPpapChkBx THEN 1 ELSE 0 END END) <> 0
    AND (CASE WHEN @PrintGenerationReq = 'B' THEN 1 ELSE CASE WHEN item.Uf_PrintGeneratedChkBx = @t_PrintGeneratedChkBx THEN 1 ELSE 0 END END) <> 0
    AND (CASE WHEN @AttributesEntered = 'B' THEN 1 ELSE CASE WHEN item.Uf_ItemAttribEntered = @t_ItemAttribEntered THEN 1 ELSE 0 END END) <> 0
    AND (CASE WHEN @PrintCustomerApprovalReq = 'B' THEN 1 ELSE CASE WHEN item.UF_PrintCustApprovalChkBx = @t_PrintCustApprovalChkBx THEN 1 ELSE 0 END END) <> 0
    AND (CASE WHEN @BOMComplete = 'B' THEN 1 ELSE CASE WHEN item.Uf_BOMCompleteChkBx = @t_BOMComplete THEN 1 ELSE 0 END END) <> 0


    ----------------------------------------------
    IF @ItemEnteredYNB = 'Y'
    BEGIN
    SELECT Item,
    Uf_ItemEntered,
    description,
    Uf_ToolingJobChkBx,
    Uf_EngineeringDataEnd,
    Uf_PrintAssignment,
    Uf_PrintGeneratedChkBx,
    Uf_PrintGenerated,
    UF_PrintCustApproval,
    UF_PrintCustApprovalChkBx,
    UF_PrintSentToCust,
    UF_BOMCompleteChkBx,
    UF_ItemPpapChkBx,
    UF_ItemAttribEntered,
    product_code,
    p_m_t_code
    FROM item
    WHERE item.Uf_ItemEntered IS NOT NULL
    END
    ELSE IF @ItemEnteredYNB = 'N'
    BEGIN
    SELECT *
    FROM item
    WHERE item.Uf_ItemEntered IS NULL
    END
    ELSE
    BEGIN
    SELECT *
    FROM item
    END
    ----------------------------------------------

    /*IF @EngDataCompleteYNB = 'Y'
    BEGIN
    SELECT *
    FROM item
    WHERE item.Uf_EngineeringDataEnd IS NOT NULL
    END
    ELSE IF @EngDataCompleteYNB = 'N'
    BEGIN
    SELECT *
    FROM item
    WHERE item.Uf_EngineeringDataEnd IS NOT NULL
    END
    ELSE
    BEGIN
    SELECT *
    FROM item
    END*/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •