Results 1 to 10 of 14

Thread: Odd SQL behavior

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #6
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,820

    Default Re: Odd SQL behavior

    Hi Samuel

    I actually overwrote the SP with the modified work around.

    I noticed the blank too, and I one point I had made sure that the WHERE section excluded anything that was blank.

    Anyway, really stumped as to why when I captured the actual call from DF and pasted in SSMS the result set was correct, but when called and returned to DF it was wrong.

    For what it is worth, here is the corrected SP that does work:

    (the green section is the replacement section that now works

    USE [WebWISH]
    GO


    /****** Object: StoredProcedure [dbo].[PR_TotalQtyCount] Script Date: 7/14/22 7:26:50 AM ******/
    SET ANSI_NULLS ON
    GO


    SET QUOTED_IDENTIFIER ON
    GO

    CREATE OR ALTER PROCEDURE [dbo].[PR_TotalQtyCount]
    (
    @yrbegin as DATE,
    @yrend AS DATE,
    @periodbegin AS DATE,
    @periodend AS DATE,
    @source AS INT,
    @orguuid AS VARCHAR(36),
    @counter AS VARCHAR(100),
    @type AS VARCHAR(100),
    @field AS VARCHAR(100)
    )
    AS
    BEGIN
    SET NOCOUNT ON


    DROP TABLE IF EXISTS #progstats


    SELECT PROG as Code, (COUNT(PROG)) as counted
    INTO #progstats
    FROM CLIPROGS
    WHERE [DATE] BETWEEN @periodbegin AND @periodend
    AND ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @source BETWEEN 1 AND 5 THEN
    CASE WHEN SOURCE =@source THEN 1
    ELSE 0
    END
    ELSE
    1
    END
    AND
    1 =
    CASE WHEN @field = '*all' THEN 1
    ELSE
    CASE WHEN @field = PROG THEN 1
    ELSE 0
    END
    END


    GROUP BY PROG


    UNION ALL


    SELECT PROG as Code, (COUNT(PROG)) as counted
    FROM ORPROGS
    WHERE [DATE] BETWEEN @periodbegin AND @periodend
    AND
    1 =
    CASE WHEN @field = '*all' THEN 1
    ELSE
    CASE WHEN @field = PROG THEN 1
    ELSE 0
    END
    END


    AND ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @source BETWEEN 1 AND 5 THEN
    CASE WHEN SOURCE =@source THEN 1
    ELSE 0
    END
    ELSE
    1
    END
    GROUP BY PROG






    UNION ALL


    SELECT A.[CODE] as Code,
    (SELECT COUNT([TYPE]) FROM FA_Lns
    WHERE [TYPE] = 'S'
    AND [TEXT] LIKE CONCAT('%;',TRIM(A.[CODE]),'^%')
    AND ORGUUID = @orguuid
    AND NUMBER IN
    (SELECT [NUMBER] FROM FA_Hdr
    WHERE [DATE] BETWEEN @periodbegin AND @periodend
    AND ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @source BETWEEN 1 AND 5 THEN
    CASE WHEN SOURCE =@source THEN 1
    ELSE 0
    END
    ELSE
    1
    END
    AND TYPE ='C') --'E' FOR CHILDREN
    as counted
    FROM PROGS AS A
    WHERE ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @field = '*all' THEN 1
    ELSE
    CASE WHEN @field = A.CODE THEN 1
    ELSE 0
    END
    END


    AND (SELECT COUNT([TYPE]) FROM FA_Lns
    WHERE [TYPE] = 'S'
    AND [TEXT] LIKE CONCAT('%;',TRIM(A.[CODE]),'^%')
    AND ORGUUID = @orguuid
    AND NUMBER IN
    (SELECT [NUMBER] FROM FA_Hdr
    WHERE [DATE] BETWEEN @periodbegin AND @periodend
    AND ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @source BETWEEN 1 AND 5 THEN
    CASE WHEN SOURCE =@source THEN 1
    ELSE 0
    END
    ELSE
    1
    END
    AND TYPE ='C' ) --'E' FOR CHILDREN
    >0


    GROUP BY A.[CODE]






    UNION ALL




    SELECT A.[CODE] as Code,
    (SELECT COUNT([TYPE]) FROM FA_Lns
    WHERE [TYPE] = 'M'
    --AND [TEXT] LIKE CONCAT(A.[CODE],' - %')
    AND LEFT([TEXT],6) = A.[CODE]
    AND ORGUUID = @orguuid
    AND NUMBER IN
    (SELECT [NUMBER] FROM FA_Hdr
    WHERE [DATE] BETWEEN @periodbegin AND @periodend
    AND ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @source BETWEEN 1 AND 5 THEN
    CASE WHEN SOURCE =@source THEN 1
    ELSE 0
    END
    ELSE
    1
    END
    AND TYPE ='C') --'E' FOR CHILDREN
    as counted
    FROM PROGS AS A
    WHERE ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @field = '*all' THEN 1
    ELSE
    CASE WHEN @field = A.CODE THEN 1
    ELSE 0
    END
    END


    AND (SELECT COUNT([TYPE]) FROM FA_Lns
    WHERE [TYPE] = 'M'
    --AND [TEXT] LIKE CONCAT(A.[CODE],' - %')
    AND LEFT([TEXT],6) = A.[CODE]
    AND ORGUUID = @orguuid
    AND NUMBER IN
    (SELECT [NUMBER] FROM FA_Hdr
    WHERE [DATE] BETWEEN @periodbegin AND @periodend
    AND ORGUUID = @orguuid
    AND
    1 =
    CASE WHEN @source BETWEEN 1 AND 5 THEN
    CASE WHEN SOURCE =@source THEN 1
    ELSE 0
    END
    ELSE
    1
    END
    AND TYPE ='C' ) --'E' FOR CHILDREN
    >0


    GROUP BY A.[CODE]








    ;


    SELECT Code, SUM(counted) FROM #progstats
    WHERE
    1 =
    CASE WHEN @field = '*all' THEN 1
    ELSE
    CASE WHEN @field = [Code] THEN 1
    ELSE 0
    END
    END
    GROUP BY Code
    ORDER BY 1
    ;
    END
    GO



    Before that section looked similar to this:
    Code:
    SELECT LEFT([TEXT],6), COUNT(LEFT([TEXT],6)) as Code FROM FA_Lns 
        WHERE [TYPE] = 'M'
        AND TRIM[TEXT] <> ''
        AND ORGUUID = '0021f322-7396-4926-8453-105730c067b5'
    	AND NUMBER IN 
    	    (SELECT [NUMBER] FROM FA_Hdr 
    		    WHERE [DATE] BETWEEN '2021-04-01' AND '2022-03-31'
    			AND ORGUUID = '0021f322-7396-4926-8453-105730c067b5'
    			AND TYPE ='C' )  --'E' FOR CHILDREN
    GROUP BY LEFT([TEXT],6)
    Last edited by Mike Cooper; 14-Jul-2022 at 06:35 AM. Reason: Had to put Code in Quote to allow formatting

Posting Permissions

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