Re: ORA-00904 when creating a view

Try using an alias for that particular column and see what happens.

• Jusung Yang

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'
Received on Thu Oct 24 2002 - 19:03:26 CEST