Home Query with subquery returns null
Reply: 0

Query with subquery returns null

user1488
1#
user1488 Published in June 23, 2018, 9:17 pm

I Have a query with returns no results despite there are still plenty of rows to process.

SELECT O.[OrderLineId]
, O.[Article]
, O.[ArticleDescription]
, O.[QtyOrdered]
, O.[QtyDelivered]
, O.[BoxId]
, O.[Status]
, O.[Picker]
, O.[PickLocation]
, O.[Sorting]
, O.[DateCreated]
, O.[DateModified]
FROM [dbo].[PickBatch] P 
INNER JOIN [dbo].[Boxes] B ON P.PickBatchId = B.PickBatchId
INNER JOIN [dbo].[OrderLines] O ON B.BoxId = O.BoxId
WHERE P.PickBatchId = @PickBatchId 
AND (O.[Status] = @Status)
AND (O.[PickLocation] = (SELECT TOP(1) O.[PickLocation] 
                        FROM [dbo].[PickBatch] P 
                      INNER JOIN [dbo].[Boxes] B ON P.PickBatchId = B.PickBatchId
                      INNER JOIN [dbo].[OrderLines] O ON B.BoxId = O.BoxId
                      WHERE P.PickBatchId = @PickBatchId 
                      AND O.[Status] = @Status      
                      ORDER BY O.Sorting))

It looks like the subquery returns a result and meanwhile the status is changed so that the main query returns no results. Is this possible? Or do I make another mistake?

When the query returns no results the users is prompted that everything is picked and he can start a new batch. When he want's to start a new batch there a check if he hasn't an open batch. This fires few queries and this query. Then the query returns a result. So basically query is fired no results, sec. later same query returns results.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307892 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO