Hi All,
Well i have this job where i'm suppose to read an oracle table based on the user defined query and create a lookup file .
well in the Oracle Enterprise stage
in the properties tab i have defined as follows
under Source
Query = SELECT
SALES_CYCLE.CYCLE_CODE, TO_CHAR(SALES_CYCLE.BEGIN_DATE,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(SALES_CYCLE.END_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
ETL.SALES_CYCLE SALES_CYCLE
WHERE
SALES_CYCLE.BEGIN_DATE <= sysdate AND SALES_CYCLE.END_DATE >= sysdate
Read Method = User-defined SQL
Under Connection
(i have parameterised the oracle user name password and server name)
DB Option Mode = Auto Generate
Password= #$OraclePassword#
User = #$OracleLoginID#
Remote Server = #$OracleServer
and later i have used a transformer to used a transfomer to create a dummy column and later from transformer to a lookup fileset.
well when i run the job the job's aborting and the fatal error its displaying is as follows
Oracle_Enterprise_7: SELECT is missing in the query: SELECT
SALES_CYCLE.CYCLE_CODE, TO_CHAR(SALES_CYCLE.BEGIN_DATE,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(SALES_CYCLE.END_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
ETL.SALES_CYCLE SALES_CYCLE
WHERE
SALES_CYCLE.BEGIN_DATE <= SYSDATE AND SALES_CYCLE.END_DATE >= SYSDATE
well i have given select in my query then why is crying saying SELECT is missing .
Can anybody please help me how to resolve this issue
Thanks in advance
Problem reading the Oracle Table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Try to execute the same query in any other client application like Toad or PLSql Developer and make sure you have entered the right query. As Ray noted, check if you have any new like character after SELECT key word, so that the rest of the query is executed as a separate query.
For the same reason, Craig use to advise to not to use User defined query as much as possible. Try using SQL Builder.
For the same reason, Craig use to advise to not to use User defined query as much as possible. Try using SQL Builder.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 20
- Joined: Tue Jun 13, 2006 5:16 am
- Location: Pune
Hi,
You need to put alias for start date and end date as follows,
SELECT
SALES_CYCLE.CYCLE_CODE,
TO_CHAR(SALES_CYCLE.BEGIN_DATE,'YYYY-MM-DD HH24:MI:SS') BEGIN_DATE,
TO_CHAR(SALES_CYCLE.END_DATE,'YYYY-MM-DD HH24:MI:SS') END_DATE
FROM
ETL.SALES_CYCLE SALES_CYCLE
WHERE
SALES_CYCLE.BEGIN_DATE <= sysdate AND SALES_CYCLE.END_DATE >= sysdate
Check if query is running fine and put metadata accordingly.
You need to put alias for start date and end date as follows,
SELECT
SALES_CYCLE.CYCLE_CODE,
TO_CHAR(SALES_CYCLE.BEGIN_DATE,'YYYY-MM-DD HH24:MI:SS') BEGIN_DATE,
TO_CHAR(SALES_CYCLE.END_DATE,'YYYY-MM-DD HH24:MI:SS') END_DATE
FROM
ETL.SALES_CYCLE SALES_CYCLE
WHERE
SALES_CYCLE.BEGIN_DATE <= sysdate AND SALES_CYCLE.END_DATE >= sysdate
Check if query is running fine and put metadata accordingly.
SANKET SARAPH