DSXchange

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

Are there any newline characters in your user-defined SQL? I find that it (user-defined SQL) generally works best if there are none.

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.

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.

Creating Alias is not really a mandate option though. the order of field and the Derivation can take care.

It's not required at all. The column names defined in the stage are, in effect, the 'aliases'.