Using a sql inside IN condition for parameters (1 Viewer)

[fernando.rosales]

I have a sql to run and I have to use many values as parameters (over 1000), which is why I am trying to use a SQL inside a IN condition.

I am testing the sql with only 1 value for now. The parameter I am using will be 2208287. This returns in a matter of seconds.

Select a.po_id, a.sku12, sum(a.unit) AS Units, sum(a.weighted) AS Weighted_Units, ((sum(a.weighted))/ (sum(a.unit))) AS weighted_turntime
From(
SELECT ds.src_evnt_txn_tmst AS alloc_ts, bc.src_evnt_txn_tmst AS ship_ts, ds.Po_id, Left(bc.sku16,12) AS SKU12, ds.distro_id AS alloc_distro, bc.distro_id AS Ship_distro, Sum(bc.unit_qty) AS Unit, (bc.src_evnt_txn_tmst-ds.src_evnt_txn_tmst) AS Expr1, (Sum(bc.unit_qty)*(bc.src_evnt_txn_tmst-ds.src_evnt_txn_tmst)) AS Weighted
FROM ROSS_REPORT_DISTRO_DTLS_SUM AS ds, BOL_CONTNR_SKU16_RTED AS bc
WHERE (((ds.distro_id)=bc.distro_id) And ((ds.sku16)=bc.sku16) And ((ds.po_id) In ('2208287')))
GROUP BY ds.src_evnt_txn_tmst, bc.src_evnt_txn_tmst, ds.Po_id, ds.distro_id, bc.distro_id, ds.sku16, bc.sku16)A
group by a.sku12, a.Po_id

but it does not work when I add a sql inside the IN condition. It actually runs forever and eventually I have to break it to stop. Can some one please help and let me know why this is not working with a sql inside a IN condition.

Also please note:
table ROSS_REPORT_DISTRO_DTLS_SUM is a linked table
table BOL_CONTNR_SKU16_RTED is a linked table
table CopyOfdata is a local table (This is where I will put all of my values to act as parameters)

Select a.po_id, a.sku12, sum(a.unit) AS Units, sum(a.weighted) AS Weighted_Units, ((sum(a.weighted))/ (sum(a.unit))) AS weighted_turntime
From(
SELECT ds.src_evnt_txn_tmst AS alloc_ts, bc.src_evnt_txn_tmst AS ship_ts, ds.Po_id, Left(bc.sku16,12) AS SKU12, ds.distro_id AS alloc_distro, bc.distro_id AS Ship_distro, Sum(bc.unit_qty) AS Unit, (bc.src_evnt_txn_tmst-ds.src_evnt_txn_tmst) AS Expr1, (Sum(bc.unit_qty)*(bc.src_evnt_txn_tmst-ds.src_evnt_txn_tmst)) AS Weighted
FROM ROSS_REPORT_DISTRO_DTLS_SUM AS ds, BOL_CONTNR_SKU16_RTED AS bc
WHERE (((ds.distro_id)=bc.distro_id) And ((ds.sku16)=bc.sku16) And ((ds.po_id) In (select PO_ID from copyofdata)))
GROUP BY ds.src_evnt_txn_tmst, bc.src_evnt_txn_tmst, ds.Po_id, ds.distro_id, bc.distro_id, ds.sku16, bc.sku16)A
group by a.sku12, a.Po_id

 

[vbaInet]

Change it to an INNER JOIN instead. It will run much faster.

 

[fernando.rosales]

Where will I place the inner join? I haven't really used this

 

[vbaInet]

I thought this is SQL code that you wrote? Is that not the case?