Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title = 'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steenuntested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
--
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Try Something on these lines:
UPDATE user
SET userdepartment = CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
...userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
.
Keith Kratochvil wrote:
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I need some help on how to update some fields with a value based on
>> the value in another field. I have tried to do this with a CASE
>> statement, but I haven't really been able to get anywhere near
>> something that works...
>> If I run the select statement -
>> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
>> a.zipcode, z.cityname, a.title from user u
>> JOIN address a on u.addressidentold = a.addressident
>> JOIN Zipcode z ON a.zipcode=z.zipcode
>> where u.userinit='spe' ",
>> then it gives the records I want to update. What I then want to do,
>> is to update the field u.userdepartment with a value based on the
>> value of the field "title". Eg. when the field title has the value
>> "IT" then I'd like to set userdepartment = 2959028.
>> I'd think that I can use something like ...userdepartment = CASE
>> title = 'IT' then 2959028... but apparently I need some guidedance
>> on how to do this.
>> Can any of you help with this?
>> Regards
>> Steen|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
> > untested code follows:
> >
> > Does this select statement return what you are looking for?
> >
> > SELECT user, title,
> > userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> > If so, this might be the update statement that you are looking for:
> > UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I need some help on how to update some fields with a value based on
> >> the value in another field. I have tried to do this with a CASE
> >> statement, but I haven't really been able to get anywhere near
> >> something that works...
> >>
> >> If I run the select statement -
> >>
> >> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> >> a.zipcode, z.cityname, a.title from user u
> >> JOIN address a on u.addressidentold = a.addressident
> >> JOIN Zipcode z ON a.zipcode=z.zipcode
> >> where u.userinit='spe' ",
> >>
> >> then it gives the records I want to update. What I then want to do,
> >> is to update the field u.userdepartment with a value based on the
> >> value of the field "title". Eg. when the field title has the value
> >> "IT" then I'd like to set userdepartment = 2959028.
> >> I'd think that I can use something like ...userdepartment = CASE
> >> title = 'IT' then 2959028... but apparently I need some guidedance
> >> on how to do this.
> >>
> >> Can any of you help with this?
> >>
> >> Regards
> >> Steen
>

Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title =
'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steen
untested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title =
> 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>
|||Try Something on these lines:
UPDATE user
SET userdepartment =
CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title =
> 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>
|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
....userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
..
Keith Kratochvil wrote:[vbcol=seagreen]
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
>

Help on this sp, it should be PERFECT!

Hi there, could someone tell me what is the wrong with the below SP's CURSOR.I exec this sp through a table when update a filed value like this :
EXECUTE THROUGH TRIGGER
exec SP_ALOCATE_PAT_CREDIT @.patid, @.creditno, @.totalamount, @.userid, @.sdesc ,@.creDate,'C'

When i run execute this sp, I got this error :
ERROR:
Another user has modified the contents of this table or view;the database row you are modifying no longer exists in the database.Databse error: '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'INVOICE_LIST' does not exist.[Microsoft][ODBC SQL Server driver][SQL Server]The statement has terminated.'

==============================
THANX GUYZ, ALREADY SOLVED IT FINALLY:beer: , ON TODAY EARLY MORNING.WELL, I GUES U GUYS R RITE, I NEED MORE PRACTISE BUT I DID GOOD AT THIS STAGE TOO EVENTOUGH IT IS NOT SO STANDARD..HAHA ,THNX ANYWAY, WILL WORK TO BE BETTER! :angel:Wow. That is horrible.
If you want this to be perfect, you have a helluva long way to go. As a matter of fact, you would be best off scrapping absolutely everything you have done so far and starting again from the beginning. Your whole design is conceptually flawed.
You are using cursors unnecessarily, and you are calling this procedure from a trigger without any reference to what records it should act upon, and no handling for multi-record inserts either.
First, rewrite your procedure using set-based operations instead of a cursor.
Then, read the sections on triggers in Books Online until you understand the purpose and utility of the INSERTED and DELETED virtual tables.|||Thanx for ur concern BLINDMAN, As for my level, i beleive this is what I can output as a totaly new to sql world.i need this to be set up as soon as posible.With minimum guide and help i gues this is what i can come so far.So i hope a guide or few examples would be greatly helpful rather thehn ur advise.thanx Mr BLINMAN eventhough u werent help me much|||Sorry tommy boy - you might not like it much but blindman is right. Apart from it being a flawed idea in the first place the execution is all wrong too. As a self confessed newby you would be well advised to read his post and think seriously about where to go from here. There are no hints, tips or tricks to sort it out - you need a wholesale redesign.|||Oh Than Poo*, Then I shall consider it. God bless america ;)|||God bless them indeed.|||The truth hurts sometimes, TommyBoy, but the truth is what I gave you and nothing but the truth.
Better you know now that you have been heading down the wrong path, and that this is going to take some time and effort for you to implement.
On the plus side, if you take the time to explain WHAT you are trying to do, and you are willing to listen to people on this forum, then we can give you some good advice on designing and coding your process. Its OK to be a noob. We don't mind noobs on the forum. We do have little patience with people who want free advice and then insist on doing things wrong. That is just a waste of our time.
So post a new thread desribing your situation and ask for some help on engineering the process.

Help on Tables

Is there a link that has good documentation on how to create tables?
Like I'm trying to put one value in a cell like:
=IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
but it will create additional rows, because there are multiple periods.If you do not use an aggregate you will get one report row per table row...
Will Sum, or AVG, etc do what you wish...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> Is there a link that has good documentation on how to create tables?
> Like I'm trying to put one value in a cell like:
> =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
>
> but it will create additional rows, because there are multiple periods.
>|||Yeah it still doesn't work:
=SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
=SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
There are 3 periods: Period 1, Period 2 and Total that come back in the
query
The values are right but it repeats in my table
Period
Total Period 1
NETSUB 3000 1000
NETSUB 3000 1000
NETSUB 3000 1000
I just want it to appear 1 time.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> If you do not use an aggregate you will get one report row per table
row...
> Will Sum, or AVG, etc do what you wish...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > Is there a link that has good documentation on how to create tables?
> >
> > Like I'm trying to put one value in a cell like:
> > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> >
> >
> >
> > but it will create additional rows, because there are multiple periods.
> >
> >
>|||From your description, it sounds like all of your fields are in the detail
row.
If this is correct, you can supress the duplicates by adding a details group
and set the group expression to use the period.
I have added a sample report to the end of this posting that demonstrates
how to use a table details group. To run it you will need access to the
Northwind sample database.
The table details group dialog can be reached from as follows: Table
Properties Dailog : Groups Tab : Details Grouping Button.
For a fuller discussion about table groups see the 'How to add a group to a
table' topic in Reporting Services Books Online.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Clark" <daveclark@.hotmail.com> wrote in message
news:usF7VwsXEHA.3292@.TK2MSFTNGP09.phx.gbl...
> Yeah it still doesn't work:
> =SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
> =SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
> There are 3 periods: Period 1, Period 2 and Total that come back in the
> query
> The values are right but it repeats in my table
> Period
> Total Period 1
> NETSUB 3000 1000
> NETSUB 3000 1000
> NETSUB 3000 1000
> I just want it to appear 1 time.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > If you do not use an aggregate you will get one report row per table
> row...
> > Will Sum, or AVG, etc do what you wish...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> > news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > Is there a link that has good documentation on how to create tables?
> > >
> > > Like I'm trying to put one value in a cell like:
> > > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> > >
> > >
> > >
> > > but it will create additional rows, because there are multiple
periods.
> > >
Table Details Group Sample
========================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="ProductID_DetailsGroup">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="northwind">
<rd:DataSourceID>1a2ecefc-e377-49f4-a355-2c6133f21447</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>northwind</DataSourceName>
<CommandText>SELECT Orders.OrderDate, [Order Details].Quantity,
[Order
Details].ProductID
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY [Order Details].ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f28ffce0-dff1-4e06-ba21-c683aab528dc</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

help on SELECT

Help on creating correct select query on the following table where customer = multi-race

( that is, customers that have value ‘1’ on more than one race category)

Thanks!

CustomerID

Black

AmIndian

Asian

White

PacIslander

Hispanic

NoRaceDisc

32501

1

1

32677

1

35062

1

1

35261

1

36490

