SQL Correction - Require your help

User_E9OMM

Hi !

Can anyone please help correct the below query ? ( SQL Server syntax)

We use SQL Server database and particularly on this it's giving errors. Syntax errors.

The same version works fine on oracle database.

Below query gives me Agent Error info from BI Platform schema.

( Syntax error near RELATIVE_ORDER)

select   

JOB_ID, INSTANCE_ID,   

replace(   

replace(   

replace(   

replace(   

max( decode(val_number, 1, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 2, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 3, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 4, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 5, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 6, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 7, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 8, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 9, ERROR_MSG_TEXT, null) ) ||   

max( decode(val_number, 10, ERROR_MSG_TEXT, null) ),   

'$_\s_$',' '),   

'$_\r_$',' '),   

'$_\n_$',' '),   

'$_\,$',',') AS ERROR_MSG   

from    ( select  JOB_ID, INSTANCE_ID,   

row_number() over ( partition by JOB_ID, INSTANCE_ID order   

RELATIVE_ORDER ) as val_number ,  

ERROR_MSG_TEXT   

from S_NQ_ERR_MSG ) ABC   

GROUP BY JOB_ID, INSTANCE_ID

Sherry George

1877648 wrote:Hi !Can anyone please help correct the below query ? ( SQL Server syntax)We use SQL Server database and particularly on this it's giving errors. Syntax errors. The same version works fine on oracle database.Below query gives me Agent Error info from BI Platform schema. ( Syntax error near RELATIVE_ORDER)select JOB_ID, INSTANCE_ID, replace( replace( replace( replace( max( decode(val_number, 1, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 2, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 3, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 4, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 5, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 6, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 7, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 8, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 9, ERROR_MSG_TEXT, null) ) || max( decode(val_number, 10, ERROR_MSG_TEXT, null) ), '$_\s_$',' '), '$_\r_$',' '), '$_\n_$',' '), '$_\,$',',') AS ERROR_MSG from ( select JOB_ID, INSTANCE_ID, row_number() over ( partition by JOB_ID, INSTANCE_ID order RELATIVE_ORDER ) as val_number , ERROR_MSG_TEXT from S_NQ_ERR_MSG ) ABC GROUP BY JOB_ID, INSTANCE_ID 

The query you posted doesn't work on Oracle either because of the incorrect use of ORDER BY. That aside, this question/issue doesn't have anything to do with OBIEE. I would say to ask this in SQL/PLSQL space.