How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql

How to eliminate the commas from the end in sql query when the column value is null

HI

I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like

select ISNULL(dbo.tblperson.Email1, N'') +';'+ISNULL(dbo.tblperson.Email2, N'') +';'+ISNULL(dbo.tblperson.Email3, N'')ASEmail from tablename.

One eg of the output of the above query when email2,email3 are having null values in the table is :

jacky_foo@.mfa.gov.sg;;

means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.

Please let me know how can i do this

If you just change SQL a bit you have the answer, see below

select ISNULL(dbo.tblperson.Email1+ ';', N'') + ISNULL(dbo.tblperson.Email2 + ';', N'') + ISNULL(dbo.tblperson.Email3 + ';', N'')ASEmail from tablename.

|||

I tried this it worked a bit but not completely.Now I am getting the semicolumn at the end if there is null for the third column or you can say for the last column.

|||

There is probably a quick easy way to do it, but this will work:

select ISNULL(dbo.tblperson.Email1, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email2, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL OR dbo.tblperson.Email2 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email3, N'')ASEmail from tablename.

|||

Another way is to first normalize your data:

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

Now use the normalized data in a query like:

DECLARE @.Email varchar(max)

SELECT @.Email=ISNULL(@.Email+';','')+Email

FROM (

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

) t1

SELECT @.Email AS Email

|||

Another way is to use one of the many string concatenation techniques once your data has been normalized.

There is a CONCATENATE aggregation function that you can install that will do the trick. (Microsoft supplies one somewhere, google "T-SQL string concatenation aggregate").

There is another technique using the FOR XML/PATH to do the same thing, but it's also kind of messy.

|||

I appreciate your reply. This query worked for me.

How to eliminate Space entries...?

Hey,
I have some field values entries in my database.. that are spaces like ' '. i wanna eliminate them.
When i use IS NOT NULL in query it only eliminates the rows with NULL values so how could i modify the query to eliminate the rows with spaces in the field value..

Thx in advance..where COALESCE(somecolumn,' ')<>' '|||Thank you very much sir..|||SELECT * FROM Table1 WHERE Col1 > ' '|||WHERE NULLIF(somecolumn,' ') IS NOT NULL

How to Eliminate Nodes with Null values?

I need to shred the xml data to retrieve BrandIDs based on the following business rules.

/**************************************************************************************************************************

(1) Not every instance of xml would contain BrandIDs node

(2) Ignore BrandIDs whenever its a descendant of AlternativeState

(3) We are interested in the data stored under MarketSize whenever the CurrentEvent node is MarketSize.

(4) We are interested in the data stored under OtherEvent whenever the CurrentEvent node is not MarketSize.

****************************************************************************************************************************/

While shredding the xml data, I have noticed that out of 200,000 xml rows there are only 1000 BrandIDs nodes that do actually have data in them (e.g. <BrandIDs> 123, 234</BrandIDs>. Others are just blank in the form of </BrandIDs>. I would like to modify XQuery given below so that I could filter out such rows where even though BrandIDs node exist but it has no scalar value for me to retrieve. From the sample query result given at the end you would notice that the third row is empty. I would like to avoid such rows in result set.

I am open to any suggestions here if anyone out there could come up with a better solution.

declare @.xml xml

set @.xml =

'

<State>

<StatsState>

<CurrentState>

<BrandIDs>2698741</BrandIDs>

</CurrentState>

</StatsState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<BrandIDs>160603,160737</BrandIDs>

</OtherEvents>

<CurrentEvent>BrandShare</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<StatsState>

<CurrentState>

<MarketSize>

<BrandIDs />

<AlternativeState>

<CurrentEvent>None</CurrentEvent>

<BrandIDs>25630,8956201</BrandIDs>

</AlternativeState>

<CompanyIDs />

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<BrandIDs>2001,2002,2003,2004,2005,2006</BrandIDs>

</OtherEvents>

<CurrentEvent>BrandShare</CurrentEvent>

<MarketSize>

<BrandIDs>40666,71788,201225</BrandIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

'

SELECT

Element.Val.query(

'for $s in self::node()

where $s//*/BrandIDs[not(parent::AlternativeState)]

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/BrandIDs/text()

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) != "MarketSize")

