have elements even when column has NULL value

Hi guys,
Sorry if posted twice as I could not see the previous post...
I need to capture element name even when the column it is associate to has a NULL value. I can best describe this with an example...
Say for example we have 2 tables names tblProduct and tblCategory with following columns and relation...
tblProduct
ProductId int (PK)
ProductName varchar(30)
CategoryId int (FK to tblCategory)
tblCategory
CategoryId (PK)
CategoryName varchar(30)
table data...
tblProduct
ProductId ProductName CategoryId
123 testProduct1 777
345 testProduct2 NULL
678 testProdyct3 888
tblCategory
CategoruId CategoryName
777 testCategory1
888 testCategory9
999 testCategory44
Now I need to have XML output as...
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName><CategoryName></CategoryName></Product><Produ
ct>
...
...
</Product></Products>
In the above case, CategoryName element should be captured even when the product has null value for it.
So, you have an schema xsd as...
<?xml version="1.0" encoding="UTF-8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship name="ProductCategory"
parent="tblProduct"
parent-key="CategoryId"
child="tblCategory"
child-key="CategoryId" /></xsd:appinfo></xsd:annotation><xsd:element name="Products" sql:is-constant="1"><xsd:complexType><xsd:sequence maxOccurs="unbounded"><xsd:element ref="Product"/></xsd:sequence></xsd:complexType></xsd:element><xsd:element name="P
roduct" type="ProductDetails"
sql:relation="tblProduct"
sql:key-field="ProductId"/><xsd:complexType name="ProductDetails"><xsd:sequence><xsd:element name="ProductId"
type="xsd:integer"
sql:field="ProductId"/><xsd:element name="ProductName"
type="xsd:string"
sql:field="ProductName"/><xsd:element name="CategoryName"
type="xsd:string"
sql:field="CategoryName"
sql:relation="tblCategory"
sql:relationship="ProductCategory"/></xsd:sequence></xsd:complexType></xsd:schema>
The problem is that you get below output when CategoryId is null for some of the products
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName>
***** error ***** does not get captured
</Product><Product>
...
...
</Product></Products>
I would apprecaite if anyone has a solution for this.
Thanks
-Sid
"sid" <anonymous@.discussions.microsoft.com> wrote in message
news:FBC3236C-9C0E-49E1-971D-4E7FD4F638DF@.microsoft.com...
> Hi guys,
> Sorry if posted twice as I could not see the previous post...
> I need to capture element name even when the column it is associate to has
> a NULL value. I can best describe this with an example...
See this FAQ:
http://sqlxml.org/faqs.aspx?faq=14
Bryant

Hard Coded parameters

Is it possible to force parameters into the reports so enabling me to force a user id value into every report that is picked up from the list. The user ID is a system value and I don't want end users having any knowledge of it?

Cheers

Darren

Hello Darren,

What you can do is create a hidden/internal parameter so that the end user can not see it. From BOL:

Hidden

Select this option if the parameter value should not appear on the report. Although hidden parameters do not appear on a report, they can be set in other ways (for example, in subscriptions and through URLs).

Internal

Select this option if the parameter cannot be changed at run time. On a published report, no visual evidence is provided to indicate that this parameter exists.

Hope this helps.

Jarret

|||

Hi Jarret,

I think this may be the answer, I need to provide a list of published reports , how would we then get the hidden parameter into it.

Can we display a URL with the user ID already in it which will then render the report viewer with the other optional parameters to be picked. How do we stop somebody constructing a URL with a different user ID.

A hidden parameter sounds like the right option but I'm confused how to populate it.

Cheers, Darren

|||

If you're wanting to keep it out of the URL as well, you'll need to go with an Internal parameter, since it can not be changed at run time. Hidden parameters can be changed through the URL. You can change the value of this from within your Report Manager. Go to the properties of your report, then go into the Parameters section. In there, you can change the default value to whatever you like.

Hope this helps.

Jarret

|||Is the Internal parameter fixed in the report then? I want to be able to change the value based on the user running the report but do not want them to be able to change to a user ID not associated to them.|||

I'm not sure how you were planning to change the user ID of the user running the report, but you can set an expression for the default value of any parameter through VS.

If you put =User!UserID as the Default value for your parameter, it will show the domain account used.

Just remember, if you use a hidden parameter, someone can manipulate the value via the URL. This is not possible with an internal parameter.

Jarret

|||

Thanks Jarret,

I think I know what we will do know. The web site maintains a guid session token for each user which originates from the database initially so if I use this as my hidden parameter and let the reports stored procedure calculate the user ID based on this I will be safe.

Cheers

|||

That sounds like it would work, but someone can pass in that parameter via the URL since you're using a hidden parameter. The internal parameter should work in this situation too, and you don't have to worry about it being changed by a user.

Anyway, I'm glad you have a solution. Can you mark this one as answered so others can see the solution?

Jarret

hanlding null value in stored procedure

Hi,
I have a simple update query as
update tblApplication
set TotalSworn = MaleSworn + FemaleSworn,
TotalCivilian = MaleCivilian + FemaleCivilian,
GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
However, I need to build a stored procedure out of the above with the
fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
FemeleCivilian fields can have null values.
Any help is appreciated. Thanks.Use COALESCE or ISNULL
like this
update tblApplication
set TotalSworn = Coalesce(MaleSworn,0) + Coalesce(FemaleSworn,0),
TotalCivilian = Coalesce(MaleCivilian,0) + Coalesce(FemaleCivilian,0),
GrandTotal = Coalesce(MaleSworn,0) + Coalesce(FemaleSworn,0) +
Coalesce(MaleCivilian,0) + Coalesce(FemaleCivilian,0)
http://sqlservercode.blogspot.com/
"Jack" wrote:

> Hi,
> I have a simple update query as
> update tblApplication
> set TotalSworn = MaleSworn + FemaleSworn,
> TotalCivilian = MaleCivilian + FemaleCivilian,
> GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
> However, I need to build a stored procedure out of the above with the
> fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
> FemeleCivilian fields can have null values.
> Any help is appreciated. Thanks.
>|||Jack,
The column definition defines whether or not the column will allow NULLs.
HTH
Jerry
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:CA610AF3-57EA-4E1B-B1CC-665ABCCE6153@.microsoft.com...
> Hi,
> I have a simple update query as
> update tblApplication
> set TotalSworn = MaleSworn + FemaleSworn,
> TotalCivilian = MaleCivilian + FemaleCivilian,
> GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
> However, I need to build a stored procedure out of the above with the
> fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
> FemeleCivilian fields can have null values.
> Any help is appreciated. Thanks.
>|||Thanks for the help to both of you. I appreciate it. Regards.
"Jerry Spivey" wrote:

> Jack,
> The column definition defines whether or not the column will allow NULLs.
> HTH
> Jerry
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:CA610AF3-57EA-4E1B-B1CC-665ABCCE6153@.microsoft.com...
>
>

Handling NULLS - help!

I would like to display some text in a textbox conditional on the null/not
null value of a database field (called "ID").
I have tried various versions of the following:
=iif(len(trim(Fields!ID.Value))<1 OR (Fields!ID.Value IS
system.dbnull.value),"ID is null","ID is not null")
but I still get this error:
"The query returned no rows for the data set. The expression therefore
evaluates to null."
any suggestions?Try =IIF(IsNothing(Fields!ID.Value), ...|||Thanks Rose - that worked perfectly :)

Handling columns with Nvarchar value

Hai All,
can any one out there help me in the following scenario.
I have a Remarks column in a table which is NVARCHAR field and length set
4000. This column value is set by a SP. Case is that when the remarks grows
beyond 4000 , how will i handle it. The requirement is, that I should not
ignore any remarks value.
Such Larger values how will i handle.
Thanks,
V.Boomessh
Hi
Look at NTEXT datatype in BOL. After NVARCHAR(4000), with SQL Server 2000,
your only option in NTEXT.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:61F38371-3BEF-436B-9421-FF37E6C30CF0@.microsoft.com...
> Hai All,
> can any one out there help me in the following scenario.
> I have a Remarks column in a table which is NVARCHAR field and length set
> 4000. This column value is set by a SP. Case is that when the remarks
> grows
> beyond 4000 , how will i handle it. The requirement is, that I should not
> ignore any remarks value.
> Such Larger values how will i handle.
> Thanks,
> V.Boomessh
>
>

Handling a Null datetime column

can anybody tell me how to do a select query on a datetime field where if i have a null value in that column, i need to display a some character.

ISNULL is a lovely function useful for doing just that.

ISNULL(MyDateColumn, 'ITS NULL!')

returns ITS NULL if column MyDateColumn's value is NULL

|||

select donor_id,isnull(check_date,'No Value') from donors where check_date is null

If the column check_date consists the null value U will get the value No Value.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||I would say it depends what is the type of your field. If your filed is data time you shoudl convert it to string for output to not have problems with data type.Looks that ISNULL is trying to convert default for null value to the same type like tested value so you you would like to use syntax like this:ISNULL(DateField, 'Null date')It can not work because SQL will try to convert you "NUll Date" to be datetime and will fail. So you have to use different syntax:ISNULL(convert(varchar(20),DateField), 'Null date')and this should work but returned column will be varchar(20) not datetime.In case you need datetime column just left nulls inside or use first valid date :ISNULL(DateField, '00:00')it will replace all nulls by date '01/01/1900 00:00' Bu I think that keeping null is better.|||

ISNULL wont work with datetime a column if we r replacing with some characters. so in this case whatjpazgiermentioned is right.but in that too there is a flaw. what i got here is we need to check for each part of the datetime value for NULL.like dd/mm/yyyy, then hh:mm:ss,then am/pm.

so the query will be like

SELECT column1,Isnull(
(convert(varchar(20),columnDate,101) + ' ' + convert(varchar(20),columnDate,108) + ' ' + right(convert(varchar(20),columnDate),2)),'-') column2 from table1

im not sure if the above mentioned is the best solution possible.if anyone have any easy method other than this please reply.

Handling a double or float value for inserting into DataTime field.

Hi,

I was trying to enter the non-normalised exponential format of double or float value into the DataTime field in my data base. It is allowing to store any kind of data passed to this field. If the same non-normalised exponential value for eg: 4.235E-329 is passed to float or double field we are getting a TDS error but when same thing is used to store in DateTime field it is simply inserting the value.

Now my concern is that SQL Server 2005 should give me such kind of exception when I am trying to insert in-valid double or float value to DateTime field. Is this a bug in SQL Server 2005? Please kindly help me how to implement this one.

Thanks,

I get the same results on SQL 2000 and SQL 2005 when I run the script below.

I get
Server: Msg 168, Level 16, State 1, Line 2
The floating point value '4.235E-329' is out of the range of computer representation (8 bytes).

and the value 1900-01-01 00:00:00.000 is inserted into the datetime field and the value 0.0 gets inserted into the float field.

Can you please clarify your question or provide a repro?

Thanks!
use tempdb
go

create table t1(d datetime)
go

create table t2(f float)
go

insert into t1 (d) values (4.235E-329)
go

insert into t2 (f) values (4.235E-329)
go

select * from t1
go

select * from t2
go

drop table t1
go

drop table t2
go

|||

Unfortunately, SQL Server is a bit inconsistent in its compliance with the IEEE floating point standards. The value 4.235E-329 is less than 2^(-1023), and it can only be represented as a reduced-precision "denormalized" value. In some situations, these values will be understood and used correctly, and in others they will generate errors. The best advice I can offer is to be cautious with them, unfortunately. In SQL Server 2000, for example (and I expect in 2005 as well), the first code snippet here will succeed, but the second will fail:

declare @.f float
set @.f = 1e-307
set @.f = @.f/100000000000000
select @.f
go

declare @.f float
set @.f = 1e-322
select @.f
go

Steve Kass
Drew University

|||Problem Repro steps:

Create table tblTest1(id int, fld1 float, dtfld datatime)
Go

insert into tblTest1 values(10, 4.235E-329, '10/29/2005')
Go

This insert will give an error
Server: Msg 168, Level 16, State 1, Line 2
The floating point value '4.235E-329' is out of the range of computer representation (8 bytes).

If we selected the data then we will see there will be no record inserted.

now change the insert statment as

insert into tblTest1 values(10, 4.23, 4.235E-329)
Go

Then this will say that 1 record inserted and when we select the table to display the rows we will see that the record inserted. My Question is that if we insert a wrong non-normalised (Exponential) double or float value into float or double column it is raising an error and the trasaction is rolled back, but if we have given the same value to a datatime field, it is not raising any error and the record is inserted. We want to know is that a Bug in SQL Server 2000 / 2005.

Our main concern is that we want to get a such a kind of exception when we try to insert a wrong non-normalised (Exponential) value into datatime field and the transaction should be rolled back. Is there any work around to get rid of this issue, please mention me

Thanks|||When I ran your scripts on SQL 2005, both insert statements would throw a warning (msg 168), and then the insert would continue and insert one row into the table. The warning indicates that there is an UNDERFLOW for the floating point value, and the value is turned into 0. Is this not the result you are seeing?

Because floating point is imprecise itself, we chose to map denormalized values to zero. Note that the values inserted into the table are actually 0, not the denormalized value. I understand that you might want to see an error, but such behavior would potentially break other existing applications.

Regards,
Jun
|||Hi Jun Fang,

According to your message you said that a warning(msg 168) is thrown when the transaction is happening, please kindly help us how to trap this warning message from the ADO.Net 2.0 (front end application) so that we can show the message and roll back the entire transaction for this kind of scenario.

Thanks

Handle error in t-sql

Hi,

I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).

If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?

Thanks

Yes, I believe that feature was added in SQL Express/2005, but I'm not familiar enough with it to give examples. Normally, I would not create a SQL query that would ERROR, but it might return an empty resultset, or other indicator that it failed. If I need to catch a true error, then catch it in a try/catch block in your code.