Custom sub-query using ApplyComparison 1

[JRO061601]

I'm trying to force MSTR to run a particular query that shows me a list of IDs that exist in a fact table but not in the lookup table.

I defined the ID twice, allowing the form to only see it on either the fact or the lookup table. I used a custom expression filter using applycomparison. Somehow, the tool still joined the two tables:

"select distinct s22.NDC_NBR NDC_NBR_CUST
from DSSPROD.P_RX_NDC s21
join DSSPROD.FRX_TXN s22
on (s21.NDC_NBR = s22.NDC_NBR)
where s22.NDC_NBR NOT IN (SELECT DISTINCT s21.NDC_NBR FROM P_RX_NDC)"

--where FRX_TXN is the fact table and P_RX_NDC is the lookup, and the filter is defined as:

" ApplyComparison ("#0 NOT IN (SELECT DISTINCT #1 FROM P_RX_NDC)", [Txn NDC]@ID, [LU NDC]@ID)"

I can easily write the SQL I want in a query tool:

"select distinct ndc_nbr
from dssprod.frx_txn
where ndc_nbr not in
(select distinct ndc_nbr
from dssprod.p_rx_ndc)"

Any ideas how to get MSTR to run this?

Thanks.

 

[spod]

As you're using the applycomparison to bypass the MSTR SQL parser you can just write in your sub select as you like:

ApplyComparison ("#0 NOT IN (SELECT DISTINCT zz.ndc_nbr FROM P_RX_NDC zz)", [Txn NDC]@ID"

 

[JRO061601]

true, i don't need to use the attribute object in the sub-select. but that isn't my problem. the tool is joining the fact and the lookup table in the main query, as per my first post. the rest of the SQL is fine, but i need to remove the join.

thanks.

 

[spod]

Did you try without the attribute object in the subselect? Because it's in there the SQL Engine will try to use it.

 

[JRO061601]

ah, good point. that did fix it. thank you very much.