Query producing wrong result

Mohamed Moulana 1 Reputation point
2021-03-19T12:12:17.79+00:00

This is my first time posting an issue. I have provided the structures of the 2 tables being used and some sample data. I have leftout some columns for clarity. Seems to me like an interesting problem. Somebody please enlighten me before I loose faith in the SQL result. #Structure and sample Data: First TABLE structure: [tblRST] [Loc] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, [Fy] [int] NOT NULL, [Seq] [int] NOT NULL, [RSTNo] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, [RSTDate] [datetime] NOT NULL, [RSTTypeId] [int] NULL, [RSTType] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, [Confirmed] [bit] NULL, [ConfirmedUser] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, [ConfirmedDate] [datetime] NULL, [LastChanged] [timestamp] NULL, [CreatedOn] [datetime] NULL, [CreatedBy] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, [ModifiedOn] [datetime] NULL, [ModifiedBy] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, Second TABLE structure: [tblRSTDetsItem] [Loc] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, [RSTFy] [int] NOT NULL, [RSTSeq] [int] NOT NULL, [RSTDetsSeq] [int] NOT NULL, [Seq] [int] NOT NULL, [Barcode] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, [Qty] decimal NULL, [LastChanged] [timestamp] NOT NULL, [CreatedOn] [datetime] NULL, [CreatedBy] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, [ModifiedOn] [datetime] NULL, [ModifiedBy] nvarchar COLLATE SQL_Latin1_General_CP1_CS_AS NULL, Since I was unable to format like a grid, I have put the column heading and sample data within []. First Table [tblRST] sample contents: [Loc] [Fy] [Seq] [RSTTypeId] [RSTType] [Confirmed] [ConfirmedUser] [ConfirmedDate] [STD] [21] [1] [1] [21/97] [1] [a] [2021-03-15 18:22:53.303] [STD] [21] [2] [1] [21/90] [1] [a][2021-03-16 15:41:41.920] [STD] [21] [3] [1] [21/96] [0][NULL] [NULL] Second Table [tblRSTDetsItem] sample contents: [Loc] [RSTFy] [RSTSeq] [RSTDetsSeq] [Seq] [Barcode] [Qty] [STD] [21] [1] [1] [1] [ AMBOOO] [2.000] [STD] [21] [1] [2] [1] [EAAOOO] [4.000] [STD] [21] [2] [1] [1] [EAAOOO] [1.000] [STD] [21] [2] [2] [1] [AMLOOO] [1.000] [STD] [21] [3] [1] [1] [EAAOOO] [6.000] #The Problem Query: SELECT @ReturnableQty2 = Coalesce(Sum([Qty]), 0) FROM [tblRSTDetsItem] WHERE [Loc] = 'STD' AND [Barcode] = 'EAAOOO' AND Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[RSTFy])+'/'+Convert(nvarchar,[RSTSeq]) IN (SELECT Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[Fy])+'/'+Convert(nvarchar,[Seq]) FROM [tblRST] WHERE [RSTTypeId] = 1 AND [RSTType] = '21/96' AND [Confirmed] = 1); When I put this query inside a procedure and execute the procedure, the query returns 6. When I run it the SQL Management Studio in a new query window, the query returns 0. How is it possible ?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Phillips 17,721 Reputation points
    2021-03-19T13:06:10.323+00:00

    I cannot read you code as posted.

    However, what you describe is always related to the data types of parameters you put in the stored proc. You must always put in a size for "nvarchar". I see you did not on your converts. This can cause inconsistent results.

    0 comments No comments

  2. Erland Sommarskog 106.5K Reputation points
    2021-03-19T23:13:12.023+00:00

    As other have said, you need to clean up your post.

    This part:

    Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[Fy])+'/'+Convert(nvarchar,[Seq])
    

    can be written as (in SQL 2012 and later):

    concat(Loc, '/', Fy, '/', Seq)
    

    and in SQL 2017 or later, even as:

    concat_ws'/', (Loc,   Fy,  Seq)
    

    As for formatting a table, this is not a WYSIGWIG editor, but it understands Markdown, so if you know Markdown, you can produce a table that way. Else it may be better to do it Excel and include a screen-clip.

    0 comments No comments

  3. MelissaMa-MSFT 24,186 Reputation points
    2021-03-22T02:47:48.287+00:00

    Hi @Mohamed Moulana

    Welcome to Microsoft Q&A!

    Please refer below to provide the DDL and sample data so that all of us could understand them better.

    create table [tblRST](  
    [Loc] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,  
    [Fy] [int] NOT NULL,   
    [Seq] [int] NOT NULL,   
    [RSTNo] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CS_AS  NULL,  
    [RSTDate] [datetime]  NULL,   
    [RSTTypeId] [int] NULL,   
    [RSTType] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,   
    [Confirmed] [bit] NULL,   
    [ConfirmedUser] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,  
    [ConfirmedDate] [datetime] NULL,  
    [LastChanged] [timestamp] NULL,  
    [CreatedOn] [datetime] NULL,  
    [CreatedBy] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,  
    [ModifiedOn] [datetime] NULL,  
    [ModifiedBy] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL)  
      
    insert into [tblRST] ([Loc], [Fy] ,[Seq], [RSTTypeId] ,[RSTType], [Confirmed] ,[ConfirmedUser] ,[ConfirmedDate]) values  
    ('STD', 21, 1,1, '21/97', 1, 'a', '2021-03-15 18:22:53.303'),  
     ('STD', 21, 2 ,1, '21/90', 1, 'a','2021-03-16 15:41:41.920') ,  
    ('STD', 21, 3, 1, '21/96' ,0,NULL, NULL)  
      
    create table [tblRSTDetsItem]  
    ( [Loc] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,   
    [RSTFy] [int] NOT NULL,  
    [RSTSeq] [int] NOT NULL,   
    [RSTDetsSeq] [int] NOT NULL,   
    [Seq] [int] NOT NULL,   
    [Barcode] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,  
    [Qty] [decimal](10, 3) NULL,   
    [LastChanged] [timestamp]  NULL,   
    [CreatedOn] [datetime] NULL,  
    [CreatedBy] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,  
    [ModifiedOn] [datetime] NULL,   
    [ModifiedBy] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL)  
      
    insert into [tblRSTDetsItem] ([Loc] ,[RSTFy], [RSTSeq], [RSTDetsSeq], [Seq], [Barcode], [Qty]) values  
    ('STD', 21,  1,1,1,  'AMBOOO' ,2.000),  
    ('STD', 21,  2 ,2,1, 'EAAOOO', 4.000 ),  
    ('STD', 21, 2,1,1,   'EAAOOO' ,1.000),  
    ('STD', 21, 2,2,1,  'AMLOOO', 1.000),   
    ('STD', 21, 3,1,1,   'EAAOOO', 6.000)  
    

    After checking your query, I found that there could be no data reported after executing below query:

    SELECT Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[Fy])+'/'+Convert(nvarchar,[Seq]) FROM [tblRST]   
    WHERE [RSTTypeId] = 1 AND [RSTType] = '21/96' AND [Confirmed] = 1  
    

    Actually there was no data meeting these conditions since the Confirmed is 0 when the [RSTType] = '21/96' as below.
    79919-1.png

    I executed below two queries with and without the condition '[Confirmed]=1'.

    SELECT  Coalesce(Sum([Qty]), 0)   
    FROM [tblRSTDetsItem]   
    WHERE [Loc] = 'STD' AND [Barcode] = 'EAAOOO'   
    AND Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[RSTFy])+'/'+Convert(nvarchar,[RSTSeq])   
    IN (SELECT Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[Fy])+'/'+Convert(nvarchar,[Seq]) FROM [tblRST]   
    WHERE [RSTTypeId] = 1 AND [RSTType] = '21/96' --AND [Confirmed] = 1  
    );  
    

    Output:
    6.000

    SELECT  Coalesce(Sum([Qty]), 0)   
    FROM [tblRSTDetsItem]   
    WHERE [Loc] = 'STD' AND [Barcode] = 'EAAOOO'   
    AND Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[RSTFy])+'/'+Convert(nvarchar,[RSTSeq])   
    IN (SELECT Convert(nvarchar,[Loc])+'/'+Convert(nvarchar,[Fy])+'/'+Convert(nvarchar,[Seq]) FROM [tblRST]   
    WHERE [RSTTypeId] = 1 AND [RSTType] = '21/96' AND [Confirmed] = 1  
    );  
    

    Output:
    0.000

    So you could check whether there is any data change while comparing or consider to change the conditions accordingly.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments