CODEWITHSUNDEEP
sqloracle-database
Soumyadip Das
Soumyadip Das

Reputation: 1791

if count of a select sql is > 0 then union the result with another sql's result

I have a SQL as follows:

SELECT
  ''                   AS "something1",
  'a pre defined text' AS "something2",
  ''                   AS "something3"
FROM dual

UNION ALL

SELECT
  rownum AS "something1",
  resultset.*
FROM (
       SELECT
         t1.abcd AS "something2",
         t2.xyz  AS "something3"
       FROM table_1 t1, table_2 t2
       WHERE t1.mnp = t2.mnp
       ORDER BY "something2"
     ) resultset;

This SQL produce the result like:

something1      |      something2      |      something3
-------------------------------------------------------------
                 a pre defined text               
1                value of abcd from t1  value of xyz from t2
2                value of abcd from t1  value of xyz from t2

and so on...

The first line in the result comes from the select part of dual (line 1 to 5) and the remaining lines are comes from the subsql of the UNION clause (line 7 to 17).

Now the challenge is the result from the dual (line 1 to 5) should be present if any result found from the subsql (line 7 to 17).

For example if any result found from the subsql then the final output will be same as above, otherwise as follows:

something1      |      something2      |      something3
-------------------------------------------------------------

Is there any way to achieve it? I have tried some approaches but not succeeded.

Upvotes: 0

Views: 538

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30835

First of all, please fix all obvious problems in your code before posting:

  • you're using single quotes around column aliases - that's illegal and is rejected by the SQL compiler
  • don't use line numbers in SQL statements - they're making it unnecessary difficult to copy & run the statement
  • consider using ANSI style JOINs instead of the old Oracle syntax

Your problem per se can be solved by using the analytical function COUNT() to count the number of rows whose something1 is NOT NULL:

count(something1) over (partition by 1) as cnt

After computing this count, you can then apply a WHERE clause to filter out rows whose cnt equals 0. Your overall query (slightly re-written, with example data):

 with table_1 as
 (select 'abcd from t1' as abcd,
         1 as mnp
    from dual),
table_2 as
 (select 'xyz from t2' as xyz,
         1 as mnp
    from dual),
resultset as
 (select t1.abcd as something2,
         t2.xyz  as something3
    from table_1 t1
    join table_2 t2
      on t1.mnp = t2.mnp
   order by something2),
resultset_plus_dummy as
 (select null as something1,
         'a pre defined text' as something2,
         '' as something3
    from dual
  union all
  select rownum as something1,
         resultset.*
    from resultset),
resultset_with_cnt as
 (select r.*,
         count(something1) over(partition by 1) as cnt
    from resultset_plus_dummy r)
select *
  from resultset_with_cnt
 where cnt > 0

Upvotes: 0

Emil Moise
Emil Moise

Reputation: 393

You could try adding a condition on the standard hard-coded row to only be displayed if the second selects returns any row, like the example below:

select
  '' as 'something1',
  'a pre defined text' as 'something2',
  '' as 'something3'
from 
  dual
  where exists (select 1 from table_1 t1, table_2 t2 where t1.mnp = t2.mnp)
union all
select
  rownum as 'something1',
  resultset.*
from (
  select
    t1.abcd as 'something2',
    t2.xyz as 'something3'
  from 
    table_1 t1, table_2 t2
  where 
    t1.mnp = t2.mnp
  order by 
    'something2'
) resultset;

Upvotes: 1

Related Questions