Re: Reports 3:Dynamic Query Fails When Tables Join Change From What It Initially Is In the User Parameter.

From: <RTProffitt_at_beckman.com>
Date: Wed, 12 May 1999 16:50:39 GMT
Message-ID: <7hcbgt$d58$1_at_nnrp1.deja.com>


The best I can see is that in the one that fails, it appears that you are using the column alias in the where clause, not the actual column name. My test showed that you cannot use the column alias in the where clause.

If you have to have that part of the where clause, but there is a situation where one of the tables may not be needed in the query then you could add another parameter and in one case say: where x.col=x.col and in the other case say where 1=1, a dummy phrase just to make the where clause legal.

      Where (:myparm1 = :myparm2)

Good Luck
Robert proffitt
RTProffitt_at_beckman.com

In article <7gv4j0$iu9$1_at_newsin-1.starnet.net>, [Quoted]   "Adam Tadjkarimi" <adamt_at_hsltd.com> wrote:
> I use a user parameter, :P_Dynamic, in one of the report SQLs as:
>
> SELECT &P_Dynamic
>
> And the initial value of this parameter is set to
>
> a.col_x col_x2, a.col_y col_y2 FROM tab_a a, tab_b WHERE a.col_x=b.col_x
>
> This works fine as long as the initial value remains as is, but depending on
> the user selection in the parameter form this can change in the AFTERPFORM
> report trigger to:
>
> a.col_x col_x2, a.col_y col_y2 FROM tab_a a
>
> Once this happens it fails and I get the error message:
>
> ORA000904 invalid column name
> SELECT a.col_x col_x2, a.col_y col_y2 FROM tab_1 a
> WHERE (:col_x3 =col_x2)
>
> I should add that :col_x3 is the Datalink column from the parent query.
>
> Any ideas why this is happening any hints to correct it or work around?
>
> Thank,
> Adam Tadj
> Adamt_at_hsltd.com
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 12 1999 - 18:50:39 CEST

Original text of this message