ROWID in oracle not working

[Ragunathan Gunasekaran]

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 all

sqlcode is: -1756
esql complaint: ORA-01756: quoted string not properly terminated

I used the same query that datastage used to prepare but it executed without any hassle. Any clues on this please.....


I am not finding any missing quotes in the query as i copied the query prepared by datastage and pasted in the SQL client.

[ray.wurlod]

Copy the query from the job log and paste it here, surrounded by code tags.

[Ragunathan Gunasekaran]

[/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')

Code: Select all

[/quote]

[ArndW]

I don't see a quoted string problem, but it seems that you use "column_name1" and "col1" - are these legal column names?

[ray.wurlod]

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.

[Ragunathan Gunasekaran]

Hi The below one is the exact query that i have used

Code: Select all

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')

[ray.wurlod]

Here's what I got running it through the SQL Navigator formatter. Note that there is a space ahead of the DATENO alias name.

Code: Select all

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.