February 3, 2009 at 7:52 am
Hi All
I am not sure if this can be done, i am using a select statement from a table with a where clause, if there are no records on the table then it must return a default value, if there are then the value from the record.
Create table #Select (id int,Name varchar(10))
Insert into #Select values(1,'Test')
Insert into #Select values(2,'Test1')
Insert into #Select values(3,'Test2')
Insert into #Select values(4,'Test3')
If i choose select * from #Service where id=5 , it must return some constant value if there is no record on the table, (say the default value is 10,Notavail) else return the value .
Can this be done, i have used isnull, nullif and colase, case on select, nothing worked, 🙂
Thanks in advance 😀
February 3, 2009 at 9:16 am
This works, but is probably not ideal:
Create table #Select (id int, Name varchar(10))
Insert into
#Select
values
(1, 'Test')
Insert into
#Select
values
(2, 'Test1')
Insert into
#Select
values
(3, 'Test2')
Insert into
#Select
values
(4, 'Test3')
select
*
from
#Select
where
id = 5
UNION ALL
SELECT
10,
'N/A'
WHERE
NOT EXISTS ( SELECT
1
FROM
#Select AS S
WHERE
id = 5 )
DROP TABLE #Select
I'd recommend loading the defaults in the UI if no rows are returned.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 9:20 am
This is similar to the above and still not ideal but if you have a nums table you could certainly join back to that and Coalesce. (for creation of nums go to
http://www.sqlmag.com/Blog/index.cfm?action=blogindex&DepartmentID=1016
select coalesce(s.ID, 10) as id, coalesce(s.name, 'NotAvailable') as Name
from nums n left join #Select s on n.N=s.id
where n=5
February 3, 2009 at 9:23 am
Thanks Jack
But i am looking to create a view, with this select statement, hope i cant do with this can i???:)
February 3, 2009 at 9:29 am
Mike's solution is probably going to perform better because you only hit the main table 1 time so I'd go with that, which, once you replace the temp table with a real table, will allow you to create a view. Here is how you'd create a view with my code:
Create table my_test (id int, Name varchar(10))
Insert into
my_test
values
(1, 'Test')
Insert into
my_test
values
(2, 'Test1')
Insert into
my_test
values
(3, 'Test2')
Insert into
my_test
values
(4, 'Test3')
GO
CREATE VIEW test
AS
select
*
from
my_test
where
id = 5
UNION ALL
SELECT
10,
'N/A'
WHERE
NOT EXISTS ( SELECT
1
FROM
my_test AS S
WHERE
id = 5 )
Go
SELECT * FROM test AS T
DROP TABLE my_test
Drop view test
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 9:40 am
Hi Mick
Thanks , i don't have a separate table, this is for a Java application, i have to return a default value from a view if there is no values on the table, As jack said i am a bit concerned about reading the table 2 times since this application is multi threaded and the table which i will be Querying across has got around a million numbers 😀
February 3, 2009 at 9:44 am
Creating an auxiliary table of numbers is something I find useful on most servers for one reason or another (Itzik Ben-Gan frequently has information that utlizes nums and as far as I know he was the first to come up with this)
This will create it for you (pulled from his post).
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY)
DECLARE @max-2 AS INT, @rc AS INT;
SET @max-2 = 1000000
SET @rc = 1
INSERT INTO dbo.Nums(n) VALUES(1);
BEGIN
INSERT INTO dbo.Nums(n) SELECT n + @rc FROM dbo.Nums;
END
INSERT INTO dbo.Nums(n)
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max-2
And the code that I have above will join back to it easily within a view or however you would like to do so in order to get the data you are looking for.
Again, not ideal but should suffice for the need you describe here.
February 3, 2009 at 10:15 am
I would be tempted to check the rowcount of records returned by the SELECT statement. For example:
Create table #Select (id int, Name varchar(10))
Insert into #Select values (1, 'Test')
Insert into #Select values(2, 'Test1')
Insert into #Select values(3, 'Test2')
Insert into #Select values(4, 'Test3')
GO
DECLARE @RowsReturn AS INT
DECLARE @Name AS VARCHAR(15)
SET @RowsReturn = 0
SET @Name = 'Not available'
select * from #Select WHERE id = 5
IF @@ROWCOUNT = 0
BEGIN
SELECT @RowsReturn, @Name
END
-- Clean up for additional testing
DROP TABLE #Select
Return for id = 5
(No column name)(No column name)
0 Not available
Return for id = 3
idName
3Test2
February 3, 2009 at 10:18 am
Hi Mick
This would have been ideal if i am dealing with numbers, the problem is that the table has a primary key of phone number and as you said you i have to create a table that contains phone number and if there are any numbers added to that table then that has to be inserted into the num table as well, hope i understand this right 🙂
February 3, 2009 at 10:27 am
Thanks Bit Bucket
This is great, but this is not working on a view, it gives null rows, rather than my default value 🙂
February 3, 2009 at 12:03 pm
CrazyMan (2/3/2009)
Hi MickThis would have been ideal if i am dealing with numbers, the problem is that the table has a primary key of phone number and as you said you i have to create a table that contains phone number and if there are any numbers added to that table then that has to be inserted into the num table as well, hope i understand this right 🙂
This changes the requirement fairly significantly though in theory it should work for phone numbers as well if you changed the nums table to use a bigint column and change the max to be much higher (much, much, much higher actually depending on the longest phone number possibility in your db).
CREATE TABLE dbo.Nums(n BIGINT NOT NULL PRIMARY KEY)
DECLARE @max-2 AS BIGINT, @rc AS INT;
SET @max-2 = 999999999999999
That said, I am not sure I would want my auxiliary table of numbers to be that big but it is doable.
February 3, 2009 at 12:35 pm
Here is another option that may work for you. It starts with using Union to add the default value to a result set, but only reads the primary table one time.
select id,Name
from
(
select *,Row_Number() OVER(Order By roworder) AS rownum
from
(
select *,0 as roworder
from #Select
where id = 5
union
select 0,'Notavail',1
) as a
) as b
where rownum = 1
The subquery 'a' adds the default value Notavail to the result set, so 'a' returns either just the default value row or the default row and a row from the phone number table. The roworder column is used for the outer subquery (note that it assumes that the phone number column has a unique constraint). The outer subquery 'b' then adds a row number to the result set. The outermost query then selects the record who's row number is 1, which will be the record from the phone number table, if one exists, and the default value record if no match is found on the phone number table.
February 3, 2009 at 12:55 pm
--CREATE VIEW viewname AS
SELECT
COALESCE(sel.id, defaults.id) AS Id,
COALESCE(sel.name, defaults.name) AS Name
FROM (
SELECT 10 AS id, 'NotAvail' AS Name
) AS defaults
LEFT OUTER JOIN #Select sel ON sel.id = 5
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply