Home  /  Questions  /  Question



0   0
May 18, 2016


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




250   96.9
May 20, 2016
Hello,

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

Best Regards,
Eric Santoso