I need help with a query

Question

Post reply

I need help with a query

camiloecheverry11

Old Hand

Points: 384
More actions
October 19, 2016 at 11:45 am

#104333

I have a table a,b,c
and I need to show a if is >=0 and b >0 and c > 0
I know that this is simple but for some reason has not been working for mi.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

what have you tried so far?

from you brief details maybe

SELECT a,

b,

c

FROM sometable

WHERE(a >= 0)

AND (b > 0)

AND (c > 0);

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

camiloecheverry11 (10/19/2016)
I have a table a,b,c
and I need to show a if is >=0 and b >0 and c > 0
I know that this is simple but for some reason has not been working for mi.
Thanks

Wow, this is really vague. What do you mean if a >= 0 and b >0 and c > 0 when a,b, and c are tables?

Sorry, but without more details, including the DDL for the tables, sample data for the tables, and expected results based on the sample data we can't really provide an answer to this question.

camiloecheverry11 Old Hand Points: 384 More actions · Answer 3

Hi linn,

thank you all for your help.

This is the situation in qty_on_hand does not appear the quantity that are in 0. What I need is to show all quantitys on hand that are > or egual to 0 and all allocated > 0 and back order > 0

this is my query

WHERE inv_mast.item_id =item_location_view.item_id AND ((item_location_view.location_id<$300) AND (item_location_view.qty_on_hand>=$0) AND (item_location_view.qty_allocated>$0) AND (item_location_view.qty_backordered>$0))

Thanks again.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

why are you using ">$0" ...ie the $ sign?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

camiloecheverry11 Old Hand Points: 384 More actions · Answer 5

camiloecheverry11

Old Hand

Points: 384

October 19, 2016 at 12:39 pm

#1907503

I normally used to refer to numbers. is not correct?

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

camiloecheverry11 (10/19/2016)
Hi linn,
thank you all for your help.
This is the situation in qty_on_hand does not appear the quantity that are in 0. What I need is to show all quantitys on hand that are > or egual to 0 and all allocated > 0 and back order > 0
this is my query
WHERE inv_mast.item_id =item_location_view.item_id AND ((item_location_view.location_id<$300) AND (item_location_view.qty_on_hand>=$0) AND (item_location_view.qty_allocated>$0) AND (item_location_view.qty_backordered>$0))
Thanks again.

This is not a query, it is a snippet, the WHERE clause of a query to be precise. And if those values are integers there is no need for the $. Also, I would suggest using white space to make your code more readable to humans.

camiloecheverry11 Old Hand Points: 384 More actions · Answer 7

This is exactly how I have the entire query.

SELECT item_location_view.item_id AS 'Item',

item_location_view.item_desc AS 'Description',

inv_mast.class_id1 AS 'Brand',

item_location_view.location_id AS 'Location',

item_location_view.qty_on_hand AS 'Qty on Hand',

item_location_view.qty_allocated AS 'Qty Allocated',

item_location_view.qty_on_po AS 'Qty on PO',

item_location_view.qty_backordered AS 'Qty Backordered',

qty_on_hand-qty_allocated AS 'Qty Available',

item_location_view.moving_average_cost AS 'Cost',

item_location_view.price1 AS 'Price',

item_location_view.purchase_discount_group AS 'Group',

item_location_view.product_group_id AS 'Product Group',

item_location_view.product_group_desc AS 'Prod Group Description',

item_location_view.last_purchase_date AS 'Last Purchase Date',

inv_mast.weight AS 'Cube Master Pack',

inv_mast.net_weight AS 'Cube',

inv_mast.cube AS 'Weight',

inv_mast.purchasing_weight AS 'Purchasing Volume'

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.location_id<$300) AND (item_location_view.qty_on_hand=>$0 and item_location_view.qty_allocated >0 and item_location_view.qty_backordered >0))

ORDER BY item_location_view.item_id

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

camiloecheverry11 (10/19/2016)
This is exactly how I have the entire query.
SELECT item_location_view.item_id AS 'Item',
item_location_view.item_desc AS 'Description',
inv_mast.class_id1 AS 'Brand',
item_location_view.location_id AS 'Location',
item_location_view.qty_on_hand AS 'Qty on Hand',
item_location_view.qty_allocated AS 'Qty Allocated',
item_location_view.qty_on_po AS 'Qty on PO',
item_location_view.qty_backordered AS 'Qty Backordered',
qty_on_hand-qty_allocated AS 'Qty Available',
item_location_view.moving_average_cost AS 'Cost',
item_location_view.price1 AS 'Price',
item_location_view.purchase_discount_group AS 'Group',
item_location_view.product_group_id AS 'Product Group',
item_location_view.product_group_desc AS 'Prod Group Description',
item_location_view.last_purchase_date AS 'Last Purchase Date',
inv_mast.weight AS 'Cube Master Pack',
inv_mast.net_weight AS 'Cube',
inv_mast.cube AS 'Weight',
inv_mast.purchasing_weight AS 'Purchasing Volume'
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.location_id<$300) AND (item_location_view.qty_on_hand=>$0 and item_location_view.qty_allocated >0 and item_location_view.qty_backordered >0))
ORDER BY item_location_view.item_id

EDIT >> forget it...I see you have already posted another thread

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MMartin1 One Orange Chip Points: 27768 More actions · Answer 9

This is a cross post.

http://www.sqlservercentral.com/Forums/Topic1827737-145-1.aspx">

http://www.sqlservercentral.com/Forums/Topic1827737-145-1.aspx

Rather than posting the same thing in multiple spots, people will better serve you better if you help them to help you. In both of these threads you are asked to give table definitions, sample data, etc... See if this article illustrates better the ask

http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/

It begins with the create table script(s), then on to sample data and anything else that may be needed. In your case it involves the definition of a view I believe.

BTW, cross posting is frowned upon in the forum.

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