I need help with a query

  • camiloecheverry11

    Old Hand

    Points: 374

    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

  • J Livingston SQL

    SSC Guru

    Points: 51272

    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: 442118

    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: 374

    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: 51272

    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: 374

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

  • Lynn Pettis

    SSC Guru

    Points: 442118

    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: 374

    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: 51272

    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: 27488

    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.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

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

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