Skip to Content
0
Former Member
Oct 30, 2015 at 06:44 AM

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

32 Views

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)