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'
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'
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';
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.
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