Query producing wrong result

Question

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 ?

Answer 1

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.

Answer 2

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.

Answer 3

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.

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.