I need to know how to return a default row if no rows exist in a table. What would be the best way to do this? I'm only returning a single column from this particular table to get its value.
Edit: This would be SQL Server.
This question is related to
sql
sql-server
How about this:
SELECT DEF.Rate, ACTUAL.Rate, COALESCE(ACTUAL.Rate, DEF.Rate) AS UseThisRate
FROM
(SELECT 0) DEF (Rate) -- This is your default rate
LEFT JOIN (
select rate
from d_payment_index
--WHERE 1=2 -- Uncomment this line to simulate a missing value
--...HERE IF YOUR ACTUAL WHERE CLAUSE. Removed for testing purposes...
--where fy = 2007
-- and payment_year = 2008
-- and program_id = 18
) ACTUAL (Rate) ON 1=1
Results
Valid Rate Exists
Rate Rate UseThisRate
----------- ----------- -----------
0 1 1
Default Rate Used
Rate Rate UseThisRate
----------- ----------- -----------
0 NULL 0
Test DDL
CREATE TABLE d_payment_index (rate int NOT NULL)
INSERT INTO d_payment_index VALUES (1)
If your base query is expected to return only one row, then you could use this trick:
select NVL( MIN(rate), 0 ) AS rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
(Oracle code, not sure if NVL is the right function for SQL Server.)
Do you want to return a full row? Does the default row need to have default values or can it be an empty row? Do you want the default row to have the same column structure as the table in question?
Depending on your requirements, you might do something like this:
1) run the query and put results in a temp table (or table variable) 2) check to see if the temp table has results 3) if not, return an empty row by performing a select statement similar to this (in SQL Server):
select '' as columnA, '' as columnB, '' as columnC from #tempTable
Where columnA, columnB and columnC are your actual column names.
One table scan method using a left join from defaults to actuals:
CREATE TABLE [stackoverflow-285666] (k int, val varchar(255))
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-1')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-2')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-3')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-1')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-2')
DECLARE @k AS int
SET @k = 0
WHILE @k < 3
BEGIN
SELECT @k AS k
,COALESCE(ActualValue, DefaultValue) AS [Value]
FROM (
SELECT 'DefaultValue' AS DefaultValue
) AS Defaults
LEFT JOIN (
SELECT val AS ActualValue
FROM [stackoverflow-285666]
WHERE k = @k
) AS [Values]
ON 1 = 1
SET @k = @k + 1
END
DROP TABLE [stackoverflow-285666]
Gives output:
k Value
----------- ------------
0 DefaultValue
k Value
----------- ------------
1 1-1
1 1-2
1 1-3
k Value
----------- ------------
2 2-1
2 2-2
How about this:
SELECT DEF.Rate, ACTUAL.Rate, COALESCE(ACTUAL.Rate, DEF.Rate) AS UseThisRate
FROM
(SELECT 0) DEF (Rate) -- This is your default rate
LEFT JOIN (
select rate
from d_payment_index
--WHERE 1=2 -- Uncomment this line to simulate a missing value
--...HERE IF YOUR ACTUAL WHERE CLAUSE. Removed for testing purposes...
--where fy = 2007
-- and payment_year = 2008
-- and program_id = 18
) ACTUAL (Rate) ON 1=1
Results
Valid Rate Exists
Rate Rate UseThisRate
----------- ----------- -----------
0 1 1
Default Rate Used
Rate Rate UseThisRate
----------- ----------- -----------
0 NULL 0
Test DDL
CREATE TABLE d_payment_index (rate int NOT NULL)
INSERT INTO d_payment_index VALUES (1)
Assuming there is a table config with unique index on config_code column:
CONFIG_CODE PARAM1 PARAM2
--------------- -------- --------
default_config def 000
config1 abc 123
config2 def 456
This query returns line for config1 values, because it exists in the table:
SELECT *
FROM (SELECT *
FROM config
WHERE config_code = 'config1'
OR config_code = 'default_config'
ORDER BY CASE config_code WHEN 'default_config' THEN 999 ELSE 1 END)
WHERE rownum = 1;
CONFIG_CODE PARAM1 PARAM2
--------------- -------- --------
config1 abc 123
This one returns default record as config3 doesn't exist in the table:
SELECT *
FROM (SELECT *
FROM config
WHERE config_code = 'config3'
OR config_code = 'default_config'
ORDER BY CASE config_code WHEN 'default_config' THEN 999 ELSE 1 END)
WHERE rownum = 1;
CONFIG_CODE PARAM1 PARAM2
--------------- -------- --------
default_config def 000
In comparison with other solutions this one queries table config only once.
One table scan method using a left join from defaults to actuals:
CREATE TABLE [stackoverflow-285666] (k int, val varchar(255))
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-1')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-2')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-3')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-1')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-2')
DECLARE @k AS int
SET @k = 0
WHILE @k < 3
BEGIN
SELECT @k AS k
,COALESCE(ActualValue, DefaultValue) AS [Value]
FROM (
SELECT 'DefaultValue' AS DefaultValue
) AS Defaults
LEFT JOIN (
SELECT val AS ActualValue
FROM [stackoverflow-285666]
WHERE k = @k
) AS [Values]
ON 1 = 1
SET @k = @k + 1
END
DROP TABLE [stackoverflow-285666]
Gives output:
k Value
----------- ------------
0 DefaultValue
k Value
----------- ------------
1 1-1
1 1-2
1 1-3
k Value
----------- ------------
2 2-1
2 2-2
*SQL solution
Suppose you have a review table which has primary key "id".
SELECT * FROM review WHERE id = 1555
UNION ALL
SELECT * FROM review WHERE NOT EXISTS ( SELECT * FROM review where id = 1555 ) AND id = 1
if table doesn't have review with 1555 id then this query will provide a review of id 1.
If your base query is expected to return only one row, then you could use this trick:
select NVL( MIN(rate), 0 ) AS rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
(Oracle code, not sure if NVL is the right function for SQL Server.)
Do you want to return a full row? Does the default row need to have default values or can it be an empty row? Do you want the default row to have the same column structure as the table in question?
Depending on your requirements, you might do something like this:
1) run the query and put results in a temp table (or table variable) 2) check to see if the temp table has results 3) if not, return an empty row by performing a select statement similar to this (in SQL Server):
select '' as columnA, '' as columnB, '' as columnC from #tempTable
Where columnA, columnB and columnC are your actual column names.
I figured it out, and it should also work for other systems too. It's a variation of WW's answer.
select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
union
select 0 as rate
from d_payment_index
where not exists( select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18 )
*SQL solution
Suppose you have a review table which has primary key "id".
SELECT * FROM review WHERE id = 1555
UNION ALL
SELECT * FROM review WHERE NOT EXISTS ( SELECT * FROM review where id = 1555 ) AND id = 1
if table doesn't have review with 1555 id then this query will provide a review of id 1.
If your base query is expected to return only one row, then you could use this trick:
select NVL( MIN(rate), 0 ) AS rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
(Oracle code, not sure if NVL is the right function for SQL Server.)
This snippet uses Common Table Expressions to reduce redundant code and to improve readability. It is a variation of John Baughman's answer.
The syntax is for SQL Server.
WITH products AS (
SELECT prod_name,
price
FROM Products_Table
WHERE prod_name LIKE '%foo%'
),
defaults AS (
SELECT '-' AS prod_name,
0 AS price
)
SELECT * FROM products
UNION ALL
SELECT * FROM defaults
WHERE NOT EXISTS ( SELECT * FROM products );
Do you want to return a full row? Does the default row need to have default values or can it be an empty row? Do you want the default row to have the same column structure as the table in question?
Depending on your requirements, you might do something like this:
1) run the query and put results in a temp table (or table variable) 2) check to see if the temp table has results 3) if not, return an empty row by performing a select statement similar to this (in SQL Server):
select '' as columnA, '' as columnB, '' as columnC from #tempTable
Where columnA, columnB and columnC are your actual column names.
This would be eliminate the select query from running twice and be better for performance:
Declare @rate int
select
@rate = rate
from
d_payment_index
where
fy = 2007
and payment_year = 2008
and program_id = 18
IF @@rowcount = 0
Set @rate = 0
Select @rate 'rate'
Insert your default values into a table variable, then update this tableVar's single row with a match from your actual table. If a row is found, tableVar will be updated; if not, the default value remains. Return the table variable.
---=== The table & its data
CREATE TABLE dbo.Rates (
PkId int,
name varchar(10),
rate decimal(10,2)
)
INSERT INTO dbo.Rates(PkId, name, rate) VALUES (1, 'Schedule 1', 0.1)
INSERT INTO dbo.Rates(PkId, name, rate) VALUES (2, 'Schedule 2', 0.2)
Here's the solution:
---=== The solution
CREATE PROCEDURE dbo.GetRate
@PkId int
AS
BEGIN
DECLARE @tempTable TABLE (
PkId int,
name varchar(10),
rate decimal(10,2)
)
--- [1] Insert default values into @tempTable. PkId=0 is dummy value
INSERT INTO @tempTable(PkId, name, rate) VALUES (0, 'DEFAULT', 0.00)
--- [2] Update the single row in @tempTable with the actual value.
--- This only happens if a match is found
UPDATE @tempTable
SET t.PkId=x.PkId, t.name=x.name, t.rate = x.rate
FROM @tempTable t INNER JOIN dbo.Rates x
ON t.PkId = 0
WHERE x.PkId = @PkId
SELECT * FROM @tempTable
END
Test the code:
EXEC dbo.GetRate @PkId=1 --- returns values for PkId=1
EXEC dbo.GetRate @PkId=12314 --- returns default values
This snippet uses Common Table Expressions to reduce redundant code and to improve readability. It is a variation of John Baughman's answer.
The syntax is for SQL Server.
WITH products AS (
SELECT prod_name,
price
FROM Products_Table
WHERE prod_name LIKE '%foo%'
),
defaults AS (
SELECT '-' AS prod_name,
0 AS price
)
SELECT * FROM products
UNION ALL
SELECT * FROM defaults
WHERE NOT EXISTS ( SELECT * FROM products );
I figured it out, and it should also work for other systems too. It's a variation of WW's answer.
select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
union
select 0 as rate
from d_payment_index
where not exists( select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18 )
This would be eliminate the select query from running twice and be better for performance:
Declare @rate int
select
@rate = rate
from
d_payment_index
where
fy = 2007
and payment_year = 2008
and program_id = 18
IF @@rowcount = 0
Set @rate = 0
Select @rate 'rate'
One table scan method using a left join from defaults to actuals:
CREATE TABLE [stackoverflow-285666] (k int, val varchar(255))
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-1')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-2')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-3')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-1')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-2')
DECLARE @k AS int
SET @k = 0
WHILE @k < 3
BEGIN
SELECT @k AS k
,COALESCE(ActualValue, DefaultValue) AS [Value]
FROM (
SELECT 'DefaultValue' AS DefaultValue
) AS Defaults
LEFT JOIN (
SELECT val AS ActualValue
FROM [stackoverflow-285666]
WHERE k = @k
) AS [Values]
ON 1 = 1
SET @k = @k + 1
END
DROP TABLE [stackoverflow-285666]
Gives output:
k Value
----------- ------------
0 DefaultValue
k Value
----------- ------------
1 1-1
1 1-2
1 1-3
k Value
----------- ------------
2 2-1
2 2-2
If your base query is expected to return only one row, then you could use this trick:
select NVL( MIN(rate), 0 ) AS rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
(Oracle code, not sure if NVL is the right function for SQL Server.)
I figured it out, and it should also work for other systems too. It's a variation of WW's answer.
select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
union
select 0 as rate
from d_payment_index
where not exists( select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18 )
Do you want to return a full row? Does the default row need to have default values or can it be an empty row? Do you want the default row to have the same column structure as the table in question?
Depending on your requirements, you might do something like this:
1) run the query and put results in a temp table (or table variable) 2) check to see if the temp table has results 3) if not, return an empty row by performing a select statement similar to this (in SQL Server):
select '' as columnA, '' as columnB, '' as columnC from #tempTable
Where columnA, columnB and columnC are your actual column names.
Insert your default values into a table variable, then update this tableVar's single row with a match from your actual table. If a row is found, tableVar will be updated; if not, the default value remains. Return the table variable.
---=== The table & its data
CREATE TABLE dbo.Rates (
PkId int,
name varchar(10),
rate decimal(10,2)
)
INSERT INTO dbo.Rates(PkId, name, rate) VALUES (1, 'Schedule 1', 0.1)
INSERT INTO dbo.Rates(PkId, name, rate) VALUES (2, 'Schedule 2', 0.2)
Here's the solution:
---=== The solution
CREATE PROCEDURE dbo.GetRate
@PkId int
AS
BEGIN
DECLARE @tempTable TABLE (
PkId int,
name varchar(10),
rate decimal(10,2)
)
--- [1] Insert default values into @tempTable. PkId=0 is dummy value
INSERT INTO @tempTable(PkId, name, rate) VALUES (0, 'DEFAULT', 0.00)
--- [2] Update the single row in @tempTable with the actual value.
--- This only happens if a match is found
UPDATE @tempTable
SET t.PkId=x.PkId, t.name=x.name, t.rate = x.rate
FROM @tempTable t INNER JOIN dbo.Rates x
ON t.PkId = 0
WHERE x.PkId = @PkId
SELECT * FROM @tempTable
END
Test the code:
EXEC dbo.GetRate @PkId=1 --- returns values for PkId=1
EXEC dbo.GetRate @PkId=12314 --- returns default values
Assuming there is a table config with unique index on config_code column:
CONFIG_CODE PARAM1 PARAM2
--------------- -------- --------
default_config def 000
config1 abc 123
config2 def 456
This query returns line for config1 values, because it exists in the table:
SELECT *
FROM (SELECT *
FROM config
WHERE config_code = 'config1'
OR config_code = 'default_config'
ORDER BY CASE config_code WHEN 'default_config' THEN 999 ELSE 1 END)
WHERE rownum = 1;
CONFIG_CODE PARAM1 PARAM2
--------------- -------- --------
config1 abc 123
This one returns default record as config3 doesn't exist in the table:
SELECT *
FROM (SELECT *
FROM config
WHERE config_code = 'config3'
OR config_code = 'default_config'
ORDER BY CASE config_code WHEN 'default_config' THEN 999 ELSE 1 END)
WHERE rownum = 1;
CONFIG_CODE PARAM1 PARAM2
--------------- -------- --------
default_config def 000
In comparison with other solutions this one queries table config only once.
One table scan method using a left join from defaults to actuals:
CREATE TABLE [stackoverflow-285666] (k int, val varchar(255))
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-1')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-2')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-3')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-1')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-2')
DECLARE @k AS int
SET @k = 0
WHILE @k < 3
BEGIN
SELECT @k AS k
,COALESCE(ActualValue, DefaultValue) AS [Value]
FROM (
SELECT 'DefaultValue' AS DefaultValue
) AS Defaults
LEFT JOIN (
SELECT val AS ActualValue
FROM [stackoverflow-285666]
WHERE k = @k
) AS [Values]
ON 1 = 1
SET @k = @k + 1
END
DROP TABLE [stackoverflow-285666]
Gives output:
k Value
----------- ------------
0 DefaultValue
k Value
----------- ------------
1 1-1
1 1-2
1 1-3
k Value
----------- ------------
2 2-1
2 2-2
This would be eliminate the select query from running twice and be better for performance:
Declare @rate int
select
@rate = rate
from
d_payment_index
where
fy = 2007
and payment_year = 2008
and program_id = 18
IF @@rowcount = 0
Set @rate = 0
Select @rate 'rate'
Source: Stackoverflow.com