Q on joining tables with nullable fields

Question.
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data

I hope this makes sense, I didn't want to post the entire actual script
as it is about 150 lines long.

Thanks in advance.mike wrote:

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.
>
The first field always has data in it, but the 2nd field is sometimes
null.
>
So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.
>
So if I have
>
select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2
>
with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data
>
what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
>
>
I would expect
record2: data,null,data,null,data

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.|||Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:
>

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data

>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.

mike wrote:

Quote:

Originally Posted by

Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.
>
Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.
>
The first field always has data in it, but the 2nd field is sometimes
null.
>
So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.
>
So if I have
>
select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2
>
with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data
>
what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
>
>
I would expect
record2: data,null,data,null,data

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||Ok I finally got it. I still don't know why that didn't work. But
here is what I did instead and it worked.
On the join I changed it to the following.

On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

mike wrote:

Quote:

Originally Posted by

Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.
>
mike wrote:

Quote:

Originally Posted by

Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:
>
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null


I would expect
record2: data,null,data,null,data
>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||mike wrote:

Quote:

Originally Posted by

Ok I finally got it. I still don't know why that didn't work.

Nulls are not considered equal to anything, not even other nulls.

http://en.wikipedia.org/wiki/Null_(SQL)

Quote:

Originally Posted by

But here is what I did instead and it worked.
On the join I changed it to the following.
>
On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

Equivalent and shorter:

on t1.field1 = t2.field1
and coalesce(t1.field2,'') = coalesce(t2.field2,'')

coalesce() is a function that takes one or more argument and returns the
first non-null value among them, or null if they're all null.

Putting SqlDataSource code in code-behind

Hi,

I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this?

Thanks geniuses.

This link:

http://aspnet.4guysfromrolla.com/articles/022206-1.aspx

|||

Hi ahTan,

You may need to use a SqlDataAdapter to fill the result set into a DataSet. Here is an example.

SqlConnection cnn = new SqlConnection("your connection string here");
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Table1", cnn);
DataSet ds = new DataSet();
sda.Fill(ds);

sql

Putting commas between select statement values

Hello,

This may be a strange request, but I am going to ask about it anyways.

Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:

NUMBERS
1
2
3
4

How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:

1,2,3,4

Instead of:

1
2
3
4

Any ideas?

ThanksThe numbers will still be as o column... but try:

SELECT CAST(numbers as varchar)+',' from TEST

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>sql

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this :(
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this :(
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
.......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this? Any suggestions?
David
Hi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this? Any suggestions?
> David
>

put results from sp to another sp

Hi,
Can anyone tell me how to use the result of a stored procedure to be used in
another stored procedure?
The first sp is a simple select statement with 2 columns, the second one
needs those two columns to insert in a temptable.
ThnxCreate a temporary table in the second sp and use INSERT ... EXEC
Example:
use northwind
go
create procedure proc1
@.d datetime
as
set nocount on
select orderid, orderdate
from dbo.orders
where orderdate >= convert(char(8), @.d, 112) and orderdate < dateadd(day, 1,
convert(char(8), @.d, 112))
return @.@.error
go
create procedure proc2
@.d datetime
as
set nocount on
create table #t (
orderid int,
orderdate datetime
)
insert into #t
exec proc1 @.d
select * from #t
drop table #t
go
exec proc2 '19980226'
go
drop procedure proc1, proc2
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Jason" wrote:

> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>
>|||OUTPUT parameters ?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:eoGJzrIMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
> in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>

purchase order query: very funny and challenge ^_^

i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004

i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number

SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"

i checked my database, last record is LP-0545381-0300

in my debuging process, surprisingly found that selected record is LP-0545381-301 !

any one hav any suggestion? ^_^

I fail to see the humor here, but that's irrelevant. It's difficult to know the query behavior without knowing what GetYearCode() is supposed to return and how it relates to the data in your table. Also, why does your last record (LP-0545381-301) have only 3 digits at the end when your format suggests that it should contain 4 digits? Finally, what were you expecting the result to be?|||

database never had that record, but selected out, isnt it very funny...anyway, GetYearCode() will return the middle code as0545381, i also suprisingly why return 3 digit?!unreasonable, isnt it

what i expected result isLP-0545381-0300

pulling a pdf out of a blob and displaying it in a browser window

I need to pull a PDF file out of a blob that I have uploaded it into. We
need it available on the web.
I can't think of how to utter a statement that will allow acrobat reader to
know that it needs to fire and open what is in the blob.
Ack.In your client code, create a memory stream, or byte arrray, and just write
the contents of that stream or byte array out to the web client using
Response.write...
To tell client browser to load Acrobat reader, just set the
Response.ContentType...
Don't know what you web server programming language is.. but in C#, it
would be:
Response.ContentType = "application/pdf";
"Linda Lalewicz" wrote:

> I need to pull a PDF file out of a blob that I have uploaded it into. We
> need it available on the web.
> I can't think of how to utter a statement that will allow acrobat reader t
o
> know that it needs to fire and open what is in the blob.
> Ack.|||Just like images, except you set the ContentType to application/pdf instead
of image/gif.
http://support.microsoft.com/kb/173308
However, just like images, you may consider storing them in the filesystem.
http://www.aspfaq.com/2149
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Linda Lalewicz" <LindaLalewicz@.discussions.microsoft.com> wrote in message
news:17F6237B-C408-46CE-9194-0DB528D4B255@.microsoft.com...
>I need to pull a PDF file out of a blob that I have uploaded it into. We
> need it available on the web.
> I can't think of how to utter a statement that will allow acrobat reader
> to
> know that it needs to fire and open what is in the blob.