I am currently using a freehand sql query to accomplish a fairly complicated task. I want to replace this with a Universe. I am querying on SAP.R3 using Oracle and do not have rights to create tables or add data in any way. Here is a piece of the sql:
SELECT
ltrim(SAPR3.AFPO.AUFNR,0) as OrdNum,
ltrim(SAPR3.AFKO.STLNR,0) AS BOM,
ltrim(SAPR3.STPO.IDNRK,0) as Component,
SAPR3.STPO.MENGE as ComponentQTY,
ltrim(SAPR3.AFPO.MATNR,0) as MaterialOrdered,
SAPR3.MAKT.MAKTX as ComponentName,
SAPR3.AFPO.GSBER as Plant,
SAPR3.AFKO.GSTRS as Date1, β0β as PONUM,
0 AS INV_QTY
From (See tables above)
Where
(Yada, yada, yada)
Union All
SELECT β0β as OrdNum,
β0β AS BOM,ltrim(SAPR3.MARD.MATNR,0) as Component, 0 as ComponentQTY,
β0β as MaterialOrdered,
SAPR3.MAKT.MAKTX as ComponentName,
SAPR3.MARD.WERKS as Plant, β0β as Date1,
β0β as PONUM,
SAPR3.MARD.LABST AS INV_QTY
From (More Tables)
Where
etc.
Now for the question. Is it possible to create an object and assign a constant as I have done with this freehand query in Designer?
Yes, just create a new object in Business Objects Designer, in the Select box type in β0β.
The object alone will not parse (because it does not reference any table), but if you use it in conjunction with other βregularβ universe objects to build your data provider in Business Objects Reporter it will work just fine.
I always keep a series of constants around β¦ space, zeroes, null to balance out a union β¦ βALLβ to include in a custom LOV β¦ even a chr(0) to force something (like βALLβ) to sort to the top. They can be quite handy.