Showing posts with label statement. Show all posts

Friday, March 30, 2012

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

Help on update statement

We have the table called Organization. In this table is a
column called OutlineNum that indicates the hierarchy of
the organizations. This table also includes a flag to
indicate whether or not the organization is active. A
user can inactivate an organization, but ONLY when its
child(ren) are inactivated also. Since our apps code did
not do this at the time, we now may have bad data out
there.
Can anyone out there help me out on creating an update
statement to fix the data based on the OutlineNum and
ActiveFlag values? Below is the DDL for the Organization
table.
create table Organization
(
OrganizationID nvarchar(15) not null,
OrganizationName nvarchar(30) not null,
ActiveFlag int not null
default 1
constraint CK_Organization_ActiveFlag check
(ActiveFlag in (1,0)),
OutlineNum nvarchar(60) not null,
constraint PK_Organization primary key (OrganizationID)
)
go
Below is some sample data. You will have to tweak it to
put bad data in.
insert into Organization
values ('ORG1000', 'Organization - ORG1000',1,'1')
insert into Organization
values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
insert into Organization
values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
insert into Organization
values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
insert into Organization
values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
insert into Organization
values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
insert into Organization
values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
insert into Organization
values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
insert into Organization
values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
insert into Organization
values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
insert into Organization
values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
insert into Organization
values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
insert into Organization
values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
insert into Organization
values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
Thanks in advance,
Dee
Dee,
I just spent a few mins but don't have a concrete solution right now.
Just FYI I created 2 SQLs to get my feet wet into the direction I was going.
I'm sure you will figure out what I'm doing with these SQLs.
I'm at work right now and don't want to spend any further time on this.
Will try and do it from home for ya.
Rgds,
Harman Sahni
select m.outlinenum as moutlinenum,
c.outlinenum as coutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where m.outlinenum = substring(c.outlinenum,1,len(m.outlinenum))
order by 1,2
select c.outlinenum as coutlinenum,
m.outlinenum as moutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where substring(c.outlinenum,1,len(c.outlinenum)-2) = m.outlinenum and
len(c.outlinenum) > 2
order by len(c.outlinenum), len(m.outlinenum), 1,2
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee
|||Oh by the way, in my SQLs
c is for children
m is for master
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee
|||Hello Dee
Not knowing finer details of what you are wanting to achieve, I have put
togather a basic cursor that may achieve this for you. Be warned that
running the UPDATE statement(s) outside of a transaction may end up
updating data that is not intended to be updated. I would recommend for you
to back the database up before running any of the commands from the script
below.
--BEGIN TRAN
declare @.active char(1)
declare @.inactive char(1)
declare @.outline varchar(20)
set @.active = 1 --define the value for which you would like to delete
set @.inactive = 0
declare c1 cursor for
select distinct outlinenum
from organization
where activeflag = @.active and len(outlinenum) = 3 --assuming that parent
that was mistakenly updated has length of 3 eg. 1.1 is considered the
parent.
open c1
fetch next from c1 into @.outline
while @.@.fetch_status = 0
begin
print 'update organization set outlinenum = ' + '''' + @.inactive + '''' +
'where outline like ' + '''' + @.outline + '%' + '''' --check the statements
to see if this meets the goals of what you want to do
--update organization set outlinenum = @.inactive where outline like
@.outline + '%'
fetch next from c1 into @.outline
end
close c1
deallocate c1
--COMMIT TRAN
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thank you both Harman and Pankaj! You both definitely
pointed me to the right direction. Both of your ideas
worked like a charm.

>--Original Message--
>Dee,
>I just spent a few mins but don't have a concrete
solution right now.
>Just FYI I created 2 SQLs to get my feet wet into the
direction I was going.
>I'm sure you will figure out what I'm doing with these
SQLs.
>I'm at work right now and don't want to spend any further
time on this.
>Will try and do it from home for ya.
>Rgds,
>Harman Sahni
>
>select m.outlinenum as moutlinenum,
> c.outlinenum as coutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where m.outlinenum = substring(c.outlinenum,1,len
(m.outlinenum))
>order by 1,2
>select c.outlinenum as coutlinenum,
> m.outlinenum as moutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where substring(c.outlinenum,1,len(c.outlinenum)-2) =
m.outlinenum and
>len(c.outlinenum) > 2
>order by len(c.outlinenum), len(m.outlinenum), 1,2
>
>"dee" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:9bf801c49757$031a9840$a601280a@.phx.gbl...
is a[vbcol=seagreen]
did[vbcol=seagreen]
Organization[vbcol=seagreen]
null,[vbcol=seagreen]
null,[vbcol=seagreen]
null[vbcol=seagreen]
null,[vbcol=seagreen]
(OrganizationID)
>
>.
>

Help on update statement

