BusinessObjects Board

Using Constants in Designer for Union Queries

building semantic layer
#1

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?

By the way, you guys are awesome professionals.

tkdrocks :us: (BOB member since 2003-06-10)

#2

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.

Andreas :de: (BOB member since 2002-06-20)

#3

That did the trick. Thank you for the quick response Andreas. Once again, BOB comes through.

tkdrocks :us: (BOB member since 2003-06-10)

#4

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.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#5

Can you elaborate on using ‘ALL’ in a custom LOV? What exactly are you doing and how do you use it ?

tscoccol :us: (BOB member since 2003-11-15)

#6

Please, take a look a this FAQ Designer entry and this one dealing with prompts.

Andreas :de: (BOB member since 2002-06-20)