I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.yes it can be done through a query, but the standard sql for it will almost certainly not work in whatever database system you're using
date functions are notoriously non-standard, and each database system pretty much has its own proprietary functions
if you wouldn't mind mentioning which database system you're using, we could probably help you|||Nevermind I figured it out. I am using a SQL server 2000 database to query my data. I used the Datediff() function in conjunction with getdate(). See examples below for anyone else needing help with this topic.
DATEDIFF([day], dbo.table.field, GETDATE()) AS DAYS,
DATEDIFF([HOUR], dbo.table.field, GETDATE()) AS HOURS|||moved to SQL Server forum|||The solution you arrived at will work, but will not benefit from any indexing on your dbo.table.field column. If, instead, you used the DateAdd() function to find the date five days prior to the current date, then the optimizer will be able to use an index on dbo.table.field for comparisons:Where dbo.table.field >= DateAdd(day, -5, GetDate())
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Need to acess a field in a dataset from the other dataset.
This is a multi-part message in MIME format.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the reports. =
In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. querying = Web services through the XML data provider).So I am able to get the data = into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use = fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter = @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, = "DecryptedAddressDS")) but getting error as "Fields cannot be used in = query parameter expressions".
Are there other ways to acess a field in a dataset from the other = dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive = Advantage. Persistent Systems provides custom software product = development services. With over 15 years, 140 customers, and 700+ = release cycles experience, we deliver unmatched value through high = quality, faster time to market and lower total costs.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I am using MS SQL Server 2005 Reporting = Services to develop the reports.
In my report I will be using 2 datasets = say A and B.
Dataset A connects to the XML type Data = Source Provider(i.e. querying Web services through the XML data provider).So I = am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server = Provider. Now I want to use fields from the dataset A into the query of the = dataset B.
I tried to access the fields of the = dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but getting = error as "Fields cannot be used in query parameter expressions".
Are there other ways to acess a = field in a dataset from the other dataset.
Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 2226900(Off) = Extn: 7026
Mob : 099605 93699www.persistentsys.com = Persistent Systems -Software Development Partner for Competitive Advantage. Persistent Systems provides custom software product development = services. With over 15 years, 140 customers, and 700+ release cycles experience, = we deliver unmatched value through high quality, faster time to market and = lower total costs.
--=_NextPart_000_0017_01C74FB3.C419E480--This is a multi-part message in MIME format.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You can only use aggregate functions outside a dataset (min, max, =first). So unless the field doesn't change value (in which case you can =use first) you cannot do this the way you want. However, if I understand =what you are trying to do you can do this with subreports. Turn the =dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a =right mouse click on the subreport, parameters and then map the =parameter to the field in dataset A.
-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the =reports. In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. querying =Web services through the XML data provider).So I am able to get the data =into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use =fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter =@.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, ="DecryptedAddressDS")) but getting error as "Fields cannot be used in =query parameter expressions".
Are there other ways to acess a field in a dataset from the other =dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive =Advantage. Persistent Systems provides custom software product =development services. With over 15 years, 140 customers, and 700+ =release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You can only use aggregate functions =outside a dataset (min, max, first). So unless the field doesn't change value (in =which case you can use first) you cannot do this the way you want. However, if =I understand what you are trying to do you can do this with subreports. =Turn the dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with parameters and thoroughly test. Then put it on the main report and do a right mouse =click on the subreport, parameters and then map the parameter to the field in =dataset A.
-- Bruce Loehle-CongerMVP =SQL Server Reporting Services
"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 =Reporting Services to develop the reports.
In my report I will be using 2 =datasets say A and B.
Dataset A connects to the XML type =Data Source Provider(i.e. querying Web services through the XML data provider).So =I am able to get the data into the dataset A from the XML =webservice.
Dataset A connects to the MS SQL =Server Provider. Now I want to use fields from the dataset A into the query of the =dataset B.
I tried to access the fields of the =dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but =getting error as "Fields cannot be used in query parameter =expressions".
Are there other ways to acess a =field in a dataset from the other dataset.
Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.persistentsys.com =Persistent Systems -Software Development Partner for Competitive Advantage. = Persistent Systems provides custom software product development services. With over 15 years, 140 customers, and 700+ release =cycles experience, we deliver unmatched value through high quality, faster =time to market and lower total costs.
--=_NextPart_000_0110_01C74F57.382E3590--|||This is a multi-part message in MIME format.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Bruce,
Thanks for the reply.
Actually I want to use the fields from dataset A into the one query of =dataset B. Like the query given below
Select count(distinct ani) from callsfromaug where applicationid in =(3,5) and convert(varchar,callsfromaug.ani) =3D @.Address and =@.datecreated >=3D @.currentDate
where @.Address and @.datecreated are fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:ee%23fEm4THHA.4632@.TK2MSFTNGP04.phx.gbl...
You can only use aggregate functions outside a dataset (min, max, =first). So unless the field doesn't change value (in which case you can =use first) you cannot do this the way you want. However, if I understand =what you are trying to do you can do this with subreports. Turn the =dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a =right mouse click on the subreport, parameters and then map the =parameter to the field in dataset A.
-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the =reports. In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. =querying Web services through the XML data provider).So I am able to get =the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use =fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter =@.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, ="DecryptedAddressDS")) but getting error as "Fields cannot be used in =query parameter expressions".
Are there other ways to acess a field in a dataset from the other =dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive =Advantage. Persistent Systems provides custom software product =development services. With over 15 years, 140 customers, and 700+ =release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Bruce,
Thanks for the reply.
Actually I want to use the fields from =dataset A into the one query of dataset B. Like the query given below =
Select count(distinct ani) from callsfromaug where applicationid in (3,5) and convert(varchar,callsfromaug.ani) =3D @.Address and @.datecreated >=3D @.currentDate
where @.Address and @.datecreated are =fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" wrote in message news:ee%23fEm4THHA.=4632@.TK2MSFTNGP04.phx.gbl...
You can only use aggregate functions =outside a dataset (min, max, first). So unless the field doesn't change value =(in which case you can use first) you cannot do this the way you want. However, =if I understand what you are trying to do you can do this with subreports. =Turn the dataset B into a subreport (which is just a normal report that you =drag and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a right mouse =click on the subreport, parameters and then map the parameter to the field in =dataset A.
-- Bruce Loehle-CongerMVP =SQL Server Reporting Services
"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 =Reporting Services to develop the reports.
In my report I will be using 2 =datasets say A and B.
Dataset A connects to the XML type =Data Source Provider(i.e. querying Web services through the XML data =provider).So I am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL =Server Provider. Now I want to use fields from the dataset A into the query =of the dataset B.
I tried to access the fields of the =dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but =getting error as "Fields cannot be used in query parameter =expressions".
Are there other ways to acess =a field in a dataset from the other dataset.
Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.persistentsys.com Persistent Systems -Software Development Partner for Competitive = Advantage. Persistent Systems provides custom software product = development services. With over 15 years, 140 customers, and =700+ release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0011_01C7501F.49516DB0--
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the reports. =
In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. querying = Web services through the XML data provider).So I am able to get the data = into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use = fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter = @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, = "DecryptedAddressDS")) but getting error as "Fields cannot be used in = query parameter expressions".
Are there other ways to acess a field in a dataset from the other = dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive = Advantage. Persistent Systems provides custom software product = development services. With over 15 years, 140 customers, and 700+ = release cycles experience, we deliver unmatched value through high = quality, faster time to market and lower total costs.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I am using MS SQL Server 2005 Reporting = Services to develop the reports.
In my report I will be using 2 datasets = say A and B.
Dataset A connects to the XML type Data = Source Provider(i.e. querying Web services through the XML data provider).So I = am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server = Provider. Now I want to use fields from the dataset A into the query of the = dataset B.
I tried to access the fields of the = dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but getting = error as "Fields cannot be used in query parameter expressions".
Are there other ways to acess a = field in a dataset from the other dataset.
Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 2226900(Off) = Extn: 7026
Mob : 099605 93699www.persistentsys.com = Persistent Systems -Software Development Partner for Competitive Advantage. Persistent Systems provides custom software product development = services. With over 15 years, 140 customers, and 700+ release cycles experience, = we deliver unmatched value through high quality, faster time to market and = lower total costs.
--=_NextPart_000_0017_01C74FB3.C419E480--This is a multi-part message in MIME format.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You can only use aggregate functions outside a dataset (min, max, =first). So unless the field doesn't change value (in which case you can =use first) you cannot do this the way you want. However, if I understand =what you are trying to do you can do this with subreports. Turn the =dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a =right mouse click on the subreport, parameters and then map the =parameter to the field in dataset A.
-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the =reports. In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. querying =Web services through the XML data provider).So I am able to get the data =into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use =fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter =@.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, ="DecryptedAddressDS")) but getting error as "Fields cannot be used in =query parameter expressions".
Are there other ways to acess a field in a dataset from the other =dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive =Advantage. Persistent Systems provides custom software product =development services. With over 15 years, 140 customers, and 700+ =release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You can only use aggregate functions =outside a dataset (min, max, first). So unless the field doesn't change value (in =which case you can use first) you cannot do this the way you want. However, if =I understand what you are trying to do you can do this with subreports. =Turn the dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with parameters and thoroughly test. Then put it on the main report and do a right mouse =click on the subreport, parameters and then map the parameter to the field in =dataset A.
-- Bruce Loehle-CongerMVP =SQL Server Reporting Services
"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 =Reporting Services to develop the reports.
In my report I will be using 2 =datasets say A and B.
Dataset A connects to the XML type =Data Source Provider(i.e. querying Web services through the XML data provider).So =I am able to get the data into the dataset A from the XML =webservice.
Dataset A connects to the MS SQL =Server Provider. Now I want to use fields from the dataset A into the query of the =dataset B.
I tried to access the fields of the =dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but =getting error as "Fields cannot be used in query parameter =expressions".
Are there other ways to acess a =field in a dataset from the other dataset.
Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.persistentsys.com =Persistent Systems -Software Development Partner for Competitive Advantage. = Persistent Systems provides custom software product development services. With over 15 years, 140 customers, and 700+ release =cycles experience, we deliver unmatched value through high quality, faster =time to market and lower total costs.
--=_NextPart_000_0110_01C74F57.382E3590--|||This is a multi-part message in MIME format.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Bruce,
Thanks for the reply.
Actually I want to use the fields from dataset A into the one query of =dataset B. Like the query given below
Select count(distinct ani) from callsfromaug where applicationid in =(3,5) and convert(varchar,callsfromaug.ani) =3D @.Address and =@.datecreated >=3D @.currentDate
where @.Address and @.datecreated are fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:ee%23fEm4THHA.4632@.TK2MSFTNGP04.phx.gbl...
You can only use aggregate functions outside a dataset (min, max, =first). So unless the field doesn't change value (in which case you can =use first) you cannot do this the way you want. However, if I understand =what you are trying to do you can do this with subreports. Turn the =dataset B into a subreport (which is just a normal report that you drag =and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a =right mouse click on the subreport, parameters and then map the =parameter to the field in dataset A.
-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 Reporting Services to develop the =reports. In my report I will be using 2 datasets say A and B. Dataset A connects to the XML type Data Source Provider(i.e. =querying Web services through the XML data provider).So I am able to get =the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use =fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter =@.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, ="DecryptedAddressDS")) but getting error as "Fields cannot be used in =query parameter expressions".
Are there other ways to acess a field in a dataset from the other =dataset.
Thanks in Advance,
Anubhav Jain MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com Persistent Systems -Software Development Partner for Competitive =Advantage. Persistent Systems provides custom software product =development services. With over 15 years, 140 customers, and 700+ =release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Bruce,
Thanks for the reply.
Actually I want to use the fields from =dataset A into the one query of dataset B. Like the query given below =
Select count(distinct ani) from callsfromaug where applicationid in (3,5) and convert(varchar,callsfromaug.ani) =3D @.Address and @.datecreated >=3D @.currentDate
where @.Address and @.datecreated are =fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]"
You can only use aggregate functions =outside a dataset (min, max, first). So unless the field doesn't change value =(in which case you can use first) you cannot do this the way you want. However, =if I understand what you are trying to do you can do this with subreports. =Turn the dataset B into a subreport (which is just a normal report that you =drag and drop unto the other report. Create the dataset B report with =parameters and thoroughly test. Then put it on the main report and do a right mouse =click on the subreport, parameters and then map the parameter to the field in =dataset A.
-- Bruce Loehle-CongerMVP =SQL Server Reporting Services
"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
Hi,
I am using MS SQL Server 2005 =Reporting Services to develop the reports.
In my report I will be using 2 =datasets say A and B.
Dataset A connects to the XML type =Data Source Provider(i.e. querying Web services through the XML data =provider).So I am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL =Server Provider. Now I want to use fields from the dataset A into the query =of the dataset B.
I tried to access the fields of the =dataset A using query parameter @.Address in the dataset B(like @.Adress=3DFirst(Fields!Address.Value, "DecryptedAddressDS")) but =getting error as "Fields cannot be used in query parameter =expressions".
Are there other ways to acess =a field in a dataset from the other dataset.
Thanks in Advance,Anubhav Jain MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.persistentsys.com Persistent Systems -Software Development Partner for Competitive = Advantage. Persistent Systems provides custom software product = development services. With over 15 years, 140 customers, and =700+ release cycles experience, we deliver unmatched value through high =quality, faster time to market and lower total costs.
--=_NextPart_000_0011_01C7501F.49516DB0--
Need text in Tital Case
Hey Friends...
In database I have description field in Uppar case but I want to display it in Title Case ( I tried ProperCase but its not working in Crystal report 8.5)
Like :
In database I have description like : TAKE BACK CONTROL OF YOUR CHANGE
But in report I want like : Take Back Control Of Your Change
and I am using crystal report 8.5 so can anybody help me please
Thanks
-Jayesh Mendpara
jmendpara@.gmail.comyou didn't mentioned what database u r using
oracle supports INITCAP funtion
im sure equivalent, available in SQL Server.
so u can handle that at database level.|||Hello sraheem...
I have the same situation as this initial post. However my situation is not really connected to a db. The initial dollar amount in the example does come from a db however the conversion just takes place within the report itself. I have converted the numbers to text and need to get this in title case. Example:
($583.00 converted to - five hundred eighty-three and xx/100)
I need the conversion to read: Five Hundred Eighty-Three And xx/100
Any assistance you could provide would be greatly appreciated. I thank you in advance for your time!|||Create a formula for the field you want to display as such and use something like this for the formula:
ProperCase ({MyTableNameGoesHere.MyFieldNameGoesHere})|||Will this work in 8.5?|||I think we already established that it won't.
Relatively simple to implement in a formula though.|||Check out this link for info on how to do it in 8.5.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=B649460555EE48B11E10F040D88394B5?cmd=displayKC&docType=kc&externalId=c2011637&sliceId=&dialogID=360384&stateId=1%200%20356275
In database I have description field in Uppar case but I want to display it in Title Case ( I tried ProperCase but its not working in Crystal report 8.5)
Like :
In database I have description like : TAKE BACK CONTROL OF YOUR CHANGE
But in report I want like : Take Back Control Of Your Change
and I am using crystal report 8.5 so can anybody help me please
Thanks
-Jayesh Mendpara
jmendpara@.gmail.comyou didn't mentioned what database u r using
oracle supports INITCAP funtion
im sure equivalent, available in SQL Server.
so u can handle that at database level.|||Hello sraheem...
I have the same situation as this initial post. However my situation is not really connected to a db. The initial dollar amount in the example does come from a db however the conversion just takes place within the report itself. I have converted the numbers to text and need to get this in title case. Example:
($583.00 converted to - five hundred eighty-three and xx/100)
I need the conversion to read: Five Hundred Eighty-Three And xx/100
Any assistance you could provide would be greatly appreciated. I thank you in advance for your time!|||Create a formula for the field you want to display as such and use something like this for the formula:
ProperCase ({MyTableNameGoesHere.MyFieldNameGoesHere})|||Will this work in 8.5?|||I think we already established that it won't.
Relatively simple to implement in a formula though.|||Check out this link for info on how to do it in 8.5.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=B649460555EE48B11E10F040D88394B5?cmd=displayKC&docType=kc&externalId=c2011637&sliceId=&dialogID=360384&stateId=1%200%20356275
Friday, March 23, 2012
Need SQL Help
I have following query which is joining couple of tables. i have a field "Status" in MeetingAttendees table. I have to add one more check (probably one more case statement) that if MA.Status=4 then Count(A.AttendeeID) as NoofAttendees. So NoofRSVPs (doesn't matter what is the status in MeetingAttendees table) will return total RSVPs and NoofAttendee will return only # of Attendees. how can i add do that? please help...
SELECT
M.State AS MeetingState,
CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
WHEN MA.AttendeeType = 3 THEN 'Client'
WHEN MA.AttendeeType = 4 THEN 'Staff'
END AS AttendeeType,
Count(A.AttendeeID) as NoofRSVPs
FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode
GROUP BY
M.State,
MA.AttendeeType
ORDER BY
MA.AttendeeTypeCOUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendees
SELECT
M.State AS MeetingState,
CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
WHEN MA.AttendeeType = 3 THEN 'Client'
WHEN MA.AttendeeType = 4 THEN 'Staff'
END AS AttendeeType,
Count(A.AttendeeID) as NoofRSVPs
FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode
GROUP BY
M.State,
MA.AttendeeType
ORDER BY
MA.AttendeeTypeCOUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendees
Wednesday, March 21, 2012
Need some helps on this task
A user photo table with the following fields:
username
path
caption
status
where the status field indicates where the photo is the main one or not. Each user only can have one main photo.
For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?
Similar situation occur when a user want to change a non-main photo to become a main one.
Any advice?
Thanks,
v.While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.
I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.|||Hi, Tony,
Thanks for your response and suggestion.
My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.
I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.
Thanks again.
v.|||Here is a solution I just come out.
For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.
Otherwise, after the deletion statement, run the following query:
UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )
This solution basically is on the DB side with a little help from the application (logic).
My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.
v.
username
path
caption
status
where the status field indicates where the photo is the main one or not. Each user only can have one main photo.
For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?
Similar situation occur when a user want to change a non-main photo to become a main one.
Any advice?
Thanks,
v.While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.
I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.|||Hi, Tony,
Thanks for your response and suggestion.
My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.
I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.
Thanks again.
v.|||Here is a solution I just come out.
For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.
Otherwise, after the deletion statement, run the following query:
UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )
This solution basically is on the DB side with a little help from the application (logic).
My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.
v.
Wednesday, March 7, 2012
Need I........
If I've indexed field "A","B","C" with primary key, So do I still need to
index field "B"?That depends on how you access the table. If your WHERE clause looks this
this:
WHERE B = x
and you do this often you would probably benefit from another index on B
alone. If you do this:
WHERE A = y AND B = x
There is no need. The only time an index will use the columns in a compound
index that are not the first ones in the index expression is if it scans the
entire index. This is usually not the desired effect.
Andrew J. Kelly SQL MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:OAQMYfHvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I've indexed field "A","B","C" with primary key, So do I still need to
> index field "B"?
>
index field "B"?That depends on how you access the table. If your WHERE clause looks this
this:
WHERE B = x
and you do this often you would probably benefit from another index on B
alone. If you do this:
WHERE A = y AND B = x
There is no need. The only time an index will use the columns in a compound
index that are not the first ones in the index expression is if it scans the
entire index. This is usually not the desired effect.
Andrew J. Kelly SQL MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:OAQMYfHvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I've indexed field "A","B","C" with primary key, So do I still need to
> index field "B"?
>
Saturday, February 25, 2012
Need Help?
I have a field with the name in my database called comments. In this field i have data like "zeroed out (10/21 10:37 AM - GS)CT SAID THIS CASE IS DISPOSED ALREADY (2/13 10:05 AM - CD)"
I need to get datetime that is in last braces (2/13 10:05AM - CD). how can i get this if i have multiple braces in substring function in sql server ?.
Thank you.
.substring(lastindexof( '(' ), lastindexof( ')' )|||VB
Dim commentsAs String ="zeroed out (10/21 10:37 AM - GS)CT SAID THIS CASE IS DISPOSED ALREADY (2/13 10:05 AM - CD)"Dim startAs Integer = comments.LastIndexOf("(")Dim endingAs Integer = comments.LastIndexOf(")") - start + 1 comments = comments.Substring(start, ending)C#{string comments ="zeroed out (10/21 10:37 AM - GS)CT SAID THIS CASE IS DISPOSED ALREADY (2/13 10:05 AM - CD)";int start = comments.LastIndexOf("(");int ending = comments.LastIndexOf(")") - start + 1; comments = comments.Substring(start, ending); } Just some other options|||Thank you for your help but i want this to do in sql server.
need help. need to check a value inside a sql statement
What i need to do is check a field inside a sql statement to see if it is null and if it is then change the value to a 0.
for example
select Field1,field2,field3(if null then =0)
from table1
does this make sense. I think in oracle PL/SQL is uses decode(field3, null... or something like that
any help would be appreciated
thanksYou can use either the coalesce function which takes the first non-null value in a list, or the isnull function. I understand coalesce is more ansi-sql friendly, and I believe is implemented in Oracle as well.|||Example:
select Field1, field2, isnull(field3, 0) from table1
or
select Field1, field2, coalesce(field3, 0) from table1
blindman|||I am trying to put this in a MS access query
My statement look like this
Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....
I get an error message saying
" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"
thanks|||I am trying to put this in a MS access query
My statement look like this
Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....
I get an error message saying
" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"
thanks|||MS Access does not use the same exact same function list as SQL Server. The ISNULL function in MS Access takes a single parameter and returns a boolean value indicating whether the parameter was null.
The MSAccess function you are looking for is "NZ", short for Null to Zero.
Select Table_Name.Field1, Table_name.Field2, NZ(Table_Name.Field3,0),Table_Name.field4
from Table, Table
MS Access SQL is hybridized with VB.
blindman
for example
select Field1,field2,field3(if null then =0)
from table1
does this make sense. I think in oracle PL/SQL is uses decode(field3, null... or something like that
any help would be appreciated
thanksYou can use either the coalesce function which takes the first non-null value in a list, or the isnull function. I understand coalesce is more ansi-sql friendly, and I believe is implemented in Oracle as well.|||Example:
select Field1, field2, isnull(field3, 0) from table1
or
select Field1, field2, coalesce(field3, 0) from table1
blindman|||I am trying to put this in a MS access query
My statement look like this
Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....
I get an error message saying
" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"
thanks|||I am trying to put this in a MS access query
My statement look like this
Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....
I get an error message saying
" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"
thanks|||MS Access does not use the same exact same function list as SQL Server. The ISNULL function in MS Access takes a single parameter and returns a boolean value indicating whether the parameter was null.
The MSAccess function you are looking for is "NZ", short for Null to Zero.
Select Table_Name.Field1, Table_name.Field2, NZ(Table_Name.Field3,0),Table_Name.field4
from Table, Table
MS Access SQL is hybridized with VB.
blindman
Monday, February 20, 2012
Need help with UPDATE with PARTITION & PARAMETER
Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB, but I am restricted to read-only.
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.
Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only.
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the each latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.|||
Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far. I am working in SQL server 2005.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only.
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.|||
You are returning the results of a data manipulation - there may not be a 'match' between the resultset and the actual table.
My 'simple' recommendation is to:
capture the resultset in a @.Table variable, then use the [ID] value from that table variable to UPDATE the data, finally, returning the contents of the table variable with a SELECT.|||Thanks for the reply. I posted this question a few too many times (was told it was deleted by administrator!).
Anyway, [ID] is not the primary key, [acmdtn_RECID] is. Any changes made to the master database simply add another record, and retain the old record, so historical queries can be run. So there can be dozens of records for each [ID]. What I want is the most recent record, for each [ID], as of a certain date.
I guess I just don't get why I can display the information, but I can't write back to the database based on that displayed information. It's a complex query, but there's no joins, or other tables involved, there can only be a 1:1 relationship between records in the table, and records in the resultset.
Need help with Update statement; DDL included
Four table: Securities, Positions, ExchangeList and ExchangeListMember
Securities has an SecurityID field and the exchange it is traded on.
Positions has a SecurityID and an Account.
ExchangeList is used to define the exchanges that an account is permitted to
own securities on. The ExchangeList.ExchangeListCode would contain an
account code.
ExchangeListMember contains the specific exchanges permitted for an account.
For example Account A can own securities on the NYS and BUE exchanges.
I have created a temp table called #TempPositions which contains all
positions. I want to update #TempPositions by placing an asterisk before the
SecurityID in records where the security is NOT permitted because the
securitys' exchange is NOT permissible.
UPDATE #TempPositions SET #TempPositions.SecurityID = '*' + #TempPositions
.SecurityID
WHERE .....
Here are my expected results for SELECT * FROM #TempPositions after the
update.
1,A
*2,A
3,A
3,B
4,B
*5,B
6,C
7,C
*1,D
*2,D
More narrative:
Account A is permitted to own securities on the NYS and BUE exchanges. It
has a position with SecurityID =2 which is traded on CBT. This position is
restricted.
Account B is permitted to own securities on the BUE and CPH exchanges. It
has a position with SecurityID =5 which is traded on NAS. This position is
restricted.
Account C does not have an ExchangeList. No restrictions.
Account D is permitted to own securities on the NAS exchange only. It has a
position with SecurityID =1 which is traded on NYS. It has a position with
SecurityID =2 which is traded on CBT .These positions is restricted.
This would be easier for me if the ExchangeList table defined restricted
exchanges rather than permitted exchanges. Any help would be appreciated.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeList]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ExchangeList]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeListMember]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ExchangeListMember]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Positions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeList] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ExchangeListName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeListMember] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[#TempPositions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[SecurityID],
[Account]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeList] ADD
CONSTRAINT [PK_ExchangeList] PRIMARY KEY CLUSTERED
(
[ExchangeListCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeListMember] ADD
CONSTRAINT [PK_ExchangeListMember] PRIMARY KEY CLUSTERED
(
[ExchangeListCode],
[Exchange]
) ON [PRIMARY]
GO
INSERT Securities (SecurityID,Exchange) VALUES (1,'NYS')
INSERT Securities (SecurityID,Exchange) VALUES (2,'CBT')
INSERT Securities (SecurityID,Exchange) VALUES (3,'BUE')
INSERT Securities (SecurityID,Exchange) VALUES (4,'CPH')
INSERT Securities (SecurityID,Exchange) VALUES (5,'NAS')
INSERT Securities (SecurityID,Exchange) VALUES (6,'IRL')
INSERT Securities (SecurityID,Exchange) VALUES (7,'JPN')
INSERT Securities (SecurityID,Exchange) VALUES (8,'KOR')
GO
INSERT Positions (SecurityID,Account)VALUES (1,'A')
INSERT Positions (SecurityID,Account)VALUES (2,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'B')
INSERT Positions (SecurityID,Account)VALUES (4,'B')
INSERT Positions (SecurityID,Account)VALUES (5,'B')
INSERT Positions (SecurityID,Account)VALUES (6,'C')
INSERT Positions (SecurityID,Account)VALUES (7,'C')
INSERT Positions (SecurityID,Account)VALUES (1,'D')
INSERT Positions (SecurityID,Account)VALUES (2,'D')
GO
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('A', 'A
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('B', 'B
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('D', 'D
Permissible Exchanges')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','NYS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','CPH')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','NAS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('D','NAS')
INSERT #TempPositions (SecurityID,Account)(SELECT * FROM Positions)Terri wrote:
Thanks for the DDL.
I tried it out but I'm
.
Your narrative explanation doesn't seem to completely fit the data you
supplied or I'm looking at it the wrong way.
However I think this is what you're looking for:
alter table #TempPositions add Restricted bit default 0 NOT NULL
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
You cannot add a asterix to a integer column so I added a column named
Restricted and flag it 1 if it is.
HTH,
Stijn Verrept.|||Thanks Stijn, Sorry about me trying to add an asterisk to an integer field.
Altering the table is fine.
I had tried something like your solution but the problem is with Account C.
Account C doesn't have an ExchangeList so securities should never be
restricted. The subselect somehow needs to take into account "accounts" with
no ExchangeList and make sure the securities of those accounts are not
flagged as restricted.
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:1sqdnTV81ZHwoDreRVny0A@.scarlet.biz...
> Terri wrote:
> Thanks for the DDL.
> I tried it out but I'm.
> Your narrative explanation doesn't seem to completely fit the data you
> supplied or I'm looking at it the wrong way.
> However I think this is what you're looking for:
> alter table #TempPositions add Restricted bit default 0 NOT NULL
> UPDATE #TempPositions SET #TempPositions.Restricted = 1
> WHERE securityID not in (select securities.SecurityID from
> ExchangeListMember inner join securities on securities.SecurityID =
> #TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
> and securities.Exchange = ExchangeListMember.Exchange)
>
> You cannot add a asterix to a integer column so I added a column named
> Restricted and flag it 1 if it is.
> --
> HTH,
> Stijn Verrept.|||Terri wrote:
> Thanks Stijn, Sorry about me trying to add an asterisk to an integer
> field. Altering the table is fine.
> I had tried something like your solution but the problem is with
> Account C. Account C doesn't have an ExchangeList so securities
> should never be restricted. The subselect somehow needs to take into
> account "accounts" with no ExchangeList and make sure the securities
> of those accounts are not flagged as restricted.
Aha! That's probably what I didn't understand, then this will be
better :)
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
and (select count(*) from ExchangeListMember where ExchangeListCode =
#TempPositions.Account) > 0
HTH,
Stijn Verrept.|||"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:T9qdnWst9ZU81TrenZ2dnUVZ8qidnZ2d@.sc
arlet.biz...
> Aha! That's probably what I didn't understand, then this will be
> better :)
Perfect, thanks so much.|||>> I have created a temp table called #TempPositions which contains all posi
tions. I want to update #TempPositions by placing an asterisk [in violation
of the rule about not formatting display data in the database!!] before the
SecurityID in records [sic
] where the security is NOT permitted because the securitys' exchange is NOT
permissible. <<
Have you thought about using a VIEW that would always be up to date,
instead of constantly updating a temp table in proprietary syntax?
But the real point is that this is a constraint and needs to be done
with REFERENCES clauses and a proper design -- NOT a temp table at all!
Also, ask yourself why you have two tables with the SAME structure in
violation of the rules of any data modeling?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135050199.293240.265890@.g14g2000cwa.googlegroups.com...
positions. I want to update #TempPositions by placing an asterisk [in
violation of the rule about not formatting display data in the database!!]
before the SecurityID in records [sic] where the security is NOT permitted
because the securitys' exchange is NOT permissible. <<
> Have you thought about using a VIEW that would always be up to date,
> instead of constantly updating a temp table in proprietary syntax?
> But the real point is that this is a constraint and needs to be done
> with REFERENCES clauses and a proper design -- NOT a temp table at all!
> Also, ask yourself why you have two tables with the SAME structure in
> violation of the rules of any data modeling?
>
My employer strongly discourages me from presenting real data or table
structure so I may post a problem that helps me solve my issue but doesn't
reveal any information about my data, data structure, or systems. I suspect
others do this is well so I don't think it's safe to assume that posted data
structure or methods are used in production. They could merely serve as a
demonstration of a related problem.
In this case I am actually using temp tables in a stored procedure. I update
my temp table 6 times in the procedure. Perhaps someone could write a single
select statement which would return the results I need, but not me at this
point. I consider the logic of the procedure to be highly complex. I find it
easier to achieve my desired result with a series of updates against temp
tables although I realize this is not ideal from an academic point of view.
The procedure takes <4 seconds to run and is only run in production several
times a w
so I consider the performance acceptable. Others who have
viewed this procedure find it easier to comprehend. I find it easier to
maintain and modify. I understand your point and will consider it in the
future.
Securities has an SecurityID field and the exchange it is traded on.
Positions has a SecurityID and an Account.
ExchangeList is used to define the exchanges that an account is permitted to
own securities on. The ExchangeList.ExchangeListCode would contain an
account code.
ExchangeListMember contains the specific exchanges permitted for an account.
For example Account A can own securities on the NYS and BUE exchanges.
I have created a temp table called #TempPositions which contains all
positions. I want to update #TempPositions by placing an asterisk before the
SecurityID in records where the security is NOT permitted because the
securitys' exchange is NOT permissible.
UPDATE #TempPositions SET #TempPositions.SecurityID = '*' + #TempPositions
.SecurityID
WHERE .....
Here are my expected results for SELECT * FROM #TempPositions after the
update.
1,A
*2,A
3,A
3,B
4,B
*5,B
6,C
7,C
*1,D
*2,D
More narrative:
Account A is permitted to own securities on the NYS and BUE exchanges. It
has a position with SecurityID =2 which is traded on CBT. This position is
restricted.
Account B is permitted to own securities on the BUE and CPH exchanges. It
has a position with SecurityID =5 which is traded on NAS. This position is
restricted.
Account C does not have an ExchangeList. No restrictions.
Account D is permitted to own securities on the NAS exchange only. It has a
position with SecurityID =1 which is traded on NYS. It has a position with
SecurityID =2 which is traded on CBT .These positions is restricted.
This would be easier for me if the ExchangeList table defined restricted
exchanges rather than permitted exchanges. Any help would be appreciated.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeList]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ExchangeList]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeListMember]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ExchangeListMember]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Positions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeList] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ExchangeListName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeListMember] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[#TempPositions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[SecurityID],
[Account]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeList] ADD
CONSTRAINT [PK_ExchangeList] PRIMARY KEY CLUSTERED
(
[ExchangeListCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeListMember] ADD
CONSTRAINT [PK_ExchangeListMember] PRIMARY KEY CLUSTERED
(
[ExchangeListCode],
[Exchange]
) ON [PRIMARY]
GO
INSERT Securities (SecurityID,Exchange) VALUES (1,'NYS')
INSERT Securities (SecurityID,Exchange) VALUES (2,'CBT')
INSERT Securities (SecurityID,Exchange) VALUES (3,'BUE')
INSERT Securities (SecurityID,Exchange) VALUES (4,'CPH')
INSERT Securities (SecurityID,Exchange) VALUES (5,'NAS')
INSERT Securities (SecurityID,Exchange) VALUES (6,'IRL')
INSERT Securities (SecurityID,Exchange) VALUES (7,'JPN')
INSERT Securities (SecurityID,Exchange) VALUES (8,'KOR')
GO
INSERT Positions (SecurityID,Account)VALUES (1,'A')
INSERT Positions (SecurityID,Account)VALUES (2,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'B')
INSERT Positions (SecurityID,Account)VALUES (4,'B')
INSERT Positions (SecurityID,Account)VALUES (5,'B')
INSERT Positions (SecurityID,Account)VALUES (6,'C')
INSERT Positions (SecurityID,Account)VALUES (7,'C')
INSERT Positions (SecurityID,Account)VALUES (1,'D')
INSERT Positions (SecurityID,Account)VALUES (2,'D')
GO
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('A', 'A
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('B', 'B
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('D', 'D
Permissible Exchanges')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','NYS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','CPH')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','NAS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('D','NAS')
INSERT #TempPositions (SecurityID,Account)(SELECT * FROM Positions)Terri wrote:
Thanks for the DDL.
I tried it out but I'm
.Your narrative explanation doesn't seem to completely fit the data you
supplied or I'm looking at it the wrong way.
However I think this is what you're looking for:
alter table #TempPositions add Restricted bit default 0 NOT NULL
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
You cannot add a asterix to a integer column so I added a column named
Restricted and flag it 1 if it is.
HTH,
Stijn Verrept.|||Thanks Stijn, Sorry about me trying to add an asterisk to an integer field.
Altering the table is fine.
I had tried something like your solution but the problem is with Account C.
Account C doesn't have an ExchangeList so securities should never be
restricted. The subselect somehow needs to take into account "accounts" with
no ExchangeList and make sure the securities of those accounts are not
flagged as restricted.
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:1sqdnTV81ZHwoDreRVny0A@.scarlet.biz...
> Terri wrote:
> Thanks for the DDL.
> I tried it out but I'm
.> Your narrative explanation doesn't seem to completely fit the data you
> supplied or I'm looking at it the wrong way.
> However I think this is what you're looking for:
> alter table #TempPositions add Restricted bit default 0 NOT NULL
> UPDATE #TempPositions SET #TempPositions.Restricted = 1
> WHERE securityID not in (select securities.SecurityID from
> ExchangeListMember inner join securities on securities.SecurityID =
> #TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
> and securities.Exchange = ExchangeListMember.Exchange)
>
> You cannot add a asterix to a integer column so I added a column named
> Restricted and flag it 1 if it is.
> --
> HTH,
> Stijn Verrept.|||Terri wrote:
> Thanks Stijn, Sorry about me trying to add an asterisk to an integer
> field. Altering the table is fine.
> I had tried something like your solution but the problem is with
> Account C. Account C doesn't have an ExchangeList so securities
> should never be restricted. The subselect somehow needs to take into
> account "accounts" with no ExchangeList and make sure the securities
> of those accounts are not flagged as restricted.
Aha! That's probably what I didn't understand, then this will be
better :)
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
and (select count(*) from ExchangeListMember where ExchangeListCode =
#TempPositions.Account) > 0
HTH,
Stijn Verrept.|||"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:T9qdnWst9ZU81TrenZ2dnUVZ8qidnZ2d@.sc
arlet.biz...
> Aha! That's probably what I didn't understand, then this will be
> better :)
Perfect, thanks so much.|||>> I have created a temp table called #TempPositions which contains all posi
tions. I want to update #TempPositions by placing an asterisk [in violation
of the rule about not formatting display data in the database!!] before the
SecurityID in records [sic
] where the security is NOT permitted because the securitys' exchange is NOT
permissible. <<
Have you thought about using a VIEW that would always be up to date,
instead of constantly updating a temp table in proprietary syntax?
But the real point is that this is a constraint and needs to be done
with REFERENCES clauses and a proper design -- NOT a temp table at all!
Also, ask yourself why you have two tables with the SAME structure in
violation of the rules of any data modeling?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135050199.293240.265890@.g14g2000cwa.googlegroups.com...
positions. I want to update #TempPositions by placing an asterisk [in
violation of the rule about not formatting display data in the database!!]
before the SecurityID in records [sic] where the security is NOT permitted
because the securitys' exchange is NOT permissible. <<
> Have you thought about using a VIEW that would always be up to date,
> instead of constantly updating a temp table in proprietary syntax?
> But the real point is that this is a constraint and needs to be done
> with REFERENCES clauses and a proper design -- NOT a temp table at all!
> Also, ask yourself why you have two tables with the SAME structure in
> violation of the rules of any data modeling?
>
My employer strongly discourages me from presenting real data or table
structure so I may post a problem that helps me solve my issue but doesn't
reveal any information about my data, data structure, or systems. I suspect
others do this is well so I don't think it's safe to assume that posted data
structure or methods are used in production. They could merely serve as a
demonstration of a related problem.
In this case I am actually using temp tables in a stored procedure. I update
my temp table 6 times in the procedure. Perhaps someone could write a single
select statement which would return the results I need, but not me at this
point. I consider the logic of the procedure to be highly complex. I find it
easier to achieve my desired result with a series of updates against temp
tables although I realize this is not ideal from an academic point of view.
The procedure takes <4 seconds to run and is only run in production several
times a w
so I consider the performance acceptable. Others who haveviewed this procedure find it easier to comprehend. I find it easier to
maintain and modify. I understand your point and will consider it in the
future.
Need help with Update query to populate new field
Hi folks:
I have added a new field to an established table, and am having trouble figuring out how to populate its values:
Two tables are involved: Jobs and Parts
There is a one-to-one relationship between each JobID and its PartID
Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.
The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:
UPDATE Jobs
SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))
WHERE (JobID = [the job in question])
My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,
Matt
which is the parent table?
|||assuming that the jobs table has a foreign key that links to the parts table, the update query should look like this
UPDATE J
set j.JobPrice = p.PartPrice
from Jobs J
join Parts p on (p.PartID = j.PartID)|||
Thanks for your replies. I don't have any knowledge about foreign keys, etc. I attempted to set one up between the Jobs and Parts tables, but SQL Server Mgt Studio is still throwing errors when I attempt your query, carlop.
Thank you both anyway for throwing a pearl or two before this swine.
m
Subscribe to:
Posts (Atom)