1

41026

1

41412

1

42488

1

1

1

45471

1

47083

1

1

50066

1

Okay, first off, you should probably change your table structure if you get the chance. The way you designed things, you actually have to go through a schema change if you ever want to add a new race. I suggest you go to a table structure that has a table for race types, and another table which contains your customer_id and the race_id. In this case, your query would look something like this:

select customer_id, count(*)
from customer_races
group by customer_id
having count(*) > 1

In your current table structure, it gets a lot more complicated. If you cannot change the table structure, I'd suggest you follow a method similar to the one I outlined above. Create a temp table / table variable with the following structure...

declare table @.customer_race_count (
customer_id int, race_count int)

Then, you'll have to construct a series of statements like this...

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where black is not null

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where white is not null

then do something similar to this...

select customer_id, count(*)
from race_count
group by customer_id
having count(*) > 1

I hope that helps!

|||oh yeah, if you're using sql 2005, you might be able to take advantage of pivot / unpivot, but I've been working all day and no longer have the brain power to conjure some sample code for that.|||

well, if the race category is a numerical value (ie int) of some sort, this would work:

select CustomerID from MyTable
where (coalesce(Black,0) + coalesce(AmIndian,0) + coalesce(Asian,0) + coalesce(White,0) + (PacIslander,0) + (Hispanic,0)) > 1

If this is 2005, you may want to look at PIVOT

|||

First off, I agree completely with the person who said change the table structure. This should be a very easy query, but it isn't like you have it.

Second, if not numbers, or values aren't actually null, just change to something like

case when Black = '1' then 1 else 0 end +
case when AmIndian = '1' then 1 else 0 end + ...

and you can handle any datatype

|||

Thanks to all that responded.
I did change the structure. Created RaceTypeID 1(White),2(Black),3(Asian),4(AmIndian),and 5(Hispanic). a customer can supply more than 1 race type id so in the race table there can be multiple instances of rows with the same customerid but different race type id.

now i run a query as follows:

SELECT DISTINCT
dbo.Customers.CustomerID
,CASE WHEN EXISTS
(SELECT DISTINCT
dbo.Customers.CustomerID,
COUNT(*)
FROM dbo.Customers
INNER JOIN dbo.Race ON dbo.Customers.CustomerID = dbo.Race.CustomerID
GROUP BY dbo.Customers.CustomerID
HAVING COUNT (*) > 1) THEN 'yes' ELSE 'no' END as MultiRace
FROM dbo.Customers
WHERE
dbo.Customers.LastName NOT LIKE 'test'
AND dbo.Customers.LastName NOT LIKE 'training%'

in my result set i am getting 'yes' on all customers though only 7 are actually muti-race. Help please?

|||

Try this...

select
Customers.CustomerId,
CASE WHEN d_Race.CustomerID IS NOT NULL THEN 'YES'
ELSE 'NO'
END As MultiRace
FROM Customers
LEFT OUTER JOIN
(select customerid, count(*)
from race
group by customerid
having count(*) > 1) AS d_Race
ON d_Race.Customer_Id = Customers.Customer_Id

|||thank you very much...i only had to assign a column name for the count (*) and i finally was able to get the result i wanted. thanks a lot for all your help and to others who pitched in as well.|||whoops, yeah, you're right. I didn't alias that column. That's what happens when you develop pseudo-code. :) Glad I was able to steer you in the right direction.

help on query