We have the table called Organization. In this table is a
column called OutlineNum that indicates the hierarchy of
the organizations. This table also includes a flag to
indicate whether or not the organization is active. A
user can inactivate an organization, but ONLY when its
child(ren) are inactivated also. Since our apps code did
not do this at the time, we now may have bad data out
there.
Can anyone out there help me out on creating an update
statement to fix the data based on the OutlineNum and
ActiveFlag values? Below is the DDL for the Organization
table.
create table Organization
(
OrganizationID nvarchar(15) not null,
OrganizationName nvarchar(30) not null,
ActiveFlag int not null
default 1
constraint CK_Organization_ActiveFlag check
(ActiveFlag in (1,0)),
OutlineNum nvarchar(60) not null,
constraint PK_Organization primary key (OrganizationID)
)
go
Below is some sample data. You will have to tweak it to
put bad data in.
insert into Organization
values ('ORG1000', 'Organization - ORG1000',1,'1')
insert into Organization
values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
insert into Organization
values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
insert into Organization
values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
insert into Organization
values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
insert into Organization
values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
insert into Organization
values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
insert into Organization
values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
insert into Organization
values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
insert into Organization
values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
insert into Organization
values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
insert into Organization
values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
insert into Organization
values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
insert into Organization
values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
Thanks in advance,
DeeDee,
I just spent a few mins but don't have a concrete solution right now.
Just FYI I created 2 SQLs to get my feet wet into the direction I was going.
I'm sure you will figure out what I'm doing with these SQLs.
I'm at work right now and don't want to spend any further time on this.
Will try and do it from home for ya.
Rgds,
Harman Sahni
select m.outlinenum as moutlinenum,
c.outlinenum as coutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where m.outlinenum = substring(c.outlinenum,1,len(m.outlinenum))
order by 1,2
select c.outlinenum as coutlinenum,
m.outlinenum as moutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where substring(c.outlinenum,1,len(c.outlinenum)-2) = m.outlinenum and
len(c.outlinenum) > 2
order by len(c.outlinenum), len(m.outlinenum), 1,2
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee|||Oh by the way, in my SQLs
c is for children
m is for master
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee|||Hello Dee
Not knowing finer details of what you are wanting to achieve, I have put
togather a basic cursor that may achieve this for you. Be warned that
running the UPDATE statement(s) outside of a transaction may end up
updating data that is not intended to be updated. I would recommend for you
to back the database up before running any of the commands from the script
below.
--BEGIN TRAN
declare @.active char(1)
declare @.inactive char(1)
declare @.outline varchar(20)
set @.active = 1 --define the value for which you would like to delete
set @.inactive = 0
declare c1 cursor for
select distinct outlinenum
from organization
where activeflag = @.active and len(outlinenum) = 3 --assuming that parent
that was mistakenly updated has length of 3 eg. 1.1 is considered the
parent.
open c1
fetch next from c1 into @.outline
while @.@.fetch_status = 0
begin
print 'update organization set outlinenum = ' + '''' + @.inactive + '''' +
'where outline like ' + '''' + @.outline + '%' + '''' --check the statements
to see if this meets the goals of what you want to do
--update organization set outlinenum = @.inactive where outline like
@.outline + '%'
fetch next from c1 into @.outline
end
close c1
deallocate c1
--COMMIT TRAN
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you both Harman and Pankaj! You both definitely
pointed me to the right direction. Both of your ideas
worked like a charm.
>--Original Message--
>Dee,
>I just spent a few mins but don't have a concrete
solution right now.
>Just FYI I created 2 SQLs to get my feet wet into the
direction I was going.
>I'm sure you will figure out what I'm doing with these
SQLs.
>I'm at work right now and don't want to spend any further
time on this.
>Will try and do it from home for ya.
>Rgds,
>Harman Sahni
>
>select m.outlinenum as moutlinenum,
> c.outlinenum as coutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where m.outlinenum = substring(c.outlinenum,1,len
(m.outlinenum))
>order by 1,2
>select c.outlinenum as coutlinenum,
> m.outlinenum as moutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where substring(c.outlinenum,1,len(c.outlinenum)-2) =m.outlinenum and
>len(c.outlinenum) > 2
>order by len(c.outlinenum), len(m.outlinenum), 1,2
>
>"dee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9bf801c49757$031a9840$a601280a@.phx.gbl...
>> We have the table called Organization. In this table
is a
>> column called OutlineNum that indicates the hierarchy of
>> the organizations. This table also includes a flag to
>> indicate whether or not the organization is active. A
>> user can inactivate an organization, but ONLY when its
>> child(ren) are inactivated also. Since our apps code
did
>> not do this at the time, we now may have bad data out
>> there.
>> Can anyone out there help me out on creating an update
>> statement to fix the data based on the OutlineNum and
>> ActiveFlag values? Below is the DDL for the
Organization
>> table.
>> create table Organization
>> (
>> OrganizationID nvarchar(15) not
null,
>> OrganizationName nvarchar(30) not
null,
>> ActiveFlag int not
null
>> default 1
>> constraint CK_Organization_ActiveFlag check
>> (ActiveFlag in (1,0)),
>> OutlineNum nvarchar(60) not
null,
>> constraint PK_Organization primary key
(OrganizationID)
>> )
>> go
>> Below is some sample data. You will have to tweak it to
>> put bad data in.
>> insert into Organization
>> values ('ORG1000', 'Organization - ORG1000',1,'1')
>> insert into Organization
>> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
>> insert into Organization
>> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
>> insert into Organization
>> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
>> insert into Organization
>> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
>> insert into Organization
>> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
>> insert into Organization
>> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
>> insert into Organization
>> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
>> insert into Organization
>> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
>> insert into Organization
>> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
>> insert into Organization
>> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
>> insert into Organization
>> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
>> insert into Organization
>> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
>> insert into Organization
>> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
>> Thanks in advance,
>> Dee
>
>.
>

