-2

I want to write the case expression for this condition in View Indicate "Y" in the report if Livestream (all) is selected and display all the individual platforms under livestream (App, Online,O&O,etc). If Livestream (all) is not selected, but individual platforms are selected, indicate the platform selected in the report. Indicate “No” if individual platforms are not selected or Livestream (All) is not selected.

how can I write this case expression in my view. The case expression should return multiple rows. Is there any other way to do this instead of using a case like a Function or join or something considering performance

Tree_id DESCRIPTION PRENT_NODE_ID
122 Livestream 0
122 Livestream BDU App 323
122 Livestream BDU Online 323
122 Livestream O&O App 323
122 Livestream O&O Online 323

Table data and same should be displayed in the view. If Livestream is Present all should come, If not then the individual platform should come whichever selected, if nothing is selected nothing should come, The column should only contain the description and not the ids. This will be one column in the report all the others.

Expected output if Livestream is Y

DESCRIPTION
Livestream
Livestream BDU App
Livestream BDU Online
Livestream O&O App
Livestream O&O Online

if any two of the subset say bdu app and bdu online is seleted then it should be like this

DESCRIPTION
Livestream BDU App
Livestream BDU Online
ARH
  • 25
  • 3
  • Side note: `CASE` **expression**, not statement. SQL (i.e. not a procedural extension) knows no flow control statements at all. – sticky bit Jan 20 '22 at 12:58
  • Hi @stickybit can you please elaborate I am quite not understanding what you said – ARH Jan 20 '22 at 13:02
  • Show us some sample table data and the expected result - all as formatted text (not images.) [mcve] – jarlh Jan 20 '22 at 13:02
  • @AishwaryaHalkhede: There are no `CASE` statements in SQL. There only are `CASE` expressions. – sticky bit Jan 20 '22 at 13:03
  • Hi @jarlh I have given the data in tabular format can you please see if you can help, Kinda urgent – ARH Jan 20 '22 at 13:30

1 Answers1

0

A simple option - if it is OK with you - is to use the INSTR function. Something like this:

Sample data:

SQL> select * from test;

   TREE_ID DESCRIPTION           PRENT_NODE_ID
---------- --------------------- -------------
       122 Livestream                        0
       122 Livestream BDU App              323
       122 Livestream BDU Online           323
       122 Livestream O&O App              323
       122 Livestream O&O Online           323

Query:

SQL> select description
  2  from test
  3  where instr(description, '&par_desc') > 0;
Enter value for par_desc: Livestream

DESCRIPTION
---------------------
Livestream
Livestream BDU App
Livestream BDU Online
Livestream O&O App
Livestream O&O Online

SQL> /
Enter value for par_desc: Livestream BDU

DESCRIPTION
---------------------
Livestream BDU App
Livestream BDU Online

SQL> /
Enter value for par_desc: Online

DESCRIPTION
---------------------
Livestream BDU Online
Livestream O&O Online

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Hi @Littlefoot. Yes your query does work But I am writing it for production and I cannot mention the values explicitly I have to write it in condition format and no explicit values to be supplied. This is going to be of the column of a view. Need to avoid the multiple row error from single query – ARH Jan 21 '22 at 07:23
  • Which values are mentioned *explicitly*? PAR_DESC is a *parameter* and you pass its value, somehow. In e.g. Oracle Apex, you'd use `where instr(description, :P1_DESC) > 0` where P1_DESC is an item on the page. – Littlefoot Jan 21 '22 at 07:32