1 Reply Latest reply on Jul 14, 2017 12:52 PM by Sherry George

    SQL Correction - Require your help

    1877648

      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

        • 1. Re: SQL Correction - Require your help
          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.