How to use CASE statements in BO SQL

Hi,

Please let me know how to work with the Select Case …End statement in BO Designer.

Thanks & Regards,
Anuradha. J


JAASI (BOB member since 2004-06-08)

All you need to do is put the CASE Statement in the Select Part of the Object Definition…
Eg:


CASE WHEN Col1=1 THEN Col2 Else Col3 END

Sridharan :india: (BOB member since 2002-11-08)

Thanks Sri…


JAASI (BOB member since 2004-06-08)

The exact syntax might vary from DBMS to DBMS though (Oracle vs MS SQL Server etc.).


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

If you are using a CASE statement in a measure, then it is better to use sum(CASE WHEN … THEN … ELSE … END) rather than CASE WHEN … THEN sum(,) ELSE … END

I have a table(view) called DETAIL_V which has a column ORDER LINES
i’m giving a CASE ststement like this ,in order to count the orderlines if they are eqaul to one.

SELECT
COUNT(CASE WHEN DETAIL_V.ORDER LINES = 1 THEN   
count(DETAIL_V.ORDER LINES ELSE  0 END)

but its displaying the error
‘NESTED GROUP FUNCTION WITHOUT GROUP BY’
can anyone tell me whether this statement is correct?


randyj262004 (BOB member since 2004-04-09)

Try:

COUNT
(
CASE 
   WHEN DETAIL_V.ORDER LINES = 1 
   THEN DETAIL_V.ORDER LINES
END
) 

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

Randy, please stop cross-posting/double posting (I deleted your other cross-post).

Thank you.


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

Thanks, a lot and sorry for posting it in wrong forum.
By mistake i posted it.

I’l try that syntax
Thanks


randyj262004 (BOB member since 2004-04-09)

Hi,
In object1 SELECT i gave :

COUNT 
( 
CASE 
   WHEN DETAIL_V.ORDER LINES = 1 
   THEN DETAIL_V.ORDER LINES 
END 
)

In object2 SELECT i gave :

COUNT 
( 
CASE 
   WHEN DETAIL_V.ORDER LINES > 1 
   THEN DETAIL_V.ORDER LINES 
END 
)  

But when i pull the two objects into the report then both ojects are displaying the exactly same values.
ANy help is apprecaited.


randyj262004 (BOB member since 2004-04-09)

Will this work?

SUM
( CASE 
   WHEN DETAIL_V.ORDER LINES = 1 
   THEN 1
   ELSE 0
   END )  

I suppose it depends on the Group By that’s generated.

Or maybe it’s just that you need the ELSE?

COUNT
( CASE 
   WHEN DETAIL_V.ORDER LINES = 1 
   THEN DETAIL_V.ORDER LINES 
   ELSE 0
   END )  

Or maybe it’s because you’re using both the = and > in the same report?


KSG :us: (BOB member since 2002-07-17)

Thanks for the input
I agve the statement below
In Select of OBJECT1

count(DETAIL_V.ORDER LINES )"orders=1",
COUNT 
( 
CASE 
   WHEN DETAIL_V.ORDER LINES = 1 
   THEN DETAIL_V.ORDER LINES 
END 
)

In Select of OBJECT2

count(DETAIL_V.ORDER LINES )"orders>1"
COUNT 
( 
CASE 
   WHEN DETAIL_V.ORDER LINES > 1 
   THEN DETAIL_V.ORDER LINES 
END 
)

I just have one GROUP BY on TIME

With these conditiond it gives the output for object1 and object2 but the Time is displayed as all 12:00:00 'S

If i’m doing wrong then what is the correct approach?
i definitly need both the objects in the same report along with the time.
Can you suggest anything?


randyj262004 (BOB member since 2004-04-09)

Randy,

Where does the time come into this CASE statement? :crazy_face:

If you have a separate question, please can you ask all of it.
If you are trying to return the time of an order, may I suggest you double check how the date/time is stored in the database.
What DBMS are you reporting from?

Regards,
Mark

The database is Oracle
I should do the GOUP BY on TIME and count the ORDER LINES which are =1 and >1

but when i use the follwoing in ‘OBJECT=1’:

COUNT(CASE WHEN DETAIL_V.ORDER LINES =1
THEN DETAIL_V.ORDER LINES ELSE 0 END)

the follwoing in ‘OBJECT>1’:

COUNT(CASE WHEN DETAIL_V.ORDER LINES >1
THEN DETAIL_V.ORDER LINES ELSE 0 END)

When i pull the TIME,OBJECT=1,OBJECT>1 on the report then
the count is same for both the objects and where as time is grouping by and hour.
Based on the time (hour) both the objects should be counted.
I’m trying a lot to solve this and posting the questions in here ,thinking that i may get a right solution but till now i couldnt solve this.

The ORDER LINES and TIME both are from a DETAILS view which are used in the designer.
The TIME format is
to_char(DETAIL_V.TIME, ‘HH’) || ':00 ’ || to_char(DETAIL_V.TIME, ‘AM’).

Time is a Dimension Objects and ORDER LINES is the measure object.

Please help me ,if anyone understand the problem.
Thanks


randyj262004 (BOB member since 2004-04-09)

hi andres

how to use in DB2?

thank u


bo_s_user (BOB member since 2004-05-19)

Hi all,
And to say more about this condition,the sql looks like taking the,
ORDER LINES = 1 and ORDER LINES>1
I understood that the same ORDER LINES cannot have both of these conditions in the same query so what id did is:
I gave the ‘OR’ condition but still only one condition is returning and the other is duplicate of first object.

Then i worked with UNION ,this query never returned any results for hours…
Please suggest me by taking a litle of your precious time.
I would really apprecaite that.
Thanks


randyj262004 (BOB member since 2004-04-09)

What happens when you use SUM instead of COUNT?


KSG :us: (BOB member since 2002-07-17)

will there be any difference in output if i use sum?
Thanks


randyj262004 (BOB member since 2004-04-09)

Even though i give the SUM,
I cant include both objects in same query.
AS again it is going to take
SUM(ORDER LINES=1) AND SUM(ORDER LINES>1)
and i cant take the UNION also as they are not conditions rather they are object

Please suggest me.
Thanks


randyj262004 (BOB member since 2004-04-09)

Hi,
Thanks for replying and helping me out.Finally it did work with the
In objects 1

SUM (CASE WHEN  DETAIL_V.ORDER LINES=1   THEN DETAIL_V.ORDER LINES ELSE 0  END)

In objects 2

SUM (CASE WHEN  DETAIL_V.ORDER LINES>1   THEN DETAIL_V.ORDER LINES ELSE 0  END)

These i created in designer and pulled both of them along with TIME onto the report ,it displays the data but now when i want
SUM(otbject1 and object2) and created a variable on the reporter then
its displaying the erroneous data like all as 12:00:00 am…in the SUM column.
Can anyone tell me as why it is behaving like that?


randyj262004 (BOB member since 2004-04-09)