tags:

views:

80

answers:

3

Hi,

I have a sql statement like below. How can I add a single row(code = 0, desc = 1) to result of this sql statement without using union keyword? thanks.

select code, desc
from material 
where material.ExpireDate ='2010/07/23'
+3  A: 

You can always create a view for your table which itself uses UNION keyword

CREATE VIEW material_view AS SELECT code, desc, ExpireDate FROM material UNION SELECT '0', '1', NULL;
SELECT code, desc FROM material_view WHERE ExpireDate = '2010/07/23' OR code = '0';
too
I don' t want to use views.
mkus
No need to `-1`-ing the answer... You did not mention view-avoidance in your question.
pascal
So add a new row to materials with empty date (and other fields) and code '0' and use:SELECT code, desc FROM material WHERE ExpireDate = '2010/07/23' OR code = '0';
too
@mkus - maybe put that in the question?
Neil Moss
ok. You are right pascal.
mkus
@too - Thanks for your qucik response but I can not use your solution.
mkus
A: 

Since you don't want to use either a union or a view, I'd suggest adding a dummy row to the material table (with code = 0, desc = 1, and ExpireDate something that would never normally be selected - eg. 01 January 1900) - then use a query like the following:

select code, desc
from material 
where material.ExpireDate ='2010/07/23' or 
    material.ExpireDate ='1900/01/01' 

Normally, a Union would be my preferred option.

Mark Bannister
+1  A: 
 WITH material   AS
 (
 SELECT * 
 FROM 
      (VALUES (2, 'x', '2010/07/23'), 
              (3, 'y', '2009/01/01'), 
              (4, 'z', '2010/07/23')) vals (code, [desc], ExpireDate)
 )

SELECT 
     COALESCE(m.code,x.code) AS code, 
     COALESCE(m.[desc],x.[desc]) AS [desc]
FROM material m
FULL OUTER JOIN (SELECT 0 AS code, '1' AS [desc] ) x ON 1=0
WHERE m.code IS NULL OR m.ExpireDate ='2010/07/23'

Gives

code        desc
----------- ----
2           x
4           z
0           1
Martin Smith
You could replace the `UNION ALL` with row constructors e.g. `..(VALUES (2, 'x', '2010/07/23'), (3, 'y', '2010/07/23'), (4, 'z', '2010/07/23')) AS material (code, [desc], ExpireDate)...`
onedaywhen
@onedaywhen - Yep I've done that. Good suggestion thanks!
Martin Smith