Hi,
My table has a column [account type], some rows have null value, when I
query it to eliminate some account type like accout != 'A', all the rows wit
h
null account won't showup either. I would like all the accounts other than
'A' show. How can I write it? ThanksYou could:
select * from TheTable
where isnull( [account type], ''') <> 'A'
Bryce|||...
Where account type Is Null OR account type <> 'A'
"Jen" wrote:

> Hi,
> My table has a column [account type], some rows have null value, when I
> query it to eliminate some account type like accout != 'A', all the rows w
ith
> null account won't showup either. I would like all the accounts other than
> 'A' show. How can I write it? Thanks|||Try,
select * from your_table
where [account] != 'A' or [account] is null
AMB
"Jen" wrote:

> Hi,
> My table has a column [account type], some rows have null value, when I
> query it to eliminate some account type like accout != 'A', all the rows w
ith
> null account won't showup either. I would like all the accounts other than
> 'A' show. How can I write it? Thanks

Help on filtering dataset issue and do I mean HELP!.

I have the following filter expression on a dataset.
Under the Filter tab on the dataset
Expression:
=iif(CStr(Parameters!Bill_ID.Value) <> "All",
CStr(Fields!Bill_ID.Value) = CStr(Parameters!Bill_ID.Value),True)
Operator:
=
Value:
True
My report parameter Bill_ID is String datatype.
My field value Bill_ID from my dataset is a varchar(15).
Yet, when I preview the report with a legitmate parameter value I
receieve this error message.
"The processing of filter expression for "data set" cannot be
performed. The comparison failed. Please check the data type returned
by the filter expression.
How can I get this to work. I spent hours trying different
combinations. What am I doing wrong!! Please respond.Add "=" in front of the True to make it a boolean instead of a string
literal:
=True
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107572335.620117.23360@.o13g2000cwo.googlegroups.com...
> I have the following filter expression on a dataset.
> Under the Filter tab on the dataset
> Expression:
> =iif(CStr(Parameters!Bill_ID.Value) <> "All",
> CStr(Fields!Bill_ID.Value) = CStr(Parameters!Bill_ID.Value),True)
> Operator:
> => Value:
> True
> My report parameter Bill_ID is String datatype.
> My field value Bill_ID from my dataset is a varchar(15).
> Yet, when I preview the report with a legitmate parameter value I
> receieve this error message.
> "The processing of filter expression for "data set" cannot be
> performed. The comparison failed. Please check the data type returned
> by the filter expression.
> How can I get this to work. I spent hours trying different
> combinations. What am I doing wrong!! Please respond.
>|||Thank you Rob very much. I was going crazy trying to figure this out
on Friday.sql

help on alias...

Dear friends and guru:
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyA) This is the wrong group for this request, it is the Reporting Services
group.
B) Don't use aliases, they don't work on inserts. Since Insert only inserts
into a single table, the tablename prefix is totally unneccesary.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:C3EFCB25-67CF-4FED-BFE4-D9A7EB7989A4@.microsoft.com...
> Dear friends and guru:
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
>

Help needed with Xquery

Hello,

I'm trying to retreive the values from multiple nodes based on the value of another , without any success. The XML source is stored in an SQL(2005) xml column .

'Sample XML

<!--Combat Flight Sim mission-->

<Mission>

<Params Version="3.0" Directive="nothing" Country="Britain" Aircraft="p_51b" Airbase="brod23" Date="8/10/1940" Time="12:00" Weather="scatteredclouds3.xml" Multiplayer="y" MultiplayerOnly="n" />

.......

<AirFormation ID="6003" Directive="nothing" Country="Britain" Skill="1" FormType="diamond">

<Unit ID="9459" Type="p_51b" IsPlayer="y" Skill="1" />

<Unit ID="9460" Type="p_51b" Skill="2" />

.........

<AirFormation ID="6000" Directive="nothing" Country="Britain" Points="2" DamagePercent="40" Skill="2" Payload="2" FormType="box">

<Unit ID="9467" Type="b_25c" Skill="2" Payload="3" />

<Unit ID="9468" Type="b_25c" Skill="2" Payload="3" />

.........

AirFormation ID="6007" Directive="nothing" Country="Germany" Skill="2" FormType="fingertip">

<Unit ID="9475" Type="bf_109g_6" Skill="2" Payload="6" />

<Unit ID="9476" Type="bf_109g_6" Skill="2"

'This is the SQL code:

SELECT DISTINCT nref.value('@.Type', 'varchar(100)') Aircraft

FROM dbo.MOG_Missions CROSS APPLY xmlData.nodes('//AirFormation/Unit') as T(nref)

WHERE id = @.id 'some additional condition here is needed but I cannot figure it out

Which returns the following values from the ?Type attribute :

b_25c
bf_109g_6
p_51b

What I would like to accomplish is to return only the values from ?Type where the AirFormation-Country attribute matches the ?Country attribute of the ?Params node.

Thank you in advance.

Your XML sample is not clear to me. What is the relationship between the Params element and the AirFormation elements? If that is known then you should simply be able to express the condition in an XPath predicate in your nodes call. For example if the Params element is a sibling of the AirFormation elements then you can check e.g.

Code Snippet

SELECT DISTINCT t.u.value('@.Type', 'nvarchar(10)') AS Type

FROM example1

CROSS APPLY xml.nodes('//AirFormation[@.Country = ../Params/@.Country]/Unit') AS t(u)

WHERE id = 3;

|||I should have asked for help sooner! Thank you so much!

Help Needed For reporting service 2005(Regarding default date value for report parameter)

Hi experts
I am working on sql server reporting services 2005.
I am using Date time control for my report parameter. And default
value i given =Today(). This is working fine.
But i want some days previous date for default value and when I am
writting Today()-1, Report is giving error.
So can any body tell me how i can do this for defalut value. I want to
give one month previous date as default value.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can
install these reports. If yes then how can i make instalable file of
my this solution.
Any help will be gratefull.
Regards
DineshOn Oct 23, 9:38 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> I am using Date time control for my report parameter. And default
> value i given =Today(). This is working fine.
> But i want some days previous date for default value and when I am
> writting Today()-1, Report is giving error.
> So can any body tell me how i can do this for defalut value. I want to
> give one month previous date as default value.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can
> install these reports. If yes then how can i make instalable file of
> my this solution.
> Any help will be gratefull.
> Regards
> Dinesh
Hi!
You can try it;
(Date = DATEADD(Day, - 1, GetDate())
Hope this helps
Regards
Shima

Help me resolve this error."Missing parameter field current value." Code Attach

Hi ,
am getting this error."Missing parameter field current value." When i Use this line of code
crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues() it says "ApplyCurrentValues is not a member of CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition".How do i get rid of this error.
Also is there anyway that without Exporting , i Can Print the Report from vb.net application directly using default Printer.
Code is:
Private Sub Btn_Export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Export.Click
Try
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = "WASA00150"
.DatabaseName = "iCalls"
.UserID = "sa"
.Password = "courage"
End With
Me.CrystalReportViewer1.ParameterFieldInfo.Clear()
If Me.txtSTdate.Text.Trim.Length > 0 Then
Me.CrystalReportViewer1.ReportSource = Server.MapPath("iCalls_CrystalReport_Department.rpt")
Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
Dim Per As New ParameterField
Per.ParameterFieldName = "Period"
Dim Period_Value As New ParameterRangeValue
Period_Value.StartValue = Me.txtSTdate.Text
Period_Value.EndValue = Me.txtCLdate.Text
Per.CurrentValues.Add(Period_Value)
ParamFields.Add(Per)
End If
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
Me.CrystalReportViewer1.RefreshReport()
CrystalReportViewer1.Visible = True
Dim exportPath As String = "D:\SampleCrystalReports\iCalls_Export\test1.pdf"
Dim crExportOptions As ExportOptions
Dim crDestOptions As New DiskFileDestinationOptions
crDestOptions.DiskFileName = exportPath
crExportOptions = crReportDocument.ExportOptions
crExportOptions.DestinationOptions = crDestOptions
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()
crReportDocument.Export()
Catch ex As Exception
lblmsg.Text = ex.Message.ToString
End Try
End Sub
Many Thanks.Not sure if this is it, but looking at the code this line.

'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()

has a ' at the begining, is that supposed to be there ?

help me in retrieving the value

Hi,

thanks for my first post...this is my problem
i have two tables a, b
i wanted to compare the value frm a with b but the probs is the table doesnt repesent the full value in b

eg...table a has value sav
able b has value savyyy

i need to pull this savyyyy frm b table but i dont want to like this
select col_tablea, col_tableb from tablea, tableb where
col_tablea like col_tableb%

this doesnt work i know its wrong without usng
select col_tablea, col_tableb from tablea, tableb where
col_tablea like sav% (i wanted to specify the colname instaead of the word, is it possible)

help me pleaseIs it one to one relationship?|||select col_a,col_b from savvy,savvy1 where savvy1.col_b like savvy.col_a + '%'|||hi ,

thanks a in tons, it really worked..........it was too helpful
thanks once again