Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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)

Tags:
Former Member
Former Member replied

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..

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question