Ideas to accomplish desired results in my query

  • camiloecheverry11

    Old Hand

    Points: 374

    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

  • Lynn Pettis

    SSC Guru

    Points: 442118

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

    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.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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: 41700

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

    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.

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

  • MMartin1

    One Orange Chip

    Points: 27488

    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. */

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

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

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