Ideas to accomplish desired results in my query

Question

Ideas to accomplish desired results in my query

camiloecheverry11

Old Hand

Points: 384
More actions
October 20, 2016 at 12:55 pm

#151576

I need a query where I can get the results from column qty hand, qty allocated and qty backordered.
My problem is that I have this query and it does not show me qty on hand that is equal to 0 and any value in the other 2 columns.
select
item_location_view.qty_on_hand AS 'Qty on Hand',
item_location_view.qty_allocated AS 'Qty Allocated',
item_location_view.qty_backordered AS 'Qty Backordered'
FROM dbo.inv_mast inv_mast, dbo.item_location_view item_location_view
WHERE inv_mast.item_id = item_location_view.item_id AND ((item_location_view.qty_on_hand>=0) AND (item_location_view.qty_allocated>0) AND (item_location_view.qty_backordered>0))
Thank you all

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

camiloecheverry11 (10/20/2016)
I need a query where I can get the results from column qty hand, qty allocated and qty backordered.
My problem is that I have this query and it does not show me qty on hand that is equal to 0 and any value in the other 2 columns.
select
item_location_view.qty_on_hand AS 'Qty on Hand',
item_location_view.qty_allocated AS 'Qty Allocated',
item_location_view.qty_backordered AS 'Qty Backordered'
FROM dbo.inv_mast inv_mast, dbo.item_location_view item_location_view
WHERE inv_mast.item_id = item_location_view.item_id AND ((item_location_view.qty_on_hand>=0) AND (item_location_view.qty_allocated>0) AND (item_location_view.qty_backordered>0))
Thank you all

How about posting the DDL (CREATE TABLE statement) for the table(s), sample data (INSERT INTO statements) that represents the problem domain, and expected results based on the sample data. Plus, pretty sure you have posted this on other threads.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

camiloecheverry11 (10/20/2016)

I need a query where I can get the results from column qty hand, qty allocated and qty backordered.

Without any DDL, sample data or anything about your data it's hard to guess.

Based on your DDL there's no reason that rows should not appear when item_location_view.qty_on_hand = 0.

I do see that you are filtering out rows WHERE item_location_view.qty_allocated>0 AND item_location_view.qty_backordered>0. Is it possible to have an item_location_view.qty_on_hand of 0 with a item_location_view.qty_allocated > 0? Perhaps you can test by changing all the filters in the WHERE clause to >=0.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

camiloecheverry11 (10/20/2016)
...
FROM dbo.inv_mast inv_mast, dbo.item_location_view item_location_view
WHERE inv_mast.item_id = item_location_view.item_id AND ((item_location_view.qty_on_hand>=0) AND (item_location_view.qty_allocated>0) AND (item_location_view.qty_backordered>0))

it's difficult to say without knowing the query inside of item_location_view, but my initial guess would be that there are some INNER JOINs in the view that would need to be LEFT OUTER JOINs, since there may not be any records in the table that qty_on_hand is calculated from.

MMartin1 One Orange Chip Points: 27764 More actions · Answer 4

My problem is that I have this query and it does not show me qty on hand that is equal to 0 and any value in the other 2 columns.

Why do you say this is a problem? Have you verified that these rows do exist and are not getting pulled? If so then the encapsulated logic within the view is a definite place to look. Have you dissected it ? I do advise you post DDL statements for the table(s) and the view definition with test data as others have mentioned (if you are still interested in getting help). At the moment there isn't much to go on.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27764 More actions · Answer 5

select
item_location_view.qty_on_hand AS 'Qty on Hand',
item_location_view.qty_allocated AS 'Qty Allocated',
item_location_view.qty_backordered AS 'Qty Backordered'
FROM dbo.inv_mast inv_mast, dbo.item_location_view item_location_view
WHERE inv_mast.item_id = item_location_view.item_id AND ((item_location_view.qty_on_hand>=0) AND (item_location_view.qty_allocated>0) AND (item_location_view.qty_backordered>0))

Just as a side note, in your programming practice try adopting the ANSI 92 standard of joining data sets. How would you represent a left join in the above? The '*' I am sure is way deprecated (though will still function for backward compatibility). Writing it this way will provide better readability and troubleshooting by commenting out filtration conditions :

SELECT

v.qty_on_hand AS 'Qty on Hand',

v.qty_allocated AS 'Qty Allocated',

v.qty_backordered AS 'Qty Backordered'

FROM

dbo.inv_mast inv_mast AS tbl

INNER JOIN dbo.item_location_view AS v ON tbl.item_id=v.item_id

WHERE

(item_location_view.qty_on_hand>=0) AND

(item_location_view.qty_allocated>0) AND

(item_location_view.qty_backordered>0) AND

1=1 /* <-- Just to help in your testing ; by commenting out any of the other line(s) in the

where clause . It may help spot your issue. */

----------------------------------------------------