how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>

How do I .. use a dataview to break up sqldatareader ? Please Help!

Hello,

I am using a sqldatareader to get back a whole set of data.

The columns are id, data_id and data_desc

for example, the collection is
id data_id data_desc
0 100 this is name for id 0
0 101 this is address for id 0
0 102 this is gender for id 0
1 100 this is name for id 1
1 101 this is address for id 1
1 102 this is gender for id 1
2 100 this is name for id 2
2 101 this is address for id 2
2 102 this is gender for id 2
3 100 this is name for id 3
3 101 this is address for id 3
3 102 this is gender for id 3

I want to be able to go thru' this list and break up for each id and create a txt file. So, i will have four text files.

txt0.txt will contain
100, this is name for id 0
101, this is address for id 0
102, this is gender for id 0

I would like to know, how do i use a dataview to break up the sqldatareader so i can then repeat the loop for each id and generate the text file.

I don't have a problem generating the text file.

Please let me know, how should i go about it.

Thanks a bunch,
-Sean

If you are just looking to loop thru a SqlDataReader. Try this.

SqlCommand cmd = new SqlCommand("<Your query here>", con);
cmd.Connection.Open();
SqlDataReader dtrText = cmd.ExecuteReaderCommandBehavior.CloseConnection);
while(dtrText.Read())
{
//Write to text file here.
}
dtrText.Close();
con.Close();

Hope this helps.

How could I use row as columns?

create table t2
(
umc varchar(20),
outdate datetime,
outnumber int
)
insert t2 values (1,'2005-2-5',1)
insert t2 values (2,'2005-2-5',1)
insert t2 values (2,'2005-2-6',1)
insert t2 values (3,'2005-2-5',2)
insert t2 values (3,'2005-2-6',1)
insert t2 values (4,'2005-2-7',1)
I hope the result to be
(2005-2-5,2005-2-6,2005-2-7 is column name now)
2005-2-5 2005-2-6 2005-2-7
1 1 0 0
2 1 1 0
3 2 1 0
4 0 0 1
Can I just compose it with SELECT statement?First you have to select distinct dates into a cursor,
than select from the table left outer join each date where date from the
table = date of the column.
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||http://aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||SELECT umc,
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
FROM T2
WHERE outdate >= @.dt
AND outdate < DATEADD(DAY,3,@.dt)
GROUP BY umc
Column names in a query are fixed so dynamic SQL would be required to
change the names based on the data. That shouldn't really be a problem
though. It should be easy enough to display different column names in
your client application.
David Portas
SQL Server MVP
--|||Nadim,
Thanks so much and that's what I want, however, is it possible for you to
show me some sample codes based on my DDL?
yours, XXY
"Nadim Wakim" <nadimlb@.cyberia.net.lb>
:uOjnD2NEFHA.1392@.tk2msftngp13.phx.gbl...
> First you have to select distinct dates into a cursor,
> than select from the table left outer join each date where date from the
> table = date of the column.
>
> "XXY" <xxy02021@.NOSPAM.163.com> wrote in message
> news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||Hi David and Roji,
I do appreciated your articles and sample codes, however when the outdate
ranges much(it might be any day in a year in my table), I am afraid it is
not a good idea using datediff. Don't you think so ?
yours, XXY
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
:1108198808.232843.84220@.g14g2000cwa.googlegroups.com...
> SELECT umc,
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
> FROM T2
> WHERE outdate >= @.dt
> AND outdate < DATEADD(DAY,3,@.dt)
> GROUP BY umc
> Column names in a query are fixed so dynamic SQL would be required to
> change the names based on the data. That shouldn't really be a problem
> though. It should be easy enough to display different column names in
> your client application.
> --
> David Portas
> SQL Server MVP
> --
>|||Roji, Thanks so much!!!
I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right answer
from
exec crosstab 'select umc from t2 group by
umc','sum(outnumber)','outdate','t2'
You are the MAN!!
"XXY" <xxy02021@.NOSPAM.163.com> д?
:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||I'm reminded of the Di-Tech commercials:( :)
www.rac4sql.net
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:%23kdz4APEFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Roji, Thanks so much!!!
> I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right
> answer
> from
> exec crosstab 'select umc from t2 group by
> umc','sum(outnumber)','outdate','t2'
> You are the MAN!!
>
> "XXY" <xxy02021@.NOSPAM.163.com> д?
> :eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||I don't see a problem. The date range selection is in the WHERE clause
and is sargable. The cost of DATEDIFF should be relatively light but if
performance is a concern then you should test it out with your typical
data-set.
David Portas
SQL Server MVP
--sql