then $s/StatsState/CurrentState/OtherEvents/BrandIDs/text()

else $s//BrandIDs/text())

') AS BrandIDs

FROM @.xml.nodes('/State') AS Element(Val)

GO

BrandIDs

-

2698741

160603,160737

2001,2002,2003,2004,2005,2006

If an element is empty then it does not have any child nodes meaning you can check with e.g. BrandIDs[node()] for BrandIDs elements that are not empty.

So your query could be written as

Code Snippet

SELECT

Element.Val.query(

'for $s in self::node()

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/BrandIDs/text()

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) != "MarketSize")

then $s/StatsState/CurrentState/OtherEvents/BrandIDs/text()

else $s//BrandIDs/text())

') AS BrandIDs

FROM @.xml.nodes('/State[.//*/BrandIDs[node() and not(parent::AlternativeState)]]') AS Element(Val)

|||

Hi marton,

Thanks once more for helping me out here. Your proposed solution does solve my problem. Is there any way that perhaps you could use the 'where' clause in FLWOR to apply the same condition? I actually have a requirement to use @.xml.nodes('/State'). I do have different set of FLWOR queries to read values for different nodes and for each the root node is always /State and I would need to combine all of them in one statement. So preferably I would like to keep the nodes clause pointing to root.

Hope you get my point.

thanks again

|||The problem is that the nodes methods shreds the xml variable into rows that you then query with the query method. So for the original example you got four rows in the result set as the nodes method yields four rows, independent of the query applied later to the each row. If you want to eliminate nodes to not yield rows at all then I think it as to be done with the nodes method.|||

Hi Martin,

Thanks for clarification. I understand your point.

I actually works with xml where each of the node goes to its own relational table and I wanted to have only one select statement where each xml row is read only once and I get all of the values for nodes by specifying any business rules within FLWOR there. Hence I hesitate to specify any specific node such as BrandIDs in nodes() because it wouldn't leave an option for me to work with other nodes. I think I would have to use function for each node and call them from my select statement.

Many thanks

How to eliminate login screen when i try to access report via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

If It is for login to the datasource then you ned to pass datasource credentials. (reportviewer.setdatasourcecredentials())And if it ask you to login to the report server than you need to set the report server to allow access of the report to your website.

and if you want you can set report server credentials.. and give that credentials access to the reports using report manager.

|||

Hi, Thanks.

I gave permissions to report manager folder for aspnet and also iusr_machine name both.

and also checked anonymous login for reports virtual directory under intepub.

still i get the login screen if somone trying to access reports via reporting services reports folder.

i am calling the reports via url from vs 2003 , and my reports are on vs 2005.

And microsoft did'nt release no report viewer with framework 1.1., that is causing the problem.

please help guys.,

Thank you all.

|||

I can give you one option.

You can crete one project with a page (may be 1 default.aspx) containing report viewer and if you want parameter promp controls of your own.

And redirect your users from original website to new website when ever they select menuitem( or whatever you have used) for report.

Now for login screen.

I need to know where it pop us..

when anyone trying to go to report folder or when any one trying to run report.

If it is asking when anyone trying to go to report folder.. I thing you need to pass login for the user in URL( Not sure how)

If it is asking when they run report just do one thing. go to each report.. than properties than datasource and save credentials for datasource.

How to eliminate login screen when i try to access report via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

If It is for login to the datasource then you ned to pass datasource credentials. (reportviewer.setdatasourcecredentials())And if it ask you to login to the report server than you need to set the report server to allow access of the report to your website.

and if you want you can set report server credentials.. and give that credentials access to the reports using report manager.

|||

Hi, Thanks.

I gave permissions to report manager folder for aspnet and also iusr_machine name both.

and also checked anonymous login for reports virtual directory under intepub.

still i get the login screen if somone trying to access reports via reporting services reports folder.

i am calling the reports via url from vs 2003 , and my reports are on vs 2005.

