Hi,
I want to implement the following query in DI…
SELECT G.[Order Id] FROM [Generatable Reports] G
WHERE G.[Order Id] NOT IN (SELECT E.[Order Id] FROM [Excluded Report Orders] E)
Both the tables in question are in the target database.
Can anyone help me with how to do this?
Thanks,
Anu
anu23 (BOB member since 2007-12-11)
This is one area where DI is annoying in my opinion. It can’t cope with this in a single query.
You need to have two sources (Generatable Reports [G] and Excluded Report Orders [E]), take these into a query and perform an OUTER join (setting G as the outer source, so you get all G records), with the WHERE clause set to E.[Order ID] = G.[Order ID]. Make sure you have both the G and E Order IDs in the output
Then you need to put the result into a second query with a WHERE clause that states E Order ID IS NULL, the results of that gives you all the records from G that are not in E.
(The reason this must be 2 steps is that DI cannot distinguish between the join clause and the where clause, so if in a single step it attempts to join G and E with the same Order ID and also only allow E Order ID is null to progress, so you end up with nothing. There have been suggestions on this forum on how DI could be changed to make this less annoying)
HuwD 
(BOB member since 2007-04-19)
Hey,
Thanks so much for the response…
I do agree it is tedious to do this in DI! 
Anyways, I gotta implement it this way…
Anu 
anu23 (BOB member since 2007-12-11)
Here’s a possible solution if you really want to do a sub-query.
Use the pushdown SQL function to artificially tag on a hand-coded piece of SQL to the where clause.
Done it before and it can work fine in certain situations.
Once you’ve added it - view the generated SQL to see your addition. You’ll be able to tell from this SQL if you’ve done it right and it’s going to work.
Yours should be pretty easy if the tables are in the same schema.
Cheers
dcstevenson (BOB member since 2006-09-06)
SQL transform will do the work.
I have used SQL transforms to replace source tables which may need any joints in DI. Using Stantard SQL for this transform.
Billy_qing 
(BOB member since 2007-11-16)
The downside to doing that is that you cannot see that it is used from the table list in the datastore. As the jobs become more complex and convoluted it becomes difficult to assess the impact of changes to the source, but agreed it will do the trick.
HuwD (BOB member since 2007-04-19)
I think I’ll add something to the running enhancement-ideas post about this…
dnewton 
(BOB member since 2004-01-30)
Hi Guyz,
Thanks for the responses…I did try implementing this query using SQL and pushdown sql…but I keep getting an “invalid WHERE clause” error.
here’s the pushdown sql i used…
pushdown_sql(‘Target’, [GENERATABLE REPORTS].[ORDER ID] not in (SELECT [EXCLUDED REPORT ORDERS].[ORDER ID] FROM [EXCLUDED REPORT ORDERS]))
I am assuming that the sql should be exactly the way it can be run in the target db. (SQL Server 2005 in this case)
here’s the error…
[Query:Query_1]
BODI-1111078:Invalid WHERE clause. Additional information: <BODI-1112394:Syntax error at line <1>: <pushdown_sql(‘Target’, [GENERATABLE REPORTS].[ORDER ID] not in (SELECT [EXCLUDED REPORT ORDERS].[ORDER ID] FROM [EXCLUDED REPORT ORDERS]))>: near <[> found <’[’> expecting <+, AL_UNSPECIFIED_PARAM, a float, an integer, a null, a string, a decimal, ->.
1 error(s), 0 warning(s).
. Please check and fix the syntax and retry the operation.>.
:?
Thanks,
Anu
anu23 (BOB member since 2007-12-11)
pushdown_sql(‘Target’, [GENERATABLE REPORTS].[ORDER ID] not in (SELECT [EXCLUDED REPORT ORDERS].[ORDER ID] FROM [EXCLUDED REPORT ORDERS]))
If that’s the exact text that you’ve entered then you are missing single quotes around your SQL.
Should be:
pushdown_sql(‘Target’,’ [GENERATABLE REPORTS].[ORDER ID] not in (SELECT [EXCLUDED REPORT ORDERS].[ORDER ID] FROM [EXCLUDED REPORT ORDERS]) ')
ScoobyDoo (BOB member since 2007-05-10)