1

I have a requirement where i have to fetch some rows based on a condition and apart from that 2 more rows should be there in output one will be * and other will be a blank row. I am using the approach of Union to club that 2 extra rows.
Query :

SELECT '' as PROMO_GRP_CD , '' as PROMO_GRP_DESC,  '' as PROMO_GRP_ALT_DESC
from PROMO_GROUP 
UNION  
SELECT '*' as PROMO_GRP_CD , 'All' as PROMO_GRP_DESC,  'Tous' as PROMO_GRP_ALT_DESC
from PROMO_GROUP 
UNION  
SELECT PROMO_GRP_CD,PROMO_GRP_DESC,PROMO_GRP_ALT_DESC from PROMO_GROUP 
where ACC_TYPE = '*' and ACC_SUB_TYPE = '*' and SUBMARKET = '*'

In above Query first 2 selects from Table PROMO_GROUP gives me a blank row and a row with * value and below that i have normal select to retrieve data from Table PROMO_GROUP based on condition. Is there any other optimum way through which i can achieve this ?

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Shantanu Tomar
  • 1,572
  • 7
  • 38
  • 64
  • "Other optimum way" as in more efficient? Or less ugly looking? Or what? The requirement seems to be something for the post-SQL part of the program - shouldn't that be dealt with outside of the SQL, more cleanly? (If we understand what the point is, you might get better suggestions.) – David Manheim Oct 09 '15 at 04:21
  • You do not need `from promo_group` if all columns are dummy values – mynawaz Oct 09 '15 at 04:22
  • @DavidManheim Sir, by saying "Other optimum way" i mean a more efficient way. – Shantanu Tomar Oct 09 '15 at 04:24
  • 1
    In fact you **must not** have a from clause. Including it will give you one dummy row for each row in PROMO_GROUP, or none if there are no rows. – cliffordheath Oct 09 '15 at 04:24
  • As noted in my comment, and by @mseifert below, appending to the array after the SQL query result is returned seems like the obvious fix. – David Manheim Oct 09 '15 at 04:25

3 Answers3

1

If you want to do it in query only, this is the best way. You should remove the from promo_group part from first two queries

But if you can, you should handle this in your code instead of your query

mynawaz
  • 1,599
  • 1
  • 9
  • 16
1

You need to create each dummy row using a SELECT clause that has no FROM clause (or in Oracle, from the special DUAL table, so it produces exactly one row), and UNION those two with the actual query. Like this:

SELECT '' as PROMO_GRP_CD , '' as PROMO_GRP_DESC,  '' as PROMO_GRP_ALT_DESC FROM DUAL
UNION  
SELECT '*' as PROMO_GRP_CD , 'All' as PROMO_GRP_DESC,  'Tous' as PROMO_GRP_ALT_DESC FROM DUAL
UNION  
SELECT PROMO_GRP_CD,PROMO_GRP_DESC,PROMO_GRP_ALT_DESC from PROMO_GROUP
WHERE ACC_TYPE = '*' and ACC_SUB_TYPE = '*' and SUBMARKET = '*'
cliffordheath
  • 2,536
  • 15
  • 16
  • I tried this but it gives an error saying that FROM is expected. Error : ORA-00923: FROM keyword not found where expected – Shantanu Tomar Oct 09 '15 at 04:36
  • Apparently ORACLE SQL does not support this type of query directly. Instead, it provides the dummy table DUAL, so you can SELECT... FROM DUAL. I'll edit the above. Further information here: http://stackoverflow.com/questions/1881853/select-without-a-from-clause-in-oracle – cliffordheath Oct 09 '15 at 04:39
0

You can append the returned result with your two records (adding two element to the array). The result will be the same and you won't have the overhead of the UNION.

Of course you didn't specify what language you are using to run the query through. I am presuming your language will allow this.

mseifert
  • 5,390
  • 9
  • 38
  • 100
  • Thanks for your reply. Can you please elaborate what exactly you mean by append. Can you please give an example ? – Shantanu Tomar Oct 09 '15 at 04:25
  • This answer is incorrect. The question was how to do it "using SQL Query", so to add two rows in your programming language is not the right answer. The right answer is to use UNION... but to use it correctly, without a FROM clause in the dummy SELECTs, not as in the question. – cliffordheath Oct 09 '15 at 04:27
  • @cliffordheath Thanks. Yes, i want to do it in SQL Query itself. How can i use it without a FROM Clause ? – Shantanu Tomar Oct 09 '15 at 04:29
  • @cliffordheath Fair enough. Sometimes we ask a question but don't always ask the right question because our experience isn't wide enough to know what the real question is. If the op were using php, for example, this answer would have been of value. – mseifert Oct 09 '15 at 04:34
  • @mseifert That's true, and it's worth pointing out that perhaps the wrong question has been asked (in a comment). However it's not an answer unless it answers the question :) – cliffordheath Oct 09 '15 at 04:37