Nested use of tables inside the script giving error of table not found.

[Sakura]

///The script is like

example

abc:

Load

     [plant Id],

     [type]

from [filepath\pqr.xlsx] //////// the data explicitly given by client for the internal use and [type] not related to plants dimension 

Data:

SQL

SELECT *,p.[Plant Name] FROM Table

INNER JOIN Plant p ON Table.[plant Id]= p.[plant Id]

WHERE Table.[plant Id] IN (SELECT DISTINCT [plant Id] from abc)

The error is Invalid object name abc.

Kindly help!

[avinashelite]

Issue is with your SQL script hit htis Query to DB and check whether its working fine

SELECT *,p.[Plant Name] FROM Table

INNER JOIN Plant p ON Table.[plant Id]= p.[plant Id]

WHERE Table.[plant Id] IN (SELECT DISTINCT [plant Id] from abc)

[Sakura]

This is not the actual script. I have created this just for the sample. I want to use the subquery for filtering the plant codes and that is not working.

[vishsaggi]

Comment your SQL Script and just run your Abc load and let us know what happens?

[flipside · Accepted solution]

Hi Ash,

If the table 'abc' in the SQL code is trying to point to the previous table 'abc' which loads from pqr.xlsx, then you can't do it that way. The SQL code only uses objects that exist in the database and hence why the table isn't found. How many distinct values of [plant Id] in table abc are there? And how many rows does the SQL code return (without the WHERE filter)?

[Sakura]

The list changes when client demands so can not tell how many. But thank you for the answer, I also think that we can not use  the previously loaded tables. But then I do not know why are we giving names to those tables(loads).

Thank you!