Back to list
Views:   3.4K
Replies:  1

Cleaning and speeding up code

How would I clean up this code and make the system response faster?
I have inventories that could be in any of the defined locations identified by (loc 1, 2, 3....)
The report below works, but figured there would be a simpler code set that is more efficient.

SELECT DISTINCT imitmidx_sql.item_no, imitmidx_sql.item_desc_1, imitmidx_sql.item_desc_2,
                             (SELECT        qty_on_hand
                               FROM            iminvloc_sql AS IMINVLOC_SQL_1
                               WHERE        (loc = '1') AND (item_no = imitmidx_sql.item_no)) AS Loc1,
                             (SELECT        qty_on_hand
                               FROM            iminvloc_sql AS IMINVLOC_SQL_2
                               WHERE        (loc = '2') AND (item_no = imitmidx_sql.item_no)) AS Loc2,
                             (SELECT        qty_on_hand
                               FROM            iminvloc_sql AS IMINVLOC_SQL_3
                               WHERE        (loc = '3') AND (item_no = imitmidx_sql.item_no)) AS Loc3,
                             (SELECT        qty_on_hand
                               FROM            iminvloc_sql AS IMINVLOC_SQL_4
                               WHERE        (loc = '4') AND (item_no = imitmidx_sql.item_no)) AS Loc4,
                             (SELECT        qty_on_hand
                               FROM            iminvloc_sql AS IMINVLOC_SQL_5
                               WHERE        (loc = '5') AND (item_no = imitmidx_sql.item_no)) AS Loc5
FROM            iminvloc_sql INNER JOIN
                         imitmidx_sql ON iminvloc_sql.item_no = imitmidx_sql.item_no
WHERE        (imitmidx_sql.activity_cd = 'a')
ORDER BY imitmidx_sql.item_no

Don Williams, May 18, 2016
Reply 1

Maybe you can use CTE and JOIN Table if you are using SQL server as database

Best Regards,
Eric Santoso
Eric Santoso, May 20, 2016
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Cookies       Do Not Sell       Licensing      
Made with    in Austin, Texas.  - vsn 44.0.0
© Data & Object Factory, LLC.