How to use the prompt function for the alias columns in custom sql?

[Former Member]

Hi all,

In my scenario, i am joining and merging many tables. So i used alias for some table. I want to use prompt for a field which is a alias column. I used the prompt function in custom sql and it throws ORA -00936 error. Can someone help me correct my query. Please find query below

Select

  trans.ODOC,

  trans.OBAT,

  trans.DIM5,

  trans.AMNT,

  trans.TRDT,

  trans.CRDT,

  trans.CRUS,

  trans.RCNO,

  trans.ITEM,

  trans.CWAR,

  trans.CUNI,

  trans.NUNI,

  Table_B.cpcl,

  busorg.SUB_FIN_COMP_ID

from

(SELECT

t418.company,

t418.dim4,

  t418.ODOC,

  t418.OBAT,

  t418.DIM5,

  t418.AMNT,

  t418.TRDT,

  t418.CRDT,

  t418.CRUS,

  t418.RCNO,

  tint.ITEM,

  tint.CWAR,

  tint.CUNI,

  tint.NUNI

FROM

(SELECT

TFGLD106.company,

TFGLD106.dim4,

  TFGLD106.ODOC,

  TFGLD106.OBAT,

  TFGLD106.DIM5,

  TFGLD106.AMNT,

  TFGLD418.TRDT,

  TFGLD418.CRDT,

  TFGLD418.CRUS,

  TFGLD418.RCNO,

  TFGLD418.ocom,

  TFGLD418.tror,

  TFGLD418.fitr,

  TFGLD418.sern,

  TFGLD106.leac,

  TFGLD106.fyer,

  TFGLD106.fprd

FROM

  TFGLD106,

  TFGLD418

Where TFGLD106.otyp = TFGLD418.ttyp

AND TFGLD106.odoc = TFGLD418.docn

AND TFGLD106.olin = TFGLD418.lino) t418,

tfgld417 tint

Where tint.ocom = t418.ocom

AND tint.tror = t418.tror

AND tint.fitr = t418.fitr

AND tint.trdt = t418.trdt

AND tint.sern = t418.sern

AND tint.leac = t418.leac

AND tint.fyer = t418.fyer

AND tint.fprd = t418.fprd

UNION

SELECT

t418.company,

t418.dim4,

  t418.ODOC,

  t418.OBAT,

  t418.DIM5,

  t418.AMNT,

  t418.TRDT,

  t418.CRDT,

  t418.CRUS,

  t418.RCNO,

  tint.ITEM,

  tint.CWAR,

  tint.CUNI,

  tint.NUNI

FROM

(SELECT

TFGLD106.company,

  TFGLD106.ODOC,

  TFGLD106.OBAT,

  TFGLD106.DIM5,

  TFGLD106.DIM4,

  TFGLD106.AMNT,

  TFGLD418.TRDT,

  TFGLD418.CRDT,

  TFGLD418.CRUS,

  TFGLD418.RCNO,

  TFGLD418.ocom,

  TFGLD418.tror,

  TFGLD418.fitr,

  TFGLD418.sern,

  TFGLD106.leac,

  TFGLD106.fyer,

  TFGLD106.fprd

FROM

  TFGLD106,

  TFGLD418

Where TFGLD106.otyp = TFGLD418.ttyp

AND TFGLD106.odoc = TFGLD418.docn

AND TFGLD106.olin = TFGLD418.lino) t418,

tfgld410 tint

Where tint.ocom = t418.ocom

AND tint.tror = t418.tror

AND tint.fitr = t418.fitr

AND tint.trdt = t418.trdt

AND tint.sern = t418.sern

AND tint.leac = t418.leac

AND tint.fyer = t418.fyer

AND tint.fprd = t418.fprd) trans,

(Select item,cpcl From tcibd001) Table_B,

bo_readfin.ta_tml_bus_org_hry_dim4@bo_fd1p1 busorg

Where

trans.item = Table_B.item

AND TRIM (trans.company) = busorg.fincompid

AND TRIM (trans.dim4) = busorg.dim4

And busorg.SUB_FIN_COMP_ID='246_24612T'

AND trans.DIM5  =  @prompt('DIM5','A','trans\Dim5',Mono,Constrained,Not_Persistent,,User:0)

AND trans.TRDT  <=  @prompt('Transactions before','D','trans\Trdt',Mono,Constrained,Not_Persistent,,User:1)

[amitrathi239 · Accepted solution]

Hi,

recheck the data type of DIM5 and TRDT objects.

check the attached link if this is relevant to your issue.

Note 1676551:BI4.0: Error: Database Error: ORA-000936: Missing Expression. (IES 10901) while vailda...

Amit

[Former Member · Accepted solution]

Hi Amit,

Thanks for the response. The datatypes are fine. I came to know about the reason.

We cant use a prompt for a alias table. we need to use the prompt only with the base tables.

I changed my query like that and it worked. That's my analysis..