question

MohamedMoulana-5140 avatar image
0 Votes"
MohamedMoulana-5140 asked MelissaMa-msft commented

Query producing wrong result

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](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 NOT NULL, [RSTDate] [datetime] NOT 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, Second TABLE structure: [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] NOT 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, 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 ?

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Your code is unreadable. Use the code sample button to format the T-SQL.

0 Votes 0 ·

Hi @MohamedMoulana-5140

Could you please validate all the answers so far and provide any update?

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MohamedMoulana-5140

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.


1.png (12.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.