Help On Trigger For Delete

I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table.
The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
For each row deleted I need to update a column in another table (PACCESOS_CAB).
This the trigger...

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)

if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
begin
select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
update PACCESOS_CAB set diasvisita = @.mdias -1 where FKFeria= @.mFKFeria and FKContacto=@.mFKContacto and Tipo=@.mTipo
end

Thanks in advanced."Inside every large program is a small program screaming to get out."

Your trigger is MUCH too complicated. Best I can figure, this is all you need:

CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS

begin
update PACCESOS_CAB
set PACCESOS_CAB.diasvisita = PACCESOS_CAB.diasvisita -1
from PACCESOS_CAB
inner join deleted
on PACCESOS_CAB.FKFeria = deleted.FKFeria
and PACCESOS_CAB.fkcontacto = deleted.fkcontacto
and PACCESOS_CAB.Tipo = deleted.Tipo
end|||It worked fine, sinco I keep on not Knowing the problem with the first solution.

anyway, lot of thanks blindman.|||Problems with your first solution:

This phrase is completely unnecessary, as records between the two tables will be matched in the UPDATE query:

"if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)"

These variables are not required, because as you can see from the solution all the records can be updated simultaneously with a single UPDATE statement, so it is not necessary to store values in temporary variables:

"declare @.mdias as int
declare @.mFKFeria as int
declare @.mtipo as char(1)
declare @.mfkcontacto as varchar(7)"

This statement fails when more than one record is deleted, because your FROM clause will return more than one record, and the parameters can hold only single values:

"select @.mfkferia=m.fkferia, @.mfkcontacto = m.fkcontacto, @.mtipo = m.tipo, @.mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo"

Wednesday, March 28, 2012

HELP on SELECT Statement

I have following table record
Year ModelYr Type
1999 1994 2
1999 1999 3
2000 1999 4
2001 2000 2
1999 1996 4
2000 1996 5
2000 1998 3
2001 2001 2
Here are the rules:
1. In display output, it will have the following column name:
Year TypeA TypeB
2. "ModelYr" is equal or less than "Year"
3. if ("Year" - "ModelYr") < 2 then
TypeA = "Type"
else
TypeB = "Type"
3. The output should be "Group by Year Order by Year"
4. For each "Year", the each type will be SUM UP.
So the expected output will be:
Year TypeA TypeB
1999 6 3
2000 7 5
2001 4 0
Could you kindly advise how to make the SELECT statement to achieve the
output above ? I'm really not too sure.
Any help is very much appreciated.
Thank you.
Regards.Something like this? Note that my output came out a little different from
your specified output:
Year | TypeA | TypeB
1999 | 6 | 3
2000 | 5 | 7
2001 | 0 | 4
CREATE TABLE #Widgets ([Year] INT NOT NULL,
ModelYr INT NOT NULL,
Type INT NOT NULL,
PRIMARY KEY ([Year], ModelYr, Type));
INSERT INTO #Widgets([Year], ModelYr, Type)
SELECT 1999, 1994, 2
UNION SELECT 1999, 1999, 3
UNION SELECT 2000, 1999, 4
UNION SELECT 2001, 2000, 2
UNION SELECT 1999, 1996, 4
UNION SELECT 2000, 1996, 5
UNION SELECT 2000, 1998, 3
UNION SELECT 2001, 2001, 2
SELECT [Year], SUM(CASE WHEN [Year] - [ModelYr] > 2 THEN Type ELSE 0 END) AS
TypeA,
SUM(CASE WHEN [Year] - [ModelYr] <= 2 THEN Type ELSE 0 END) AS TypeB
FROM #Widgets
WHERE ModelYr <= [Year]
GROUP BY [Year]
ORDER BY [Year]
DROP TABLE #Widgets
"magix" <magix@.asia.com> wrote in message news:449bef96_2@.news.tm.net.my...
>I have following table record
> Year ModelYr Type
> 1999 1994 2
> 1999 1999 3
> 2000 1999 4
> 2001 2000 2
> 1999 1996 4
> 2000 1996 5
> 2000 1998 3
> 2001 2001 2
> Here are the rules:
> 1. In display output, it will have the following column name:
> Year TypeA TypeB
> 2. "ModelYr" is equal or less than "Year"
> 3. if ("Year" - "ModelYr") < 2 then
> TypeA = "Type"
> else
> TypeB = "Type"
> 3. The output should be "Group by Year Order by Year"
> 4. For each "Year", the each type will be SUM UP.
> So the expected output will be:
> Year TypeA TypeB
> 1999 6 3
> 2000 7 5
> 2001 4 0
>
> Could you kindly advise how to make the SELECT statement to achieve the
> output above ? I'm really not too sure.
> Any help is very much appreciated.
> Thank you.
> Regards.
>

Monday, March 26, 2012

Help on multiple date range on sql statement

Using SQLServer ver 7.0, two tables:
TableA = contains all inventory data
TableB = contains four fields: ID, source, date_from, date_to
This is where multiple range of dates are populated.
Sample 1:
1,'A','9/1/2004','9/30/2004'

Sample 2:
2,'A','1/1/2003','3/31/2003'
3,'A','10/1/2004','10/31/2004'

Data populated on TableB varies.

Sample SQL for Sample 1:
SELECT *
FROM TableA
WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
DATE_TO from TableB)

Problem: How to approach sql statement based on Sample 2 above?B (no_spam@.no_spam.com) writes:
> Using SQLServer ver 7.0, two tables:
> TableA = contains all inventory data
> TableB = contains four fields: ID, source, date_from, date_to
> This is where multiple range of dates are populated.
> Sample 1:
> 1,'A','9/1/2004','9/30/2004'
> Sample 2:
> 2,'A','1/1/2003','3/31/2003'
> 3,'A','10/1/2004','10/31/2004'
> Data populated on TableB varies.
>
> Sample SQL for Sample 1:
> SELECT *
> FROM TableA
> WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
> DATE_TO from TableB)

SELECT *
FROM TableA A
JOIN TableB B ON B.ID = A.ID
WHERE A.inventory_date BETWEEN B.date_from ABD B.date_to

But this is really a guess. If this does not answer your question, please
post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The expected result given the sample data.

That makes it possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 8 Nov 2004 22:40:44 -0500, B wrote:

>Using SQLServer ver 7.0, two tables:
>TableA = contains all inventory data
>TableB = contains four fields: ID, source, date_from, date_to
>This is where multiple range of dates are populated.
>Sample 1:
>1,'A','9/1/2004','9/30/2004'
>Sample 2:
>2,'A','1/1/2003','3/31/2003'
>3,'A','10/1/2004','10/31/2004'
>Data populated on TableB varies.
>
>Sample SQL for Sample 1:
>SELECT *
>FROM TableA
>WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
>DATE_TO from TableB)
>Problem: How to approach sql statement based on Sample 2 above?

Hi B,

If you want it to return all inventory details with an inventory_date
between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
and 10/31/2004, try this query:

SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM TableA AS A
INNER JOIN TableB AS B
ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is exactly solution I needed.

Many thanks for your time!
Bob

> If you want it to return all inventory details with an inventory_date
> between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
> and 10/31/2004, try this query:
> SELECT A.Column1, A.Column2, ..., A.ColumnN
> FROM TableA AS A
> INNER JOIN TableB AS B
> ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

help on login issue

Hi,
I restored a db on a new server. I remeber I got a sql statement or store pr
ocedure to get all "sp_change_users_login" for the db, ie, the query result
is a list of sp_change_users_login statment for each login, so I don't need
to type for every user. But
I can't find it anymore. anyone can help? ThanksHere are some related links:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:08DCEBA2-C07B-4F12-B65A-1ACBB19CAD71@.microsoft.com...
> Hi,
> I restored a db on a new server. I remeber I got a sql statement or store
procedure to get all "sp_change_users_login" for the db, ie, the query
result is a list of sp_change_users_login statment for each login, so I
don't need to type for every user. But I can't find it anymore. anyone can
help? Thanks

help on login issue

Hi,
I restored a db on a new server. I remeber I got a sql statement or store procedure to get all "sp_change_users_login" for the db, ie, the query result is a list of sp_change_users_login statment for each login, so I don't need to type for every user. But I can't find it anymore. anyone can help? ThanksHere are some related links:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:08DCEBA2-C07B-4F12-B65A-1ACBB19CAD71@.microsoft.com...
> Hi,
> I restored a db on a new server. I remeber I got a sql statement or store
procedure to get all "sp_change_users_login" for the db, ie, the query
result is a list of sp_change_users_login statment for each login, so I
don't need to type for every user. But I can't find it anymore. anyone can
help? Thanks

help on login issue

Hi,
I restored a db on a new server. I remeber I got a sql statement or store procedure to get all "sp_change_users_login" for the db, ie, the query result is a list of sp_change_users_login statment for each login, so I don't need to type for every user. But
I can't find it anymore. anyone can help? Thanks
Here are some related links:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:08DCEBA2-C07B-4F12-B65A-1ACBB19CAD71@.microsoft.com...
> Hi,
> I restored a db on a new server. I remeber I got a sql statement or store
procedure to get all "sp_change_users_login" for the db, ie, the query
result is a list of sp_change_users_login statment for each login, so I
don't need to type for every user. But I can't find it anymore. anyone can
help? Thanks
sql

help on join statement

I have two tables:

tblUserData
UserName
UserCode

tblBlogs
UserCode
BlogText

I have an SP which takes the username as a variable.

How can I select all blogtext from tblBlogs where the usercode belonging to the username in tblUserdata is equal to the usercode in tblBlogs?

so select all blogs for a specfic username...

SELECT TB.* FROM tblBlogs TB

JOIN tblUserData TUD ON TB.Usercode = TUD.UserCode

WHERE TUD.UserName = @.UserName

help on join statement

I have table A:
ID int
Name text

And Table B
ID int
Name text

Now, I want to select all records from A where there is no matching record in B based on the ID

I want to do this with a JOIN statement and not a subquery as I understood that the execution plan for JOIN statements is more efficient...

Any help?

Something like this:

select *
from TableA
left outer join TableB
on TableA.ID= TableB.ID
where TableB.ID is null

The inner query does an outer join, all records show up, some with nulls

The outer query gets the records with null

|||

SELECT A.* FROM A LEFT OUTER JOIN B on A.ID = B.ID WHERE B.ID IS NULL

I would strongly advise comparing performance with

SELECT * FROM A WHERE A.ID NOT IN (SELECT ID FROM B)

sql

Friday, March 23, 2012

Help on INI file

Hi all,
I'm building a DTS package that needs to take parameters from an INI
file. The SQL statement in the Transform task goes something like this:
select * from customer where division in ('D','I','2','3','C')
I need to pass the division 'D','I','2','3','C' part in because there
might be more divisions we need in the future or change of divisions,
we don't want to modifiy the package every time. So I set it in the INI
file like this and read it into a global variable:
[Customer]
Division = 'D','I','2','3','C'
But SQL doesn't recognize the SQL statement "select * from customer
where division in (?), where ? stands for the global variable. I tried
with setting Division = "'D','I','2','3','C'" in INI file, it still
doesn't work. The only way it works is to pass the 5 values as 5
seperate parameters and thus 5 global variables then the SQL statement
is like this:
select * from customer where division in (?,?,?,?,?)
But this defeats the purpose because I can't add another parameter in
the INI file without having to open the package and make changes.
Has anybody run into this before? Any suggestions is appreciated.
Thanks,http://www.sommarskog.se/arrays-in-sql.html

Simon

Help on global variable for insert statement

I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.

I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code to do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Here is all the code:

Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing

End Sub

This sentence is confusing: "I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls."

Do you mean that the table fields do not accept NULL values?

If I recall correctly, a textbox cannot contain a NULL value. At the minimum, it contains an 'empty string'.

In your code sniplet, you are inserting the value '1rowst' in the field [Test1], etc. There is no ambiguity about NULL values.

Perhaps I am missing something. Could you please expand upon your request so in order to clear up the confusion?

|||

I am sorry, lack of english.

My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.

My question is how do I format the following line of code do do what I need?

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"

I think it is something like this, but I am Really not sure and some help would be greatly appretiated:

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"

I am really not sure.

Thanks

|||

I'm still confused. Please help me clarify.

You wish to put all the contents of three textboxes into a single field in the table?

"Textbox1, 2, 3 Needs to insert values into the first column"

If that is the situation, why not just have one textbox -not three?

|||

No, sorry Arnie,

Textbox 1,4,7 go in column 1

TextBox 2, 5, 8 Go in column 2

TextBox 3, 6, 9 Go in column 3

I need my application to insert the values into the Database. If there is Nulls in the textbox go to the next record.

I think the code should go something like this:

If Textbox2 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

Elseif Textbox3 = '' then

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"

And

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text "', '" Textbox5.Text "', '" Textbox8.Text"')"

As you can see I am throwing a rock into the grass, because I am not % 100 sure if this is correct.

Thanks for the patience Arnie

|||

If I am understanding you correctly, it appears that you wish to load textbox values into the table depending upon the status of other textbox values.

Your use of the IF...ELSEIF structure may work just fine for your intentions. Something like this might work.

IF Textbox1.Text = '' Then

IF Textbox2.Text = '' Then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox3.Text + "', '" + Textbox6.Text + "', '" + Textbox9.Text + "')"

ELSE
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text + "', '" + Textbox5.Text + "', '" + Textbox8.Text + "')"
END IF

ELSE

oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox1.Text + "', '" + Textbox4.Text + "', '" + Textbox7.Text + "')"

END IF

|||That was exactly what I needed. Thanks Arnie|||Of course you should be careful with this method since this is completely vulnerable to SQL injection attacks.

Just a friendly reminder.|||

Jon,

Thank you very much for that. I was not familiar with that at all. I just want to make sure. This is a big issue with web applications (wich mine is not). Is that correct?

Thanks

|||Yes it is, but it's completely possible from within windows forms applications as well. So it's definitely something to watch out for.

Help on delete statement

Hi,
I have two tables.
Table1:
MySymbol, BloombergSymbol
A, A_Bloomberg
B, B_Bloomberg

Table2:
MySymbol, Open, High, Low, Close
A,...
A,...
B,...
B,...

I want to perform one task--Delete all the records in table2 whose MySymbol matches one given BloombergSymbol in table1.

3x.which dbms is this?|||Try this query:

Delete Table2.* from Table1 LEFT JOIN Table2 ON Table1.MySymbol = Table2.MySymbol|||Alternate syntax :

DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)

n.b. I have based this on MySQL syntax which may or may not work in your DB.|||sql server
which dbms is this?|||this one works.
thank you guys.

Alternate syntax :

DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)

n.b. I have based this on MySQL syntax which may or may not work in your DB.|||For SQL Server use:

DELETE table2
FROM table2
INNER JOIN table1 ontable2.MySymbol = table1.MySymbol

Monday, March 19, 2012

Help needed with SQL UPDATE statement

I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement.....

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @.IDParam"

Thanks!

Eugh

what is the error message?|||The statement looks correct|||Are you sure the problem is with your SQL UPDATE statement? It's obvious that it's part of your program, so I'm wondering if the rest of your code is where the problem lies. Post more codes to see if anyone can find the cause.|||

Sorry to post and run last Friday. Thanks for taking a look at it. I've never written anything in .net for web applications before. I installed IIS and VB.net myself so if the SQL statement looks correct I'm wondering if I have a permissions issue. I gave the ASPNET read/write permissions by right clicking on the DB in windows explorer, properties, security tab, add, (machinename\ASPNET). The DB is in a subfolder under wwwroot. Did I do something wrong there perhaps?

I'm using VB.Net 2003 Standard Edition w/ and Access 2003 DB. My exact error message is...

Server Error in '/Proposal_List' Application.

Syntax error in UPDATE statement.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

Source Error:

Line 112: objConn.Open()Line 113:Line 114: objCmd.ExecuteNonQuery()Line 115: objConn.Close()Line 116:

Source File:c:\inetpub\wwwroot\Proposal_List\Pnumberlist.aspx.vb Line:114

Stack Trace:

[OleDbException (0x80040e14): Syntax error in UPDATE statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) System.Data.OleDb.OleDbCommand.ExecuteNonQuery() Proposal_List.Pnumberlist.dgPnum_UpdateRow(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Proposal_List\Pnumberlist.aspx.vb:114 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()

HERE IS MY COMPLETE CODE

VB............................................................................................................

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

IfNot Page.IsPostBackThen

BindData()

EndIf

EndSub

Sub BindData()

'Create a connection

Const strConnStringAsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\wwwroot\Proposal_List\Proposal_List.mdb;User Id=admin;Password=;"

Dim objConnAsNew OleDbConnection(strConnString)

'create a command object for the query

Const strSQLAsString = "SELECT * FROM [tbl-Pnumber_list]"

Dim objCMDAsNew OleDbCommand(strSQL, objConn)

'create a dataadapter

Dim objDAAsNew OleDbDataAdapter

objDA.SelectCommand = objCMD

'Populate a dataset and close the connection

Dim objDSAsNew DataSet

objDA.Fill(objDS)

objConn.Close()

'specify the data source and bind the data

dgPnum.DataSource = objDS

dgPnum.DataBind()

EndSub

Sub dgPnum_EditRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

dgPnum.EditItemIndex = e.Item.ItemIndex

BindData()

EndSub

Sub dgPnum_UpdateRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

'get info from columns

Dim PMTextBoxAs TextBox = e.Item.Cells(3).Controls(0)

Dim Project_TitleTextBoxAs TextBox = e.Item.Cells(2).Controls(0)

Dim iIDAsInteger = dgPnum.DataKeys(e.Item.ItemIndex)

'update the database

Dim strSQLAsString

'strSQL = "UPDATE tbl-Pnumber_list SET Project_Manager = @.PMParam," & _

'"Project_Title = @.Project_TitleParam" & _

'"WHERE ID = @.IDParam"

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @.IDParam"

Const strConnStringAsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Inetpub\wwwroot\Proposal_List\Proposal_List.mdb;User Id=admin;Password=;"

Dim objConnAsNew OleDbConnection(strConnString)

Dim objCmdAsNew OleDbCommand(strSQL, objConn)

Dim PMParamAsNew OleDbParameter("@.NameParam", OleDbType.VarChar, 200)

Dim Project_TitleParamAsNew OleDbParameter("@.CommentParam", OleDbType.VarChar, 254)

Dim IDParamAsNew OleDbParameter("@.IDParam", OleDbType.Integer, 4)

PMParam.Value = PMTextBox.Text

objCmd.Parameters.Add(PMParam)

Project_TitleParam.Value = Project_TitleTextBox.Text

objCmd.Parameters.Add(Project_TitleParam)

IDParam.Value = iID

objCmd.Parameters.Add(IDParam)

'Issue the SQL command

objConn.Open()

objCmd.ExecuteNonQuery()

objConn.Close()

dgPnum.EditItemIndex = -1

BindData()

EndSub

Sub dgpnum_CancelRow(ByVal senderAsObject,ByVal eAs DataGridCommandEventArgs)

dgPnum.EditItemIndex = -1

BindData()

EndSub

HTML....................................................................................................................................

Thanks again!

Cheers

Eugh

|||

OleDB doesn't understand the @. parameters, change them to ? instead.

WHERE something=?

|||

I'm not familiar with the way you're providing the UPDATE parameters in your code. Also, I use C#, so the syntax may be different. Following is a sample code from what I used to do in ASP.NET 1.x. Maybe you can get some ideas from it and translate it to your code. Just keep in mind that with OLE-db (Access), the order of parameter specified in your UPDATE statement is all that matters, not the actual placeholder specified:

OleDbCommand myCmd =new OleDbCommand();

myCmd.Connection = oleDbConnection1;

myCmd.CommandText ="UPDATE TableName SET Date_Upd = ?, Name_Last = ? WHERE Rec_ID = ?";

myCmd.Parameters.Add("Date_Upd", OleDbType.Date).Value =DateTime.Now;

myCmd.Parameters.Add("Name_Last", OleDbType.Char).Value = Name_Last.Text;

myCmd.Parameters.Add("Rec_ID", OleDbType.Integer).Value = Session["REC_ID"].ToString();

myCmd.CommandType =CommandType.Text;

myCmd.Connection.Open();

myCmd.ExecuteNonQuery();

myCmd.Connection.Close();

|||

Good to know that Access doesnt understand the @. parameters, I would have been stuck there for a while. Just to see my DB update I changfed the UPDATE statement to....

strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = 1"

I didnt change anything else, should that have worked? It didnt.

Also, thanks for the example it helps clear some things up for me.

|||Looks okay, assuming your ID is a numeric field. Also, I'm not 100% sure about using single-quote versus double-quote when you specify a text within Access SQL statement. You can probably play with that to verify.|||Try the same statement again with tbl-Pnumber_list in []'s, `'s, or "'s (I'm not sure how Access quotes it's identifiers). The - causes some RDMSs a fit because, well... It's hard to know you aren't trying to say something strange like... tbl (minus) Pnumber_list, which well, doesn't make a lot of sense.|||

Yeah, wadda know... putting [] around tbl-Pnumber_list worked like a champ. Man it feels great to get outta this rut, but it sure hits ya in the gut knowing how simple the solution was. I'll just make sure I wont tell my boss what it was Smile [:)]

Thanks to both of you!

Remember me, because I'm sure I'll be begging for some help again soon.

Cheers!

Eugh

Help Needed With Simple Case Statement in SQL

Hello,

I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value.

Right now if the value is anything other than 1 or 0, it will leave the cell blank.

CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus

Thank You.

Try something like this:

CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE CAST(dbo.Training.TrainingStatus AS varchar(20)) END AS TrainingStatus

Help needed with insert Statement

Hi,

I am trying to insert the follows rows to my production database... and this the sample data

RowPlan PART_ID FUND_ID TOT_ACT1 TOT_ACT2 Number Num1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79 2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27 3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00 4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59 5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79 6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48 7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16 8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80 9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94

So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows

1INSERT INTO Statements..ParticipantPlanFundBalances12(3PlanId,4ParticipantId,5PeriodId,6FundId,7 Loans,8--PortfolioId,9Act1,10TotAct1,11Act2,12TotAct2,13Act3,14TotAct3,15Act4,16TotAct4,17Act5,18TotAct5,19Act6,20TotAct6,21Act7,22TotAct7,23Act8,24TotAct8,25Act9,26TotAct9,27Act10,28TotAct10,29Act11,30TotAct11,31Act12,32TotAct12,33Act13,34TotAct13,35Act14,36TotAct14,37Act15,38TotAct15,39Act16,40TotAct16,41Act17,42TotAct17,43Act18,44TotAct18,45Act19,46TotAct19,47Act20,48TotAct20,49OpeningUnits,50OPricePerUnit,51ClosingUnits,52CPricePerUnit,53AllocationPercent54)55SELECT56cp.PlanId,57p.ParticipantId,58@.PeriodId,59CaseWhen a.FUND_ID <>'LOAN'Then f.FundIdELSE 0END,60CASEWhen a.FUND_ID ='LOAN'Then'LOAN'END as Loanfunds,61--planinfo.PortfolioId,62CaseWHEN a.ACT_ID1 ='BB'Then 1END,63a.TOT_ACT1,64CaseWHEN a.ACT_ID2 ='CT'Then 2END,65a.TOT_ACT2,66CASEWhen a.ACT_ID3 ='DV'then 3END,67a.TOT_ACT3,68CASEWhen a.ACT_ID4 ='GL'Then 4End,69a.TOT_ACT4,70CAseWhen a.ACT_ID5 ='TF'THEN 5END,71 a.TOT_ACT5,72CASEWhen a.ACT_ID6 ='WD'THEN 6END,73a.TOT_ACT6,74CASEWHEN a.ACT_ID7 ='OT'THEN 7END,75a.TOT_ACT7,76CASEWhen a.ACT_ID8 ='EB'THEN 8END,77a.TOT_ACT8,78a.ACT_ID9,79a.TOT_ACT9,80a.ACT_ID10,81a.TOT_ACT10,82a.ACT_ID11,83a.TOT_ACT11,84a.ACT_ID12,85a.TOT_ACT12,86a.ACT_ID13,87a.TOT_ACT13,88a.ACT_ID14,89a.TOT_ACT14,90a.ACT_ID15,91a.TOT_ACT15,92a.ACT_ID16,93a.TOT_ACT16,94a.ACT_ID17,95a.TOT_ACT17,96a.ACT_ID18,97a.TOT_ACT18,98a.ACT_ID19,99a.TOT_ACT19,100a.ACT_ID20,101a.TOT_ACT20,102a.UNIT_OP,103a.PRICE_OP,104a.UNIT_CL,105a.PRICE_CL,106IsNull(i.ALLOC_PER1,'0.00')107FROM108ASDBF a109110--Derive the unique Plan Id111INNERJOIN Statements..ClientPlan cp112ONa.PLAN_NUM = cp.ClientPlanId113AND114cp.ClientId = @.ClientId115--Derive the unique ParticipantId from the Participant table116INNERJOIN Statements..Participant p117ONa.PART_ID = p.PartId118-- Derive the unique fund id from the Fund Table119INNERJOIN Statements..Fund f120ONa.FUND_ID = f.Cusip121OR122a.FUND_ID = f.Ticker123OR124a.FUND_ID = f.ClientFundId125LeftOuter JOIN INVSRC i126ONa.FUND_ID = i.INV_ID127AND128a.PLAN_NUM = i.Plan_Number129AND130a.PART_ID = i.PART_ID131--Get the unique portfolio name ffor the PArticipant Funds..132WHERE133--Ignore rows that failed the scrub.134a.Import = 1135AND136--Import only those that are not already in the ParticipantPlanFundBalances table137NOT EXISTS (138SELECT *139FROM140Statements..ParticipantPlanFundBalances1 pfb141WHERE142pfb.PlanId = cp.PlanId143AND144pfb.ParticipantId = p.ParticipantId145AND146pfb.PeriodId = @.PeriodId147AND148pfb.FundId = f.FundId149)

any help is appreciated.

Regards

Karen

Is there any error msg? Also can you explain this:

INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId

|||

Thanks for your answer, no i am not getting any error message

INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId
and this one means... i am getting the fundId from that table and inserting it to the PlanFundbalances..

for example in the sample data i have provided.. FUND_ID can be a 5 letter word saying DODGX,(Ticker) or some alphanumeric data whose length is and ClientFund(what ever the client wants and not in our database)

So fUND_ID 19765P471 will have a fundId of 15 or whatever..

But the word Loan isnt there in the Statements..Fund f table

Hope this helps.

Regards

Karen

|||

The way to debug would be to selectively comment out lines.. comment out the INSERT INTO line and just run the SELECT part. Start with the first ASDBF JOIN with ClientPlan and see if you get results. Then include the join with Participant and see if you get expected results..keep including each of the tables and see which part of the query is throwing you off. Otherwise there's really no way for us to tell what the issue is.. unless we see some sample data from each of the tables in the query and expected data into the final table...

|||

This is the first 10 rows of my final table..

1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.009178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0010178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00

Suppose if PartID 18752 had loans i want the information to be displayed like in Line number 9

1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.00917824 18752 7 0 LOAN other columns10178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0011178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00

I will try debugging the sproc and see what i can acheive

Regards,

Karen

|||

ndinikar hit it spot on. Are there any records in the ASDBF table that have a FUND_ID ='LOAN'?

If not, that's your problem.

If yes, then one of the joins you have is filtering them out.

|||

Yes i do have 18 rows of Data where FUND_ID = 'LOAN'

|||

after debugging it...

When i include this Join

JOIN Statements..Fund f

ON a.FUND_ID= f.Cusip

a.FUND_ID= f.Ticker

a.FUND_ID= f.ClientFundId

i am getting a problem and i solved it by giving

LeftOuterJOIN Statements..Fund f

ON a.FUND_ID= f.Cusip

a.FUND_ID= f.Ticker

a.FUND_ID= f.ClientFundId

Thanks a lot...

Regards

Karen

Friday, March 9, 2012

Help needed creating select statement

Hi,

I have a need to create a table detailing the ID of all contacts and the
last time they were contacted. This information is stored in 2 tables,
'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
in the 'activity' table).

I guess I need some sort if iteration to go through each contact and find
find the last activity that took place against each of them (there many be
more than 1 activity against each contact) and then place the output values
into the new table.

Can anyone show me how to go about this?

Thanks!This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.

SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)

Roy Harvey
Beacon Falls, CT

On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a need to create a table detailing the ID of all contacts and the
>last time they were contacted. This information is stored in 2 tables,
>'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
>in the 'activity' table).
>
>I guess I need some sort if iteration to go through each contact and find
>find the last activity that took place against each of them (there many be
>more than 1 activity against each contact) and then place the output values
>into the new table.
>
>Can anyone show me how to go about this?
>
>Thanks!
>

|||Hi Roy,

Many thanks. I've managed to use your example to get exactly what I need.
Cheers!

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:7240031r1ken2gmb5a3qe8gfost4nvma25@.4ax.com...

Quote:

Originally Posted by

This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.
>
SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)
>
Roy Harvey
Beacon Falls, CT
>
On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:
>

Quote:

Originally Posted by

>>Hi,
>>
>>I have a need to create a table detailing the ID of all contacts and the
>>last time they were contacted. This information is stored in 2 tables,
>>'contact' and 'activity' (ID in the 'contact' table links to
>>'main_contact'
>>in the 'activity' table).
>>
>>I guess I need some sort if iteration to go through each contact and find
>>find the last activity that took place against each of them (there many be
>>more than 1 activity against each contact) and then place the output
>>values
>>into the new table.
>>
>>Can anyone show me how to go about this?
>>
>>Thanks!
>>

Friday, March 30, 2012

Help on using CASE together with UPDATE

Help on using CASE together with UPDATE

Help on update statement

Help on update statement

Help On Trigger For Delete

Wednesday, March 28, 2012

HELP on SELECT Statement

Monday, March 26, 2012

Help on multiple date range on sql statement

help on login issue

help on login issue

help on login issue

help on join statement

help on join statement

Friday, March 23, 2012

Help on INI file

Help on global variable for insert statement

Help on delete statement

Monday, March 19, 2012

Help needed with SQL UPDATE statement

Server Error in '/Proposal_List' Application.

Syntax error in UPDATE statement.

Help Needed With Simple Case Statement in SQL

Help needed with insert Statement

Friday, March 9, 2012

Help needed creating select statement

sql server codes

Blog Archive

About Me