Numbers less than zero

In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.

SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM dbo.t_Agents LEFT OUTER JOIN
dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID

Any ideas how i do this?

Cheers
Anthony Swift

CASE

WHEN (dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total) < THEN 0

ELSE dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total

END AS amount_outstanding_commission

|||Cheers :)

Number of records in table

Hi!
How I can calculate number of records in the table which has filtering?
E.g., DataSet "dsABC" return 1000 rows. After filtering table contains 300 rows.
Function:
= CountRows("dsABC") return 1000.
How I can get 300?

Alexey,

I'm not sure I completely understand your question. Do you want the number of rows to show up on the report? Is this correct?

|||Thanks. It's correct.
I found workaround - create expression in the table footer (e.g. texbox name "TableRows") :
= Count()
Then in the textbox on the report header area I create exppression:
=ReportItems!TableRows.Value
It's work!

Number format to return 0 when empty?

My client has several issues with the number formating in the reports.
They want to have a space between 1 000 and no decimals. First I created a
custom format, but today I noticed I could get the same result with N0.
Anyway, N0 format will not show empty numbers, it just leaves the cell
blank. But my client now wants to have a 0 instead of blank. Is it possible?
Kaisa M. LindahlTry using =IIF(<Your number field> Is Nothing, 0, <Your number field>) as
the value expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:e$Sm76V%23EHA.2076@.TK2MSFTNGP15.phx.gbl...
> My client has several issues with the number formating in the reports.
> They want to have a space between 1 000 and no decimals. First I created a
> custom format, but today I noticed I could get the same result with N0.
> Anyway, N0 format will not show empty numbers, it just leaves the cell
> blank. But my client now wants to have a 0 instead of blank. Is it
> possible?
> Kaisa M. Lindahl
>|||Yes, I've figured that one out.
Problem is, I'd have to do it on aprox. 20 cells in each report (> 10 so
far), all with different field names... If there are any other solutions,
I'd be very happy to hear it.
Kaisa M. Lindahl
"Fang Wang (MSFT)" <fangw@.microsoft.com> wrote in message
news:u907lUd#EHA.936@.TK2MSFTNGP12.phx.gbl...
> Try using =IIF(<Your number field> Is Nothing, 0, <Your number field>) as
> the value expression.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:e$Sm76V%23EHA.2076@.TK2MSFTNGP15.phx.gbl...
> > My client has several issues with the number formating in the reports.
> > They want to have a space between 1 000 and no decimals. First I created
a
> > custom format, but today I noticed I could get the same result with N0.
> > Anyway, N0 format will not show empty numbers, it just leaves the cell
> > blank. But my client now wants to have a 0 instead of blank. Is it
> > possible?
> >
> > Kaisa M. Lindahl
> >
> >
>|||I'm sorry, but there is no "easier" solution than using IIF for this
scenario.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:ORxJ1Jh%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Yes, I've figured that one out.
> Problem is, I'd have to do it on aprox. 20 cells in each report (> 10 so
> far), all with different field names... If there are any other solutions,
> I'd be very happy to hear it.
> Kaisa M. Lindahl
> "Fang Wang (MSFT)" <fangw@.microsoft.com> wrote in message
> news:u907lUd#EHA.936@.TK2MSFTNGP12.phx.gbl...
> > Try using =IIF(<Your number field> Is Nothing, 0, <Your number field>)
as
> > the value expression.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:e$Sm76V%23EHA.2076@.TK2MSFTNGP15.phx.gbl...
> > > My client has several issues with the number formating in the reports.
> > > They want to have a space between 1 000 and no decimals. First I
created
> a
> > > custom format, but today I noticed I could get the same result with
N0.
> > > Anyway, N0 format will not show empty numbers, it just leaves the cell
> > > blank. But my client now wants to have a 0 instead of blank. Is it
> > > possible?
> > >
> > > Kaisa M. Lindahl
> > >
> > >
> >
> >
>

Nulls in concatenated string

How do I prevent the following null 'Answer'?
This SQL will return a null string for 'Answer' whenever the count is null either for 'subquery-1' or for 'subquery-2', even though the other is not null. I need a string in either case. It would be better to have 'Answer' be "f1=, f2=25" than to have nothing. It doesn't seem right that both COUNT's have to be non-null to get anything other than null for the concatenated 'Answer'. There ought to be a way for COUNT to return 0 in some cases where it now returns null. I'd expect/prefer an 'Answer' of "f1=0, f2=25" or maybe even "f1=<null>, f2=25".
I expect I'd have the same problem with nulls even if I wasn't using subqueries.
SELECT 'f1='+CAST(COUNT(subquery-1) AS VARCHAR)+', f2='+CAST(COUNT(subquery-2) AS VARCHAR) AS Answer
FROM table1
WHERE condition=5
GROUP BY fieldXTheISNULL function should help you out:

SELECT 'f1='+ISNULL(CAST(COUNT(subquery-1) AS VARCHAR),'')+', f2='+ISNULL(CAST(COUNT(subquery-2) AS VARCHAR),'') AS Answer
FROM table1
WHERE condition=5
GROUP BY fieldX

null values return

Hi,
How do I join 2 tables with null value return?
Table1
Month, Year, Hours
Table2
Month, Year, Accounting_hrs
Here is my current statement.
Select Table1.month as MONTH, Table1.year as YEAR,
Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
from Table2,
(
select month, year, sum (hours) as sumhrs
from Table1
where year = 2005
group by month, year
)
where
(
Table1.year = Table2.year
and
Table1.month = Table2.month
)
order by Table1.Month
RESULT:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
I would like the results to look like this:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
Mar 2005 NULL
Apr 2005 NULL
May 2005 NULL
etc.
TIA!!
Joyce
*** Sent via Developersdex http://www.examnotes.net ***Probably the easiest method to solve this problem is the build a calendar ta
ble
that contains a row for every day from now until some arbitrary point in the
future. That makes this problem trival to solve. For example you might have
(granted without the specific DDL of your solution, this may not be perfect)
Create Table Calendar
(
SpecificDate DateTime
, Month TinyInt
, Year SmallInt
, W TinyInt
, Quarter TinyInt
)
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours) / Sum(T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Group By C.Month, C.Year
If you know that for every value in Table2 there exists a value in Table1, t
hen
you can adjust the query slightly like so:
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours / T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join (Table1 As T1
Join Table2 As T2
On T1.Month = T2.Month
And T1.Year = T2.Year)
On C.Month = T1.Month
And C.Year = T1.Year
Group By C.Month, C.Year
This solution of course presumes that Sum(Accounting_Hrs) will not be zero.
It
should also be noted that if either Sum(T1.Hours) or Sum(T2.Accounting_Hrs)
is
null that you will get Null for the result.
HTH,
Thomas|||Actually, as I think about you'll get bad results joining directly to the
calendar table. You would need to group your Calendar table first like so:
Select C.Month, C.Year
, Sum(T1.Hours) As TotalHours
, Sum(T1.Hours) / Sum(T2.Accounting_hrs) As AverageHours
From (
Select C1.Month, C1.Year
From Calendar As C1
Where C1.Year = 2005
Group By C1.Month, C1.Year
) As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Thomas
"Joyce L" <jsh_57@.hotmail.com> wrote in message
news:OdVy8sbSFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> How do I join 2 tables with null value return?
> Table1
> Month, Year, Hours
> Table2
> Month, Year, Accounting_hrs
> Here is my current statement.
> Select Table1.month as MONTH, Table1.year as YEAR,
> Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
> from Table2,
> (
> select month, year, sum (hours) as sumhrs
> from Table1
> where year = 2005
> group by month, year
> )
> where
> (
> Table1.year = Table2.year
> and
> Table1.month = Table2.month
> )
> order by Table1.Month
> RESULT:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
>
> I would like the results to look like this:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
> Mar 2005 NULL
> Apr 2005 NULL
> May 2005 NULL
> .etc.
> TIA!!
> Joyce
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thank you very much Thomas!!!
*** Sent via Developersdex http://www.examnotes.net ***

Null values not returns when "Not in List" used on column

Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
Paul
Yes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegro ups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegro ups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP
|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul

Null values not returns when "Not in List" used on column

Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
--
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to. :)
Thanks all.
Paul

Null values not returns when "Not in List" used on column

Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul

NULL values in CLR TableResult UDF

Hi everyone,

I need my UDF (which returns a table) to be able to return NULL values.

My function looks like this:

<SqlFunction(FillRowMethodName:="Process_TrainingInfo", TableDefinition:=" CourseName nvarchar(80), CreditDate DateTime, " + _

"CreditResult nvarchar(1), ExpiryDate DateTime ")> _

Public Shared Function funct_GetCreditsFromTIMS(ByVal EmployeeID As String) As IEnumerable

Dim dr() as dataRow

…. This queries an oracle database which returns a small number of rows…. This all works well….

Return dr

End Function

This is the Fill Row Method:

Public Shared Sub Process_ TrainingInfo(ByVal row As Object, <Runtime.InteropServices.Out()>ByRef CourseName As String, _

<Runtime.InteropServices.Out()> ByRef CreditDate As Date, _

<Runtime.InteropServices.Out()> ByRef CreditResult As String, _

<Runtime.InteropServices.Out()> ByRef ExpiryDate As Date)

Dim dr As DataRow = CType(row, DataRow)

CourseName = dr.Item(1).ToString

CreditDate = CType(dr.Item(2), Date)

CreditResult = dr.Item(3).ToString

'dr.Item(5) Might be NULL!!!!

If Not IsDBNull(dr.Item(5)) Then

ExpiryDate = CType(dr.Item(5), Date)

End If

End Sub

The problem is that the EXPIRYDATE field may be NULL. If I leave the field empty, or I try ExpiryDate = Nothing I get this error:

An error occurred while getting new row from user defined Table Valued Function :

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I have also tried:

Dim nullDate as Date

ExpiryDate = nullDate

…but I get the same error.

I can’t do ExpiryDate = Dbnull.value, as I get this error “System.dbnull can not be converted to date”

Is there anyway that I can do this?

Thanks,

Forch

Try and change the ExpiryDate out param to SqlDateTime (from System.Data.SqlTypes namespace) and when the value is null set the ExpiryDate to SqlDateTime.Null.

Niels

Null Field Values not returned - For XML AUTO

Is there a way to get SQL to return NULL field values as 'empty' XML tags,
instead of completely omitting them from the output?
I'm using FOR XML AUTO
Michael L wrote:
> Is there a way to get SQL to return NULL field values as 'empty' XML tags,
> instead of completely omitting them from the output?
> I'm using FOR XML AUTO
Use
FOM XML AUTO, ELEMENTS XSINIL
that way for a null value in a column you get
<Columnname xsi:nil="true"/>
where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
See "Using the XSINIL directive" in
<http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||THANKS! I assume this only works in SQL Server 2005?
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Michael L wrote:
> Use
> FOM XML AUTO, ELEMENTS XSINIL
> that way for a null value in a column you get
> <Columnname xsi:nil="true"/>
> where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
> See "Using the XSINIL directive" in
> <http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||Correct. This is only available in 2005. In 2000 you could always use
ISNULL(col, '') in the select clause (with cast adjustments for nonstring
types).
Best regards
Michael
"Michael L" <mlarter@.comcast.net> wrote in message
news:CMGdnckxp900QfjbnZ2dnUVZ_hCdnZ2d@.comcast.com. ..
> THANKS! I assume this only works in SQL Server 2005?
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

Null Field Values not returned - For XML AUTO

Is there a way to get SQL to return NULL field values as 'empty' XML tags,
instead of completely omitting them from the output?
I'm using FOR XML AUTOMichael L wrote:
> Is there a way to get SQL to return NULL field values as 'empty' XML tags,
> instead of completely omitting them from the output?
> I'm using FOR XML AUTO
Use
FOM XML AUTO, ELEMENTS XSINIL
that way for a null value in a column you get
<Columnname xsi:nil="true"/>
where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
See "Using the XSINIL directive" in
<http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||THANKS! I assume this only works in SQL Server 2005?
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Michael L wrote:
> Use
> FOM XML AUTO, ELEMENTS XSINIL
> that way for a null value in a column you get
> <Columnname xsi:nil="true"/>
> where xsi is bound to http://www.w3.org/2001/XMLSchema-instance.
> See "Using the XSINIL directive" in
> <http://msdn2.microsoft.com/en-us/library/ms177400.aspx>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Correct. This is only available in 2005. In 2000 you could always use
ISNULL(col, '') in the select clause (with cast adjustments for nonstring
types).
Best regards
Michael
"Michael L" <mlarter@.comcast.net> wrote in message
news:CMGdnckxp900QfjbnZ2dnUVZ_hCdnZ2d@.co
mcast.com...
> THANKS! I assume this only works in SQL Server 2005?
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:%23PvVNP2pHHA.4772@.TK2MSFTNGP05.phx.gbl...
>