Re: ORA-00904 when creating a view

From: Sagi <sag1rk_at_yahoo.com>
Date: 29 Oct 2002 09:47:17 -0800
Message-ID: <54d80104.0210290947.32b0134b_at_posting.google.com>

odd.morten.sveas_at_accenture.com (=?ISO-8859-1?Q?Odd_Morten_Sve=E5s?=) wrote in message news:<4306a83.0210222147.3f5b1a88_at_posting.google.com>...
> Hi
>
> I want to create a view, that uses a function. If I isolate the select
> that creates the view and runs it, it all works fine. But when i put
> the select in create view statement i get ORA-00904: invalid column
> name, and indikating the function as the invalid column.
>
> I bouth the select and the create view as the same user, and its the
> owner of the function. (Actualy the packaege thats contain the
> function)
>
> I'm on 8.1.7
>
> here is the statement, and its the fnd_profile.value() that make
> problems.
>
> create or replace view xxmx.mx040v_ap_match
> (artskonto,dato,bilagsnummer,belop,serie,code_combination_id) as
> SELECT
> GL.GL_CODE_COMBINATIONS.SEGMENT1 ARTSKONTO,
> GL.GL_JE_LINES.EFFECTIVE_DATE DATO,
> GL.GL_JE_LINES.REFERENCE_4 BILAGSNUMMER,
> NVL( GL.GL_JE_LINES.ACCOUNTED_DR, 0) - NVL(
> GL.GL_JE_LINES.ACCOUNTED_CR, 0) BELOP,
> substr(GL.GL_JE_LINES.REFERENCE_4,length(ltrim(fnd_profile.value('BSKK_LOGINORG'),'0'))+1,2),
> GL.GL_JE_LINES.CODE_COMBINATION_ID
> FROM
> GL.GL_JE_LINES,
> GL.GL_CODE_COMBINATIONS,
> GL.GL_JE_HEADERS,
> ap.AP_AE_LINES_ALL aal ,
> AP.ap_checks_all aca
> WHERE
> GL.GL_JE_LINES.CODE_COMBINATION_ID =
> GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
> AND GL.GL_JE_LINES.JE_HEADER_ID =
> GL.GL_JE_HEADERS.JE_HEADER_ID
> AND gl.gl_je_lines.GL_SL_LINK_ID = aal.GL_SL_LINK_ID
> AND aal.SOURCE_ID = aca.CHECK_ID
> AND GL.GL_CODE_COMBINATIONS.SEGMENT5 =
> fnd_profile.value('BSKK_LOGINORG')
> AND GL.GL_JE_HEADERS.STATUS = 'P'
> AND GL.GL_JE_LINES.GL_SL_LINK_TABLE = 'APECL'
Hi,

I think the problem is because you are missing ALIAS and therefore the column length creating a problem.

substr(GL.GL_JE_LINES.REFERENCE_4,length(ltrim(fnd_profile.value('BSKK_LOGINORG'),'0'))+1,2), <======= Alias 'serie' Missing here

So it should be:

create or replace view xxmx.mx040v_ap_match  (artskonto,dato,bilagsnummer,belop,serie,code_combination_id) as  SELECT

    GL.GL_CODE_COMBINATIONS.SEGMENT1  ARTSKONTO,
    GL.GL_JE_LINES.EFFECTIVE_DATE DATO,
    GL.GL_JE_LINES.REFERENCE_4 BILAGSNUMMER,

    NVL( GL.GL_JE_LINES.ACCOUNTED_DR, 0) - NVL(  GL.GL_JE_LINES.ACCOUNTED_CR, 0) BELOP,
    substr(GL.GL_JE_LINES.REFERENCE_4,length(ltrim(fnd_profile.value('BSKK_LOGINORG'),'0'))+1,2) serie,

   GL.GL_JE_LINES.CODE_COMBINATION_ID
 FROM

         GL.GL_JE_LINES,
         GL.GL_CODE_COMBINATIONS,
         GL.GL_JE_HEADERS,
 	ap.AP_AE_LINES_ALL aal ,
 	AP.ap_checks_all aca
     WHERE
         GL.GL_JE_LINES.CODE_COMBINATION_ID =
 GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
         AND GL.GL_JE_LINES.JE_HEADER_ID =
 GL.GL_JE_HEADERS.JE_HEADER_ID
 	AND gl.gl_je_lines.GL_SL_LINK_ID = aal.GL_SL_LINK_ID
 	AND aal.SOURCE_ID = aca.CHECK_ID
         AND GL.GL_CODE_COMBINATIONS.SEGMENT5 =
 fnd_profile.value('BSKK_LOGINORG')
         AND GL.GL_JE_HEADERS.STATUS = 'P'
     	AND GL.GL_JE_LINES.GL_SL_LINK_TABLE = 'APECL'

Execute the above and see if it works.

Regards,
Sagi Received on Tue Oct 29 2002 - 18:47:17 CET