Report Parameter not working in subquery?

Hi All,
There are two parameters in our DataSet based on the query below.
@.Year causes "syntax or access violation" error, because it's in subquery.
@.Parameter2 works (in the main query)
----
SELECT *,
( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
THEN 'Green'
WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
WHEN Av_MTD < ISNULL(Av_Comm,0)
THEN 'Red' ELSE 'Black'
END) AS Avail_color_MTD,
( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
THEN 'Red' ELSE 'Black' END
) AS Resp_color_MTD
FROM (
SELECT
Sla_Name_By_Loc,
MAX(Sla_Definition) AS Sla_Definition,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Commitment_By_Loc END) AS Av_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Commitment_By_Loc END) AS Re_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Result_by_loc END) AS Av_MTD,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Result_by_loc END) AS Re_MTD
FROM
Sla_Results_Trans_BY_LOC
WHERE (Sla_Category_id_By_Loc = 2)
AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
AND (Sla_Month_by_loc = 9)
AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
GROUP BY
Sla_Name_By_Loc) Y
where Y.Re_Comm = @.Parameter2 -- works!
ORDER BY Sla_Name_By_Loc
---
Please help!
TaoMake sure that you have two report parameters. In layout, menu
reports->report parameter. Next go to your dataset, ... , parameters tab and
make sure that each of your query parameters are mapped to a report
parameter. Being a subquery or not really doesn't matter.
Bruce L-C
"Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> Hi All,
> There are two parameters in our DataSet based on the query below.
> @.Year causes "syntax or access violation" error, because it's in subquery.
> @.Parameter2 works (in the main query)
> ----
> SELECT *,
> ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> THEN 'Green'
> WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> WHEN Av_MTD < ISNULL(Av_Comm,0)
> THEN 'Red' ELSE 'Black'
> END) AS Avail_color_MTD,
> ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> THEN 'Red' ELSE 'Black' END
> ) AS Resp_color_MTD
> FROM (
> SELECT
> Sla_Name_By_Loc,
> MAX(Sla_Definition) AS Sla_Definition,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Commitment_By_Loc END) AS Av_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Commitment_By_Loc END) AS Re_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Result_by_loc END) AS Av_MTD,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Result_by_loc END) AS Re_MTD
> FROM
> Sla_Results_Trans_BY_LOC
> WHERE (Sla_Category_id_By_Loc = 2)
> AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> AND (Sla_Month_by_loc = 9)
> AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> GROUP BY
> Sla_Name_By_Loc) Y
> where Y.Re_Comm = @.Parameter2 -- works!
> ORDER BY Sla_Name_By_Loc
> ---
> Please help!
> Tao|||Such a dataset would fail:
(pubs database)
SELECT au_lname
FROM authors
WHERE (au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id = @.title))
Error message: "Parameter Information cannot be derived from SQL statements
with sub-select queries. Set parameter information before preparing command."
But the parametere exists under Layout/Reports/Report Parameters and the
parameter mapping is there in the Dataset/Edit/Parameters tab:
@.title =Parameters!title.Value
Can some one try this out to see if you can succeed?
Thanks,
Tao
"Bruce Loehle-Conger" wrote:
> Make sure that you have two report parameters. In layout, menu
> reports->report parameter. Next go to your dataset, ... , parameters tab and
> make sure that each of your query parameters are mapped to a report
> parameter. Being a subquery or not really doesn't matter.
> Bruce L-C
> "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > Hi All,
> >
> > There are two parameters in our DataSet based on the query below.
> >
> > @.Year causes "syntax or access violation" error, because it's in subquery.
> > @.Parameter2 works (in the main query)
> > ----
> > SELECT *,
> > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > THEN 'Green'
> > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > THEN 'Red' ELSE 'Black'
> > END) AS Avail_color_MTD,
> > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > THEN 'Red' ELSE 'Black' END
> > ) AS Resp_color_MTD
> > FROM (
> > SELECT
> > Sla_Name_By_Loc,
> > MAX(Sla_Definition) AS Sla_Definition,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Commitment_By_Loc END) AS Av_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Commitment_By_Loc END) AS Re_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Result_by_loc END) AS Av_MTD,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Result_by_loc END) AS Re_MTD
> > FROM
> > Sla_Results_Trans_BY_LOC
> > WHERE (Sla_Category_id_By_Loc = 2)
> > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > AND (Sla_Month_by_loc = 9)
> > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > GROUP BY
> > Sla_Name_By_Loc) Y
> > where Y.Re_Comm = @.Parameter2 -- works!
> > ORDER BY Sla_Name_By_Loc
> > ---
> >
> > Please help!
> >
> > Tao
>
>|||Got the solution for you. If you use the generic query instead (click on the
button next to the !) then it will work. I think you have come across a
limitation of the graphical designer to deal with subqueries and parameters.
Works just the way you want from the query designer.
Bruce L-C [MVP Reporting Services]
"Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> Such a dataset would fail:
> (pubs database)
> SELECT au_lname
> FROM authors
> WHERE (au_id IN
> (SELECT au_id
> FROM titleauthor
> WHERE title_id = @.title))
> Error message: "Parameter Information cannot be derived from SQL
statements
> with sub-select queries. Set parameter information before preparing
command."
> But the parametere exists under Layout/Reports/Report Parameters and the
> parameter mapping is there in the Dataset/Edit/Parameters tab:
> @.title =Parameters!title.Value
> Can some one try this out to see if you can succeed?
> Thanks,
> Tao
> "Bruce Loehle-Conger" wrote:
> > Make sure that you have two report parameters. In layout, menu
> > reports->report parameter. Next go to your dataset, ... , parameters tab
and
> > make sure that each of your query parameters are mapped to a report
> > parameter. Being a subquery or not really doesn't matter.
> >
> > Bruce L-C
> >
> > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > Hi All,
> > >
> > > There are two parameters in our DataSet based on the query below.
> > >
> > > @.Year causes "syntax or access violation" error, because it's in
subquery.
> > > @.Parameter2 works (in the main query)
> > > ----
> > > SELECT *,
> > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > THEN 'Green'
> > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > THEN 'Red' ELSE 'Black'
> > > END) AS Avail_color_MTD,
> > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > THEN 'Red' ELSE 'Black' END
> > > ) AS Resp_color_MTD
> > > FROM (
> > > SELECT
> > > Sla_Name_By_Loc,
> > > MAX(Sla_Definition) AS Sla_Definition,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Result_by_loc END) AS Av_MTD,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Result_by_loc END) AS Re_MTD
> > > FROM
> > > Sla_Results_Trans_BY_LOC
> > > WHERE (Sla_Category_id_By_Loc = 2)
> > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > AND (Sla_Month_by_loc = 9)
> > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > GROUP BY
> > > Sla_Name_By_Loc) Y
> > > where Y.Re_Comm = @.Parameter2 -- works!
> > > ORDER BY Sla_Name_By_Loc
> > > ---
> > >
> > > Please help!
> > >
> > > Tao
> >
> >
> >|||I am having the same problem.
I have selected the Generic Query pane and it let me then enter a parameter
value.
It ran but showed no data. The field names were shown but no data was shown.
Any help would be appreciated. Thank you.
"Bruce Loehle-Conger [MVP]" wrote:
> Got the solution for you. If you use the generic query instead (click on the
> button next to the !) then it will work. I think you have come across a
> limitation of the graphical designer to deal with subqueries and parameters.
> Works just the way you want from the query designer.
> Bruce L-C [MVP Reporting Services]
> "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > Such a dataset would fail:
> >
> > (pubs database)
> > SELECT au_lname
> > FROM authors
> > WHERE (au_id IN
> > (SELECT au_id
> > FROM titleauthor
> > WHERE title_id = @.title))
> >
> > Error message: "Parameter Information cannot be derived from SQL
> statements
> > with sub-select queries. Set parameter information before preparing
> command."
> >
> > But the parametere exists under Layout/Reports/Report Parameters and the
> > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > @.title =Parameters!title.Value
> >
> > Can some one try this out to see if you can succeed?
> >
> > Thanks,
> >
> > Tao
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Make sure that you have two report parameters. In layout, menu
> > > reports->report parameter. Next go to your dataset, ... , parameters tab
> and
> > > make sure that each of your query parameters are mapped to a report
> > > parameter. Being a subquery or not really doesn't matter.
> > >
> > > Bruce L-C
> > >
> > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > Hi All,
> > > >
> > > > There are two parameters in our DataSet based on the query below.
> > > >
> > > > @.Year causes "syntax or access violation" error, because it's in
> subquery.
> > > > @.Parameter2 works (in the main query)
> > > > ----
> > > > SELECT *,
> > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > THEN 'Green'
> > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > THEN 'Red' ELSE 'Black'
> > > > END) AS Avail_color_MTD,
> > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > THEN 'Red' ELSE 'Black' END
> > > > ) AS Resp_color_MTD
> > > > FROM (
> > > > SELECT
> > > > Sla_Name_By_Loc,
> > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Result_by_loc END) AS Re_MTD
> > > > FROM
> > > > Sla_Results_Trans_BY_LOC
> > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > AND (Sla_Month_by_loc = 9)
> > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > GROUP BY
> > > > Sla_Name_By_Loc) Y
> > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > ORDER BY Sla_Name_By_Loc
> > > > ---
> > > >
> > > > Please help!
> > > >
> > > > Tao
> > >
> > >
> > >
>
>|||Not the same problem. He was getting an error when trying to execute it. If
you are getting the field list back then that means that it is executing
correctly. Most likely something is not being specified correctly for your
parameter when you are prompted. What database are you going against (SQL
Server 2000?), what is the datatype of the field you are querying against?
How are you putting in the value when prompted for the parameter when you
execute it from the generic query pane.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> I am having the same problem.
> I have selected the Generic Query pane and it let me then enter a
parameter
> value.
> It ran but showed no data. The field names were shown but no data was
shown.
> Any help would be appreciated. Thank you.
> "Bruce Loehle-Conger [MVP]" wrote:
> > Got the solution for you. If you use the generic query instead (click on
the
> > button next to the !) then it will work. I think you have come across a
> > limitation of the graphical designer to deal with subqueries and
parameters.
> > Works just the way you want from the query designer.
> >
> > Bruce L-C [MVP Reporting Services]
> >
> > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > Such a dataset would fail:
> > >
> > > (pubs database)
> > > SELECT au_lname
> > > FROM authors
> > > WHERE (au_id IN
> > > (SELECT au_id
> > > FROM titleauthor
> > > WHERE title_id = @.title))
> > >
> > > Error message: "Parameter Information cannot be derived from SQL
> > statements
> > > with sub-select queries. Set parameter information before preparing
> > command."
> > >
> > > But the parametere exists under Layout/Reports/Report Parameters and
the
> > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > @.title =Parameters!title.Value
> > >
> > > Can some one try this out to see if you can succeed?
> > >
> > > Thanks,
> > >
> > > Tao
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > Make sure that you have two report parameters. In layout, menu
> > > > reports->report parameter. Next go to your dataset, ... , parameters
tab
> > and
> > > > make sure that each of your query parameters are mapped to a report
> > > > parameter. Being a subquery or not really doesn't matter.
> > > >
> > > > Bruce L-C
> > > >
> > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > Hi All,
> > > > >
> > > > > There are two parameters in our DataSet based on the query below.
> > > > >
> > > > > @.Year causes "syntax or access violation" error, because it's in
> > subquery.
> > > > > @.Parameter2 works (in the main query)
> > > > > ----
> > > > > SELECT *,
> > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > THEN 'Green'
> > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > THEN 'Red' ELSE 'Black'
> > > > > END) AS Avail_color_MTD,
> > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > THEN 'Red' ELSE 'Black' END
> > > > > ) AS Resp_color_MTD
> > > > > FROM (
> > > > > SELECT
> > > > > Sla_Name_By_Loc,
> > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > FROM
> > > > > Sla_Results_Trans_BY_LOC
> > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > AND (Sla_Month_by_loc = 9)
> > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > GROUP BY
> > > > > Sla_Name_By_Loc) Y
> > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > ORDER BY Sla_Name_By_Loc
> > > > > ---
> > > > >
> > > > > Please help!
> > > > >
> > > > > Tao
> > > >
> > > >
> > > >
> >
> >
> >|||Thank you very much for responding.
Problem fixed. When I clicked back from the Generic SQL Query and added more
fields, my query formatting changed somehow. Now it works great.
Thanks alot.
"Bruce L-C [MVP]" wrote:
> Not the same problem. He was getting an error when trying to execute it. If
> you are getting the field list back then that means that it is executing
> correctly. Most likely something is not being specified correctly for your
> parameter when you are prompted. What database are you going against (SQL
> Server 2000?), what is the datatype of the field you are querying against?
> How are you putting in the value when prompted for the parameter when you
> execute it from the generic query pane.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
> news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> > I am having the same problem.
> > I have selected the Generic Query pane and it let me then enter a
> parameter
> > value.
> > It ran but showed no data. The field names were shown but no data was
> shown.
> > Any help would be appreciated. Thank you.
> >
> > "Bruce Loehle-Conger [MVP]" wrote:
> >
> > > Got the solution for you. If you use the generic query instead (click on
> the
> > > button next to the !) then it will work. I think you have come across a
> > > limitation of the graphical designer to deal with subqueries and
> parameters.
> > > Works just the way you want from the query designer.
> > >
> > > Bruce L-C [MVP Reporting Services]
> > >
> > > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > > Such a dataset would fail:
> > > >
> > > > (pubs database)
> > > > SELECT au_lname
> > > > FROM authors
> > > > WHERE (au_id IN
> > > > (SELECT au_id
> > > > FROM titleauthor
> > > > WHERE title_id = @.title))
> > > >
> > > > Error message: "Parameter Information cannot be derived from SQL
> > > statements
> > > > with sub-select queries. Set parameter information before preparing
> > > command."
> > > >
> > > > But the parametere exists under Layout/Reports/Report Parameters and
> the
> > > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > > @.title =Parameters!title.Value
> > > >
> > > > Can some one try this out to see if you can succeed?
> > > >
> > > > Thanks,
> > > >
> > > > Tao
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > Make sure that you have two report parameters. In layout, menu
> > > > > reports->report parameter. Next go to your dataset, ... , parameters
> tab
> > > and
> > > > > make sure that each of your query parameters are mapped to a report
> > > > > parameter. Being a subquery or not really doesn't matter.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > > Hi All,
> > > > > >
> > > > > > There are two parameters in our DataSet based on the query below.
> > > > > >
> > > > > > @.Year causes "syntax or access violation" error, because it's in
> > > subquery.
> > > > > > @.Parameter2 works (in the main query)
> > > > > > ----
> > > > > > SELECT *,
> > > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > > THEN 'Green'
> > > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > > THEN 'Red' ELSE 'Black'
> > > > > > END) AS Avail_color_MTD,
> > > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > > THEN 'Red' ELSE 'Black' END
> > > > > > ) AS Resp_color_MTD
> > > > > > FROM (
> > > > > > SELECT
> > > > > > Sla_Name_By_Loc,
> > > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > > FROM
> > > > > > Sla_Results_Trans_BY_LOC
> > > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > > AND (Sla_Month_by_loc = 9)
> > > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > > GROUP BY
> > > > > > Sla_Name_By_Loc) Y
> > > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > > ORDER BY Sla_Name_By_Loc
> > > > > > ---
> > > > > >
> > > > > > Please help!
> > > > > >
> > > > > > Tao
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>