And microsoft did'nt release no report viewer with framework 1.1., that is causing the problem.

please help guys.,

Thank you all.

|||

I can give you one option.

You can crete one project with a page (may be 1 default.aspx) containing report viewer and if you want parameter promp controls of your own.

And redirect your users from original website to new website when ever they select menuitem( or whatever you have used) for report.

Now for login screen.

I need to know where it pop us..

when anyone trying to go to report folder or when any one trying to run report.

If it is asking when anyone trying to go to report folder.. I thing you need to pass login for the user in URL( Not sure how)

If it is asking when they run report just do one thing. go to each report.. than properties than datasource and save credentials for datasource.

sql

How to eliminate duplicate data

I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?

You can use the Group By fucntion

SELECT col_a, col_b, col_c From Table GROUP BY col_a, col_b, col_c

If you want the newest date - you can also using HAVING Clause

SELECT col_a, col_b, col_c From Table GROUP BY col_a, col_b, col_c HAVING max(col_date)

WHERE col_a - col_c are the columns with the same data and col_date is your date column

AWAL

|||Is this a easier process if I manually delete them? I just want to view the duplicate data but my problem is that the DateTime column in my table is unique unlike all the other columns with the same values.|||

John -

I'm not quite certain what you mean but if your datetime column is not unique but you want dups of all the other columns just don't group by the datetime column.

AWAL

|||

Assuming that your data isn't too big, you can use a technique like this:

drop table removeDups
go
create table removeDups(
column1 int,
column2 int,
column3 int,
column4 int,
column5 int,
column6 int,
column7 int,
column8 int,
column9 int,
datevalue datetime)

insert removeDups
select 1,1,1,1,1,1,1,1,1,getdate()
waitfor delay '00:00:01'
insert removeDups
select 1,1,1,1,1,1,1,1,1,getdate()
waitfor delay '00:00:01'
insert removeDups
select 1,1,1,1,1,1,1,1,1,getdate()
waitfor delay '00:00:01'

insert removeDups
select 2,2,2,2,2,2,2,2,2,getdate()
waitfor delay '00:00:01'
insert removeDups
select 2,2,2,2,2,2,2,2,2,getdate()
waitfor delay '00:00:01'
insert removeDups
select 3,3,3,3,3,3,3,3,3,getdate()
waitfor delay '00:00:01'

delete from removeDups
where not exists
(select *
from ( select min(dateValue) as dateValue,column1,column2,column3,column4,column5,column6, column7, column8, column9
from removeDups
group by column1,column2,column3,column4,column5,column6, column7, column8, column9) as mins
where mins.dateValue = removeDups.dateValue
and mins.column1 = removeDups.column1
and mins.column2 = removeDups.column2
and mins.column3 = removeDups.column3
and mins.column4 = removeDups.column4
and mins.column5 = removeDups.column5
and mins.column6 = removeDups.column6
and mins.column7 = removeDups.column7
and mins.column8 = removeDups.column8
and mins.column9 = removeDups.column9)

select *
from removeDups

I figure once you finish this process you will probably want to hurt the person who gave you this design, even if it is yourself. Try to identify a key amongst the 68 columns and add a unique constraint so you can never get in this position again :)

how to eliminate "items to synchronize" dialog?

Hi there:
When I log in and log out, I get a dialog prompting me to create a
subscription to synch Sql Server data. I have tried to google the
issue to find out how to eliminate the dialog, without finding
anything useful. Can someone point me to a resource?
Thanks;
Duncan
Have a look at windows synchronization manager. Go to Start, All Programs,
and click on Synchronize. Delete the unwanted items there.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Duncan A. McRae" <google.com@.mcrae.ca> wrote in message
news:d0b466c7-b0dd-4826-82ca-fe8cb924e746@.i12g2000prf.googlegroups.com...
> Hi there:
> When I log in and log out, I get a dialog prompting me to create a
> subscription to synch Sql Server data. I have tried to google the
> issue to find out how to eliminate the dialog, without finding
> anything useful. Can someone point me to a resource?
> Thanks;
> Duncan