Home Query with subquery returns null
Reply: 3

Query with subquery returns null

swd Published in 2017-11-14 09:31:14Z

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.

Leran2002 Reply to 2017-11-14 10:24:56Z

As variant try to use ISNULL. Maybe, PickLocation IS NULL

AND (ISNULL(O.[PickLocation],'') = ISNULL((SELECT TOP(1) O.[PickLocation] ...),''))

or if [PickLocation] is numeric

AND (ISNULL(O.[PickLocation],-1) = ISNULL((SELECT TOP(1) O.[PickLocation] ...),-1))
Joe Taras
Joe Taras Reply to 2017-11-14 10:52:44Z

I want to suppose your query is exact but you can't use the same alias in main query and sub query. Aliases are used to differentiate the same table. So in your subquery:

  • instead of P use P2
  • instead of B use B2
  • instead of O use O2

So. If your query doens't work please post a data set and expected result set

Jack Reply to 2017-11-17 16:54:59Z

Can you describe primary keys for this tables. I think problem with sub query always return a row but don't matched with main query values.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO