Inter Table Multiplication and Result (Newbie)

I want to multiply a column (Units) for all transactions in the current
table by a price from another table and reflect the result a column in the
'Units' table. How would I do this and update this daily using SQL server.
The transactions and pricing are matched by a 'Date' field.
Thanks for any help or direction.http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
> I want to multiply a column (Units) for all transactions in the current
> table by a price from another table and reflect the result a column in the
> 'Units' table. How would I do this and update this daily using SQL server.
> The transactions and pricing are matched by a 'Date' field.
> Thanks for any help or direction.
>|||Adam
The SQL statement I am using is below but I get the error that is shown
thereafter. What I am really trying to achieve is as documented int eh
second statement.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*10
WHERE [Dates]=[dbo].[Prices].[Dates]
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'dbo.Prices' does not match with a table name or alias
name used in the query.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
Thanks for an assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
>|||Total guess, since you still haven't provided DDL (I don't know what the
keys are):
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[Prices].[numbers]*10
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
If this still doesn't do it, please post DDL and sample data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
> Adam
> The SQL statement I am using is below but I get the error that is shown
> thereafter. What I am really trying to achieve is as documented int eh
> second statement.
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*10
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'dbo.Prices' does not match with a table name or alias
> name used in the query.
>
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Thanks for an assistance.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
>|||Adam
I hope I have the relevant info correct as requested. Thanks again.
CREATE TABLE [Prices] (
[Prices] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [numbers] (
[numbers] [float] NULL ,
[number_result] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(100, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(200, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(300, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(400, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(2.5, 38564)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(3.2, 38565)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Total guess, since you still haven't provided DDL (I don't know what the
> keys are):
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[Prices].[numbers]*10
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
>
> If this still doesn't do it, please post DDL and sample data.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
>|||Try:
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
By the way, what are these floating-point "dates" ?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> Adam
> I hope I have the relevant info correct as requested. Thanks again.
>
> CREATE TABLE [Prices] (
> [Prices] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [numbers] (
> [numbers] [float] NULL ,
> [number_result] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(100, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(200, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(300, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(400, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(2.5, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(3.2, 38565)
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
alias
message
in
>|||Adam
I get the error 'Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ')'.
I have tried adding another ')' after line and then get the error message
Invalid object name 'dbo.Prices'
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%235WjsUMqFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Try:
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
>
> By the way, what are these floating-point "dates" ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> alias
> message
> in
>|||On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:

>Adam
>I get the error 'Server: Msg 170, Level 15, State 1, Line 13
>Line 13: Incorrect syntax near ')'.
Hi dl,
There's a small typo in Adam's message:
should be
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I changed this and got the error message 'Invalid object name 'dbo.Prices'.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8uapg19u9imvq32ca4a1nc2ujhdq84186o@.
4ax.com...
> On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:
>
> Hi dl,
> There's a small typo in Adam's message:
>
> should be
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Integrating RS 2005 Reports with Web Applications

Hi All
M a newbie in RS 2005 ..
I need to integrate my RS 2005 Reports into the web applications ...
say ASP.NET or made using C# ...
without using the web-services ...
there's a way using URL access and one more using Visual Basic 2005 ...
All my queries are stored as stored procedure and need to have my own
interface where from the user can see the report for the actions which
one takes ..
Please help...
Regards
Manu GuptaURL access to reports is well documented in the SQL Server books online.
You can download here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
in case you don't already have SQL 2005 installed somewhere.
URL access will work no matter what your development environment or language
is. Obviously I'd recomend using VisualStudio.NET 2005. The choice of C#,
VB.NET or any of the other .NET language providers makes no difference at
all.
<rmanu7382@.gmail.com> wrote in message
news:1141130557.353566.267620@.t39g2000cwt.googlegroups.com...
> Hi All
> M a newbie in RS 2005 ..
> I need to integrate my RS 2005 Reports into the web applications ...
> say ASP.NET or made using C# ...
> without using the web-services ...
> there's a way using URL access and one more using Visual Basic 2005 ...
> All my queries are stored as stored procedure and need to have my own
> interface where from the user can see the report for the actions which
> one takes ..
> Please help...
> Regards
> Manu Gupta
>|||What is your problem with web services? That is how Report Manager itself
integrates wtih RS.
URL access will work but in your case what you should be looking at are the
new controls that come with VS 2005. The controls come in two flavors:
winform and webform. They will drastically reduce your integration time.
They are built using web services but that would be transparent to you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rmanu7382@.gmail.com> wrote in message
news:1141130557.353566.267620@.t39g2000cwt.googlegroups.com...
> Hi All
> M a newbie in RS 2005 ..
> I need to integrate my RS 2005 Reports into the web applications ...
> say ASP.NET or made using C# ...
> without using the web-services ...
> there's a way using URL access and one more using Visual Basic 2005 ...
> All my queries are stored as stored procedure and need to have my own
> interface where from the user can see the report for the actions which
> one takes ..
> Please help...
> Regards
> Manu Gupta
>

Integrating Reporting Services with My Own Front End?

Greetings!
Okay, kind of a newbie question here. Maybe you more experienced folks can
help. I've got RS up & running okay. I'm hoping you can lead me to info
and/or examples of using Reporting Services, but integrating it into my own
ASP.NET front end pages. The Reporting Services-supplied front end is very
nice, but the look & feel is not consistent with our other pages.
Thanks in advance,
Joel Koblich
www.3cms.orgThere are several ways to integrate your own front end. The easiest way is
to use URL integration. The advantage of this is that you can easily call
any report passing it parameters. If you want to hide parameters from your
user then this gets to be dicey. Also, you get full functionality with URL
integration (drill down, drill through). You can also use web services. You
render a stream and do with it as you will. However, you have to handle some
of the features yourself that come for free with URL integration. That being
the case, why would someone want to use web services. A big reason could be
that the application is internet facing and the report server is being the
firewall. URL integration will not work for this scenario.
With RS 2005 (release date November) and Widbey (Visual Studio 2005, same
release date) you can use the new web and winform controls. These will make
integrating with RS reports much easier. If you want you can not even have a
report server, you can operate in local mode and give the control the report
and the dataset(s) and away it goes. However, you do lose functionality in
local mode. Not all rendering options are available, you don't have caching,
subscriptions etc. Having a server in the picture does provide value, but,
depending on your circumstances the new controls operating in local mode
might be all you need (the controls are distributed with VS). These controls
are available with the latest public beta of Widbey.
One thing to keep in mind when rolling your own is deciding on security.
Depending on what you are doing you might need to implement forms based
security.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Just Joel" <joeljustjoel@.att_DO_NOT_LIKE_SPAM.net> wrote in message
news:8N6te.985661$w62.310982@.bgtnsc05-news.ops.worldnet.att.net...
> Greetings!
> Okay, kind of a newbie question here. Maybe you more experienced folks
> can help. I've got RS up & running okay. I'm hoping you can lead me to
> info and/or examples of using Reporting Services, but integrating it into
> my own ASP.NET front end pages. The Reporting Services-supplied front end
> is very nice, but the look & feel is not consistent with our other pages.
> Thanks in advance,
> Joel Koblich
> www.3cms.org
>|||Thanks! That helps a lot.
Joel
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%2352a8WOdFHA.1456@.TK2MSFTNGP15.phx.gbl...
> There are several ways to integrate your own front end. The easiest way is
> to use URL integration. The advantage of this is that you can easily call
> any report passing it parameters. If you want to hide parameters from your
> user then this gets to be dicey. Also, you get full functionality with URL
> integration (drill down, drill through). You can also use web services.
> You render a stream and do with it as you will. However, you have to
> handle some of the features yourself that come for free with URL
> integration. That being the case, why would someone want to use web
> services. A big reason could be that the application is internet facing
> and the report server is being the firewall. URL integration will not work
> for this scenario.
> With RS 2005 (release date November) and Widbey (Visual Studio 2005, same
> release date) you can use the new web and winform controls. These will
> make integrating with RS reports much easier. If you want you can not even
> have a report server, you can operate in local mode and give the control
> the report and the dataset(s) and away it goes. However, you do lose
> functionality in local mode. Not all rendering options are available, you
> don't have caching, subscriptions etc. Having a server in the picture does
> provide value, but, depending on your circumstances the new controls
> operating in local mode might be all you need (the controls are
> distributed with VS). These controls are available with the latest public
> beta of Widbey.
> One thing to keep in mind when rolling your own is deciding on security.
> Depending on what you are doing you might need to implement forms based
> security.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Joel Just Joel" <joeljustjoel@.att_DO_NOT_LIKE_SPAM.net> wrote in message
> news:8N6te.985661$w62.310982@.bgtnsc05-news.ops.worldnet.att.net...
>> Greetings!
>> Okay, kind of a newbie question here. Maybe you more experienced folks
>> can help. I've got RS up & running okay. I'm hoping you can lead me to
>> info and/or examples of using Reporting Services, but integrating it into
>> my own ASP.NET front end pages. The Reporting Services-supplied front
>> end is very nice, but the look & feel is not consistent with our other
>> pages.
>> Thanks in advance,
>> Joel Koblich
>> www.3cms.org
>|||Bruce,
Do you know if the VS 2005 web control will use SOAP or URL when it is
operating in "Remote" mode? I have been trying to determine if VS2005/SQL2005
will solve this issue for us, over SSRS 2000.
Thanks,
Steve
"Bruce L-C [MVP]" wrote:
> There are several ways to integrate your own front end. The easiest way is
> to use URL integration. The advantage of this is that you can easily call
> any report passing it parameters. If you want to hide parameters from your
> user then this gets to be dicey. Also, you get full functionality with URL
> integration (drill down, drill through). You can also use web services. You
> render a stream and do with it as you will. However, you have to handle some
> of the features yourself that come for free with URL integration. That being
> the case, why would someone want to use web services. A big reason could be
> that the application is internet facing and the report server is being the
> firewall. URL integration will not work for this scenario.
> With RS 2005 (release date November) and Widbey (Visual Studio 2005, same
> release date) you can use the new web and winform controls. These will make
> integrating with RS reports much easier. If you want you can not even have a
> report server, you can operate in local mode and give the control the report
> and the dataset(s) and away it goes. However, you do lose functionality in
> local mode. Not all rendering options are available, you don't have caching,
> subscriptions etc. Having a server in the picture does provide value, but,
> depending on your circumstances the new controls operating in local mode
> might be all you need (the controls are distributed with VS). These controls
> are available with the latest public beta of Widbey.
> One thing to keep in mind when rolling your own is deciding on security.
> Depending on what you are doing you might need to implement forms based
> security.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Joel Just Joel" <joeljustjoel@.att_DO_NOT_LIKE_SPAM.net> wrote in message
> news:8N6te.985661$w62.310982@.bgtnsc05-news.ops.worldnet.att.net...
> > Greetings!
> >
> > Okay, kind of a newbie question here. Maybe you more experienced folks
> > can help. I've got RS up & running okay. I'm hoping you can lead me to
> > info and/or examples of using Reporting Services, but integrating it into
> > my own ASP.NET front end pages. The Reporting Services-supplied front end
> > is very nice, but the look & feel is not consistent with our other pages.
> >
> > Thanks in advance,
> > Joel Koblich
> > www.3cms.org
> >
>
>