How could I modify it as MDX Parameter?

Hi,
I have a MDX syntax as follow
select
[Measures].[Amount] on columns,
[XX03Buyer] on rows
from cubeef
where [XX01Product].[XX01Product].&[2212]
and want to change .&[2212] as Parameter,
that I can select Product code like 2200, 2211.
So, how should I do?
Thanks for any advice!
AngiSet up your parameter as you would normally then in the mdx change it to
="select" & "[Measures].[Amount] on columns, "
& "[XX03Buyer] on rows "
& "from cubeef "
& "where [XX01Product].[XX01Product].&["& Parameters!ProductID.Value &"]"
That should do it, as long as i got the syntax right. Problem with this is
that you can't test it unless you preview it. So if you want to get more
from you MDX suxh as more fields you need to take out all the quotes and &'s
and the = at the beginning. So my advice is save a copy of the MDX query
for a particular product so you can refresh your fields easily.
hope that helps
Greg
"Angi" <enchiw@.msn.com> wrote in message
news:%23pZmQPw9FHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MDX syntax as follow
> select
> [Measures].[Amount] on columns,
> [XX03Buyer] on rows
> from cubeef
> where [XX01Product].[XX01Product].&[2212]
> and want to change .&[2212] as Parameter,
> that I can select Product code like 2200, 2211.
> So, how should I do?
> Thanks for any advice!
> Angi
>sql

How could I get the records recusrively (nested)?

How could I get the records recusrively (nested)?
I've a table ACCT with columns as follows.
ACCT_CD varchar(20)
TYPE_CD varchar(1)
It has following data.
ACCT1 F
ACCT10 F
ACCT2 F
ACCT3 F
ACCT4 F
ACCT5 F
ACCT6 F
ACCT7 F
ACCT8 F
ACCT9 F
GRP_1 G
GRP_2 G
SGRP_1 C
SGROUP C
I've another table ACCT_REL with columns as follows.
PARENT_ACCT varchar(20)
CHILD_ACCT varchar(20)
REL_TYP varchar(1)
It has following data.
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGROUP'
SGROUP ACCT1 C
SGROUP ACCT2 C
SGROUP GRP_2 C
SGROUP SGRP_1 C
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_1'
GRP_1 ACCT3 G
GRP_1 ACCT4 G
GRP_1 ACCT5 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_2'
GRP_2 ACCT6 G
GRP_2 ACCT7 G
GRP_2 ACCT8 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGRP_1'
SGRP_1 GRP_1 C
SGRP_1 ACCT9 C
SGRP_1 ACCT10 C
I want retrive all the child_acct for PARENT_ACCT = 'SGROUP'. If the
CHILD_ACCT has some records in the ACCT_REL table, then I would like to
get them also. It could have many levels of nesting. How could I get the
records recusrively?
E.g. In the above example, the expected result could be:
ACCT1
ACCT10
ACCT2
ACCT3
ACCT4
ACCT5
ACCT6
ACCT7
ACCT8
ACCT9
GRP_1
GRP_2
SGRP_1
Thanks,
DJ
*** Sent via Developersdex http://www.examnotes.net ***check this out... (on behalf of ML :)
http://milambda.blogspot.com/2005/0...or-monkeys.html|||Also check out CTEs (Common Table Expressions) if you are using 2005.
There is a good example posted in Omni's blog here:
http://omnibuzz-sql.blogspot.com/20...vs.ht
ml
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9CB52622-90FB-4E32-9F92-CC3456633DDD@.microsoft.com...
> check this out... (on behalf of ML :)
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>|||Get a copy of TREES & HIERARCHJIES IN SQL for several methods of
modeling this kidn of data. You do not need recursive procedural code
in the Nesteed sets model and do this in one simple query.|||
Can you please tell me how to do this?
An example would be helpful.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||I beleive --CELKO-- is suggesting that you change your data model to store
the data in a tree format.
Omni's post contains one method for doing this.
CTEs on SQL Server 2005 allow you to do it with a single sql statement using
your current data model, but performance may not be as good.
If you do a search on "TREES & HIERARCHIES IN SQL", or just the phrases SQL
TREES HIERARCHIES, you will find several examples, including some articles
by Joe (--CELKO--).
"DJ" <dominic_koyappillil@.yahoo.com> wrote in message
news:uQqaGahiGHA.3572@.TK2MSFTNGP04.phx.gbl...
>
> Can you please tell me how to do this?
> An example would be helpful.
> Thanks.
> *** Sent via Developersdex http://www.examnotes.net ***

How come this dont work?

What is the definition of the table. Also, you didn't specify a row separato
r. Perhaps the table has
only one columns and you really want ~ as the row terminator?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deasun" <Deasun@.discussions.microsoft.com> wrote in message
news:BCA35F53-B895-4F6A-9435-8AA493BA3338@.microsoft.com...
> Ok I am new to this command, BULK INSERT, I cant seem to get this to work
or
> error out for me.
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
> I am running this thru QA logged in as the Admin.
> The file is in the location and the date looks like so, in Notepad.
> 302,2066707~302,2074122~302,2076346~302,
2077647~302,2094465~
> ~ is the field and row terminator.
> When I run above cmd I get it telling me it ran successfully.
> But theres no data in table and it runs instantly.
> Any help would be appreciated.
> Thanks
> Deasun
> --
> Deasun
> Home Site: www.tirnaog.com
> Check out: The Code Vault in my forums section."Deasun" wrote:

> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
That should be:
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
ROWTERMINATOR = '~'
)
ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
get the idea.
Maury|||Thanks to you both for the reply.
I had specified both row and field terminators at one point neither worked.
We now think it has something to do with the SQL server itself.
We have tried the cmd on another server and it worked fine.
Thanks again.
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.
"Maury Markowitz" wrote:

> "Deasun" wrote:
>
> That should be:
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> ROWTERMINATOR = '~'
> )
> ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
> get the idea.
> Maury
>|||Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
error out for me.
Heres what I have;
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
FIELDTERMINATOR = '~'
)
I am running this thru QA logged in as the Admin.
The file is in the location and the date looks like so, in Notepad.
302,2066707~302,2074122~302,2076346~302,
2077647~302,2094465~
~ is the field and row terminator.
When I run above cmd I get it telling me it ran successfully.
But theres no data in table and it runs instantly.
Any help would be appreciated.
Thanks
Deasun
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.|||What is the definition of the table. Also, you didn't specify a row separato
r. Perhaps the table has
only one columns and you really want ~ as the row terminator?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deasun" <Deasun@.discussions.microsoft.com> wrote in message
news:BCA35F53-B895-4F6A-9435-8AA493BA3338@.microsoft.com...
> Ok I am new to this command, BULK INSERT, I cant seem to get this to work
or
> error out for me.
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
> I am running this thru QA logged in as the Admin.
> The file is in the location and the date looks like so, in Notepad.
> 302,2066707~302,2074122~302,2076346~302,
2077647~302,2094465~
> ~ is the field and row terminator.
> When I run above cmd I get it telling me it ran successfully.
> But theres no data in table and it runs instantly.
> Any help would be appreciated.
> Thanks
> Deasun
> --
> Deasun
> Home Site: www.tirnaog.com
> Check out: The Code Vault in my forums section.|||"Deasun" wrote:

> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
That should be:
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
ROWTERMINATOR = '~'
)
ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
get the idea.
Maury|||Thanks to you both for the reply.
I had specified both row and field terminators at one point neither worked.
We now think it has something to do with the SQL server itself.
We have tried the cmd on another server and it worked fine.
Thanks again.
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.
"Maury Markowitz" wrote:

> "Deasun" wrote:
>
> That should be:
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> ROWTERMINATOR = '~'
> )
> ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
> get the idea.
> Maury
>

How can this be done...

I'm not to good in programming sql,
what i want to do is select 3 columns from 4 possible,
what i mean is that i have 2 columns in the DB that i need to select
only one of them everytime(one of them is null, but i can't no each row
which one)
and i need the result (the one that is not null) to be in a column
select into is my answer? i don't know how to use it...
plz advice.
And if you if it will work on sql mobile would be great aswellUse CASE , check SQL Books Online. This gives you the capacity to select
different columns depending on what values exist in the column(s)
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<chook.harel@.gmail.com> wrote in message
news:1149669940.164598.26800@.i39g2000cwa.googlegroups.com...
> I'm not to good in programming sql,
> what i want to do is select 3 columns from 4 possible,
> what i mean is that i have 2 columns in the DB that i need to select
> only one of them everytime(one of them is null, but i can't no each row
> which one)
> and i need the result (the one that is not null) to be in a column
> select into is my answer? i don't know how to use it...
> plz advice.
> And if you if it will work on sql mobile would be great aswell
>|||I'm not sure , so just a guess
SELECT col1,col2 +COALESCE(col3,'') AS bla FROM Table
<chook.harel@.gmail.com> wrote in message
news:1149669940.164598.26800@.i39g2000cwa.googlegroups.com...
> I'm not to good in programming sql,
> what i want to do is select 3 columns from 4 possible,
> what i mean is that i have 2 columns in the DB that i need to select
> only one of them everytime(one of them is null, but i can't no each row
> which one)
> and i need the result (the one that is not null) to be in a column
> select into is my answer? i don't know how to use it...
> plz advice.
> And if you if it will work on sql mobile would be great aswell
>|||Use COALESCE - check in BOL.
"chook.harel@.gmail.com" wrote:

> I'm not to good in programming sql,
> what i want to do is select 3 columns from 4 possible,
> what i mean is that i have 2 columns in the DB that i need to select
> only one of them everytime(one of them is null, but i can't no each row
> which one)
> and i need the result (the one that is not null) to be in a column
> select into is my answer? i don't know how to use it...
> plz advice.
> And if you if it will work on sql mobile would be great aswell
>|||Use
isnull or coalesce
check in BOL|||Thanks very much for the quick answers,
but from what i've read the COALESCE function still gets only one
paramater..
My thing is to differ from the columns and at the end show only one...|||ooops
I've missread,
Thanks alot! I'll check it out and return with my findings :)
chook.ha...@.gmail.com wrote:
> Thanks very much for the quick answers,
> but from what i've read the COALESCE function still gets only one
> paramater..
> My thing is to differ from the columns and at the end show only one...|||Hehe So I now understand that the COALESCE function checks for NULL
but when I have a null field i've put an empty string there, so it
still gets it
is there a way for the sql to interpert the empty string "" as null?
or should I rewrite my code, so that "" will go as NULL to the db.|||then you will have to use a case.
Say you want to take the non null value between two columns col2 and col3
select case isnull(col2,'') when '' then col3 else col2 end as non_null_col
from tbl1
But if both col2 and col3 are null, then it will give u a null.
You can extend this to more than 2 columns..
Hope this helps.|||On 7 Jun 2006 02:42:00 -0700, chook.harel@.gmail.com wrote:

>Hehe So I now understand that the COALESCE function checks for NULL
>but when I have a null field i've put an empty string there, so it
>still gets it
>is there a way for the sql to interpert the empty string "" as null?
>or should I rewrite my code, so that "" will go as NULL to the db.
Hi Chook,
NULLIF (TheColumn, '')
will return NULL if TheColumn contains an empty string. Otherwise, the
contents of TheColumn is returned.
Hugo Kornelis, SQL Server MVP

how can NULL be equal to todays date (was "Weird Query behavior plz help")

I have a query that is behaving a little a weird. here is the example:

i have 1 table in this table i have 2 columns wich are date and time

DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
---------- --------
1/27/1997 2/27/1997
1/1/2005 2/6/2005

here is my query:

BEGIN
declare @.datefin_flag datetime, @.strip datetime
SELECT @.strip = dateadd(d,datediff(d,0,getdate()),0)
SELECT @.datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < @.strip AND DATE_FIN_PERIODE_FISCALE = @.strip
--select @.datefin_flag
--select @.strip
IF(@.datefin_flag != @.strip)
RAISERROR('You cant run this',16,1)
END

Well this Query should return the raiserror it returns completes successfuly
since todays date is not the same as the date in the database.
if you select @.datefin_flag it returns NULL and if you select @.strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?Your code is checking for NOT equal. NULL is not equal to anything (including another NULL).

-PatP|||Crap...didnt think of that how would i go about fixing this based on my expresion since if it looks in the table and doesnt find anything.|||How about:IF(@.datefin_flag != @.strip OR @.datein_flag IS NULL)
RAISERROR('You cant run this',16,1)
END-PatP|||I'd write it this way...

IF NOT EXISTS( SELECT *
FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < dateadd(d,datediff(d,0,getdate()),0)
AND DATE_FIN_PERIODE_FISCALE = dateadd(d,datediff(d,0,getdate()),0)
)
RAISERROR('You cant run this',16,1)
END

But I'd handle the raise a little differently

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx|||Thanks alot Pat worked fine i realy apreciated.|||thanks also Brett that is a realy good alternative|||Your welcome...

But it's not really an alternative...

How can i write a sproc for dynamic columns

Hi...

Is it possible to eliminate values from a select statements if they are NULL or Blank..

the reason i have use cast as decimals is because that value is a varchar in my database... and i want to eliminate those values from my Final select statement that dont is blank so that i will have a data set which may be only 1 - 10 long...

ALTER PROCEDURE [dbo].[rpt_ParticipantPlanPeriodInvActivity]@.PlanIdint,@.ParticipantIdint,@.PeriodIdintASDECLARE @.tbl table (tblId smallint IDENTITY(1,1),ParticipantIdint,LoanIdint, Name1char(2), NDesc1char(30),TotAct1decimal(19,4),Name2char(2), NDesc2char(30), TotAct2decimal(19,4),Name3char(2), NDesc3char(30),TotAct3decimal(19,4),Name4 Char(2), NDesc4char(30),TotAct4decimal(19,4),Name5char(2),NDesc5char(30),TotAct5decimal(19,4),Name6char(2),NDesc6char(30), TotAct6decimal(19,4),Name7char(2),NDesc7char(30),TotAct7decimal(19,4), Name8char(2),NDesc8char(30),TotAct8decimal(19,4),Name9char(2),NDesc9char(30),TotAct9decimal(19,4), Name10char(2),NDesc10char(30),TotAct10decimal(19,4),Name11char(2),NDesc11char(30),TotAct11decimal(19,4),Name12char(2),NDesc12char(30),TotAct12decimal(19,4),Name13char(2),NDesc13char(30),TotAct13decimal(19,4), Name14char(2),NDesc14char(30), TotAct14decimal(19,4),Name15char(2),NDesc15char(30),TotAct15decimal(19,4),Name16char(2),NDesc16char(30),TotAct16decimal(19,4),Name17char(2),NDesc17char(30),TotAct17decimal(19,4),Name18char(2),NDesc18char(30),TotAct18decimal(19,4), Name19char(2),NDesc19char(30),TotAct19decimal(19,4),Name20char(2),NDesc20char(30),TotAct20decimal(19,4) )Insert Into @.tbl SELECTpf.ParticipantId,pf.FundIdas LoanId, --CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames, --pf.PortfolioId,--PortfolioName, Act1as Name1, a.Descriptionas NDesc1, cast(TotAct1as decimal(19,4)) ,Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn pf.PortfolioId = p.PortfolioId Left outer Join AscActCodes a on pf.Act1 = a.Name left outer Join AscActCodes b on pf.Act2 = b.Name left outer Join AscActCodes c on pf.Act3 = c.Name left outer Join AscActCodes d on pf.Act4 = d.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHERE pf.FundId = 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDpf.ParticipantId = @.ParticipantId--Get the Fund informationfor the report and combine it with the Loan information--in the table variable...SELECTpf.ParticipantId,pf.PortfolioId,PortfolioName,pf.FundId LoanFundId, CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames,Act1as Name1, a.Descriptionas NDesc1, Cast(TotAct1as decimal(19,4)),Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn pf.PortfolioId = p.PortfolioId Left outer Join AscActCodes a on pf.Act1 = a.Name left outer Join AscActCodes b on pf.Act2 = b.Name left outer Join AscActCodes c on pf.Act3 = c.Name left outer Join AscActCodes d on pf.Act4 = d.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHEREpf.FundId <> 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDParticipantId = @.ParticipantIdUnionSELECTParticipantId,0,'NA',LoanId,'Loan ' + cast(tblIdas char(1)),Name1,NDesc1,Cast(TotAct1as decimal(19,4)),Name2,NDesc2,Cast(TotAct2as decimal(19,4)),Name3,NDesc3,Cast(TotAct3as decimal(19,4)), Name4,NDesc4,Cast(TotAct4as decimal(19,4)),Name5,NDesc5,Cast(TotAct5as decimal(19,4)),Name6,NDesc6,Cast(TotAct6as decimal(19,4)),Name7,NDesc7,Cast(TotAct7as decimal(19,4)),Name8,NDesc8,Cast(TotAct8as decimal(19,4)),Name9,NDesc9,Cast(TotAct9as decimal(19,4)),Name10,NDesc10,Cast(TotAct10as decimal(19,4)),Name11,NDesc11,Cast(TotAct11as decimal(19,4)),Name12,NDesc12,Cast(TotAct12as decimal(19,4)),Name13,NDesc13,Cast(TotAct13as decimal(19,4)),Name14,NDesc14,Cast(TotAct14as decimal(19,4)),Name15,NDesc15,Cast(TotAct15as decimal(19,4)),Name16,NDesc16,Cast(TotAct16as decimal(19,4)),Name17,NDesc17,Cast(TotAct17as decimal(19,4)),Name18,NDesc18,Cast(TotAct18as decimal(19,4)),Name19,NDesc19,Cast(TotAct19as decimal(19,4)),Name20,NDesc20,Cast(TotAct20as decimal(19,4)) FROM @.tbl

Any help will be appreciated.

Regards

Karen

You can either limit rows in the WHERE clause by specifying the NOT NULL option or you can cast at the front end...

|||

huh?

Please post the minimum amount of code necessary to make your question clear. We don't want to read a book! :)

Your queries are bringing back values from many different columns in one resulting row.

Do you want to skip an entire row because one of the columns has a null or blank value? Or something else? I really don't understand.

|||

I want to skip an entire column because everything is NULL

|||

Never ask a query you don't want the answer to! :)

The short answer is no. If you query a value as a distinct column in the result set, you get that column in the result set.

The longer answer is maybe. If you don't mind concatenating multiple values together into one column in the result set, you can effectively skip the column. Just be sure to use isnull(columnname,'') to replace null values with an empty string, otherwise the whole string will end up null. That's a useful technique for writing lines of text that need to be printed out (as in queries that write sql code), but useless if you need the other column values back as discrete values.

|||

Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..

Regards

Karen

|||

Karenros:

Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..

Sorry, no. I'm sure you can get a free website with yahoo or geocities.

|||

David and Dinakar

Thanks for your answers..

Suppose if i have declared a table in my sproc.. like

Declare @.tbl table

(

Column1 - Column N

)

Is it possible to add columns dynamically to it.. like for eg..

my select statement has around 10 columns and in that 4 columns are returning no value... so can i adjust the number of columns in that table.. based on the select statement results...

Regards

Karen

|||

Karenros:

Declare @.tbl table

(

Column1 - Column N

)

I don't understand the syntax you are using in your example.

You could do this as a two step process.

Issue the query with all the columns.

Construct a query statement in a string that only includes the columns that got results and exec that query string.

Honestly, why not just set the Visible or Hidden property of the UI component to false for those columns that don't have values. I think that would be a lot simpler. :)

|||

The reason i am not doing it in the UI compenent,, cause in SSRS 2005 if i hide a column the width of the table would shrink.

Declare @.Tbl Table

(

tbld int identity,

col 1,

Col2,

.

.

Col N

)

|||

To add columns you need to ALTER TABLE. I dont think you can ALTER a table variable. you might either need to use an actual table or a temporary table... I havent looked at your entire post.. I've been quite busy last week and will be so next week too.. am just posting based on your most recent post...

How can i view the value of a variable during debug?

Thats the question... i can see the value of columns adding viewers, but how can i see variables?

Thanks!

Add a break point to one of your control flow tasks (by right clicking on it) and then when debug stops at that break point, you can go up to the Debug menu and select the Locals window to be displayed.|||

Thanks Phil,

I do that, but my variable doesnt appear in the window...

|||

Which window are you looking at? It should be in the Locals window; you have to expand the variables node...

|||

Sorry for my delay rafael, i was on holidays ;-)

I am looking at "Debug/windows/local variables", but nothing appears in this window.

Edit: I finally found the solution, the problem was taht i didnt insert the break point on control flow. Regards.

How can i view the value of a variable during debug?

Thats the question... i can see the value of columns adding viewers, but how can i see variables?

Thanks!

Add a break point to one of your control flow tasks (by right clicking on it) and then when debug stops at that break point, you can go up to the Debug menu and select the Locals window to be displayed.|||

Thanks Phil,

I do that, but my variable doesnt appear in the window...

|||

Which window are you looking at? It should be in the Locals window; you have to expand the variables node...

|||

Sorry for my delay rafael, i was on holidays ;-)

I am looking at "Debug/windows/local variables", but nothing appears in this window.

Edit: I finally found the solution, the problem was taht i didnt insert the break point on control flow. Regards.

How can i use Recursive CTE here

Hello Everyone,

I have a purchase order table that holds say 2 columns.PO andOrgPO. That isPurchase Order # andOriginal Purchase Order # respectively. Assume i have the following rows in the table.

PO OrgPO

-- --

po1 NULL

co1 po1

co2 co1

co3 co2

po2 NULL

cpo1 po2

po3 NULL

Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,

PO LastCO

-- --

po1 co3

po2 cpo1

po3 po3

Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.

Assume your table named 'pot' and here's the code below:

WITH x (newPO, newLastCO, depth)AS(SELECTCASEWHEN OrgPOISNULLTHEN POELSE OrgPOEND AS newPO, POAS newLastCO, 0AS depthFROM potWHERE OrgPOISNULLUNIONALLSELECT x.newPO, POAS newLastCO, x.depth + 1FROM pot pJOIN xON x.newLastCO = p.OrgPO)SELECT x.newPO, x.newLastCOFROM xINNERJOIN (SELECT newPO,MAX(depth)AS deepestFROM XGROUP BY newPO)AS yON x.newPO = y.newPOAND x.depth = y. deepestORDER BY x.newPO

|||

Hi jackyang,

Thanx for the solution man