Hi ,
I am using the ROWID in the user defined sql query .. When i execute the query from the SQL client its working fine and when the same is used inside the Oracle stage its showing the following error
[/code]SELECT
column_name1,column_name2
FROM table_name1
WHERE ROWID in(SELECT MAX(ROWID) FROM table_name1 GROUP BY col1,col2 HAVING TO_CHAR(col2,'YYYY-MM-DD')='2008-09-30')
When you use a Code tag to open, don't use a Quote tag to close - use a Code tag to close. Please go back and edit your post.
Not sure about the legality of using different column names in the SELECT and GROUP BY clauses - are they defined as synonyms? You certainly have not specified aliases.
I also suspect that you did not use Copy/Paste as requested.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
SELECT
HIERARCHY_ID, EMPLOYEE_NO,
(SELECT CAST(DATENO as INT) FROM owner.DATE_HIERARCHY_DIM WHERE
to_char(EXTERNAL_DATE ,'YYYY-MM-DD') ='2008-09-03')DATENO
FROM owner.FIN_HIERARCHY_DIM
WHERE ROWID in(SELECT MAX(ROWID) FROM owner.FIN_HIERARCHY_DIM GROUP BY EMPLOYEE_NO,END_DATE HAVING TO_CHAR(END_DATE,'YYYY-MM-DD')='3500-12-31')
SELECT hierarchy_id, employee_no,
(SELECT CAST (dateno AS INT)
FROM owner.date_hierarchy_dim
WHERE TO_CHAR (external_date, 'YYYY-MM-DD') = '2008-09-03') dateno
FROM owner.fin_hierarchy_dim
WHERE ROWID IN (SELECT MAX (ROWID)
FROM owner.fin_hierarchy_dim
GROUP BY employee_no, end_date
HAVING TO_CHAR (end_date, 'YYYY-MM-DD') = '3500-12-31')
The formatter complains if there are syntax errors, and that's the only one I spotted by inspection.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.