Trouble passing paramaters into the query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trouble passing paramaters into the query

Author

Topic

matrosov
Starting Member

4 Posts

Posted - 2010-09-14 : 13:15:45

Glad I found this forum. Have a problem with a report I'm trying to create on the report server. The report is actually reading data from an oracle database but I don't think this is my problem.

Here is a background.

I have a sql query that reads off of database to give me a data set.
it is as follows.
What I want to do now is create a set of hard coded paramaters and pass them thru into this query to pull different months and years.

I started with the @Month paramater and hardcoded to be Label Jan Value 01 and so on. but whenever I try to inject that paramater into the query I get an error message about missing expression.

my code with injected paramter looks like this

from financial.rpts_financials a, financial.hierarchy b
where a.dept=b.dept
and a.mnth =@Month

What am I doing wrong? and thanks a lot for your help.

select
a.dept as dept_cd
object as object_cd
mnth
ytd,
a.dept||'-'||deptname as department,
object||'-'||descrip as account,
sap_costcenter,
sap_account,
area,
subarea
from financial.hierarchy b, financial.accounts c,
(select object, dept, mnth, ytd
from
(select object, a.dept, sum(amt) as mnth
from financial.rpts_financials a, financial.hierarchy b
where a.dept=b.dept
and a.mnth = '08' -- parameter substitute - first two of month string
and calyear=2010 -- parameter substitute year
and process = 'ACT'
and division ='MRL'
and object in (select objct_cd from mrlgrant.object)
group by a.dept, object)
full outer join
(select object, a.dept, sum(amt) as YTD
from financial.rpts_financials a, financial.hierarchy b
where a.dept=b.dept
and calyear=2010 -- parameter substitute year
and process = 'ACT' and
division ='MRL'
and object in (select objct_cd from mrlgrant.object)
group by a.dept, object)
using (dept, object) ) a
where
a.dept = b.dept and
a.object = c.linnum

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-14 : 14:08:08

You might want to try here

http://www.dbforums.com/oracle/

This is a SQLServer forum

but are you creating a Package/Procedure to fo this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

matrosov
Starting Member

4 Posts

Posted - 2010-09-24 : 23:08:43

Figured it out :). I posted here because I was using MSSQL Server 2008 BIDS to create the report. Solution to my problem was that I needed to really mix and match T-SQL and Oracle syntax to get it to work. T-SQL is using @ to specify paramaters and oracle using : so once i figured that one out it all fell into place.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources