June 7, 2018 at 1:35 pm
Hi, below the sample structure,
Declare @sample table(id int, name varchar, datetime);
select * from @sample
this will return empty row. but i need row with null values like below
Expected output :
select null as id, null as name, null as createdDate
how do i achieve this. Any example please
June 7, 2018 at 2:32 pm
Is this what you are after? SELECT ID, name, createdDate
FROM @sample
UNION ALL
SELECT NULL, NULL, NULL
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 7, 2018 at 2:43 pm
KGJ-Dev - Thursday, June 7, 2018 1:35 PMHi, below the sample structure,
Declare @sample table(id int, name varchar, datetime);select * from @sample
this will return empty row. but i need row with null values like belowExpected output :
select null as id, null as name, null as createdDate
how do i achieve this. Any example please
There isn't a row so there are no values. If you need a row with null values, insert a row with null values - insert values (NULL, NULL, NULL). Or I suppose you could do a union with just SELECT NULL, NULL, NULL union....
But if you insert null values the count is 1. If you have just declared the table variable the count is 0. So the two are different. I am wondering if there just isn't a logic error somewhere that leads to needing an empty table variable stuffed with nulls. Logic for the nulls seems like it would be the same if the count of rows was 0. Unless you really want a row of NULLs in a table for some reason.
Sue
June 8, 2018 at 6:28 am
Sue_H - Thursday, June 7, 2018 2:43 PMKGJ-Dev - Thursday, June 7, 2018 1:35 PMHi, below the sample structure,
Declare @sample table(id int, name varchar, datetime);select * from @sample
this will return empty row. but i need row with null values like belowExpected output :
select null as id, null as name, null as createdDate
how do i achieve this. Any example pleaseThere isn't a row so there are no values. If you need a row with null values, insert a row with null values - insert values (NULL, NULL, NULL). Or I suppose you could do a union with just SELECT NULL, NULL, NULL union....
But if you insert null values the count is 1. If you have just declared the table variable the count is 0. So the two are different. I am wondering if there just isn't a logic error somewhere that leads to needing an empty table variable stuffed with nulls. Logic for the nulls seems like it would be the same if the count of rows was 0. Unless you really want a row of NULLs in a table for some reason.
Sue
And I'm thinking there's a reason for the desire for a NULL result that could allow us to better assist overall if we knew enough more about the WHY....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2018 at 11:41 am
This would add the nulls row only if there's not a row in the table.
SELECT id,
name,
createdDate
FROM @sample
RIGHT JOIN (SELECT 1 a)x ON 1=1
One more thing, I hope it was just a mistake for the sample data but you should always define the length of your strings.
June 13, 2018 at 6:35 pm
thank you luis. new learning for me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply