Sql Parameters in a Union

[hlardieri]

HI,

I am not sure if I should post in SQL or Office.

Can you add parameters in sql if the query is written as a union?

I am creating a dashboard in Excel 2003 using sql.
I have all the queries done and am now trying to add parameters. IF I only add the question mark for the one part of the union. My data will refresh for that section in Excel but if I try to add the question marks for all sections in the Union I get column prefix xxx does not match with a table name or alias.

Thanks
Hope

 

[RiverGuy]

I'm guessing it's more of an Excel/OLEDB issue. Are the parameters for the same column in each part of the union? You could try wrapping it in a derived table:

SELECT * FROM

(SELECT a.*
FROM Table1 a

UNION ALL

SELECT b.*
FROM Table2 b) c

WHERE SomeColumn = ?

 

[hlardieri]

unfortunately I am not an expert in sql would i be able to still have a where statement inside each union.
Here is one of the sql unions
I want FISCALYEAR AND FISCALPERIOD TO =?

select ASI.BUSGROUP,SUM(ASI.XTNDPRCE),SUM(ASI.EXTDCOST)
from FAIRFIELDSQL.ASI.dbLI80100A ASI left outer join
FAIRFIELDSQL.ASI.dbo.SY40100 FISCALASI on ASI.FISCALYEAR = FISCALASI.YEAR1 AND
ASI.FISCALPERIOD = FISCALASI.PERIODID
where ASI.FISCALYEAR = '2009'
AND ASI.FISCALPERIOD = '1'
AND FISCALASI.FORIGIN = 1
and ASI.CUSTNMBR <> 'ZANICHELLI' AND
ASI.CUSTNMBR <> 'ZANETTI'
group by ASI.BUSGROUP
union
seleCt ASMW.BUSGROUP,SUM(ASMW.XTNDPRCE),SUM(ASMW.EXTDCOST)
from GPSQL.ASMW.dbLI80100A ASMW left outer join
GPSQL.ASMW.dbo.SY40100 FISCALMW on ASMW.FISCALYEAR = FISCALMW.YEAR1 AND ASMW.FISCALPERIOD = FISCALMW.PERIODID
where ASMW.FISCALYEAR = '2009'
AND ASMW.FISCALPERIOD = '1'
AND FISCALMW.FORIGIN = 1
and ASMW.CUSTNMBR <> 'ARTSCH'
AND ASMW.CUSTNMBR <>'ARTSCHWEST'
group by ASMW.BUSGROUP
UNION
SELECT ASW.BUSGROUP,SUM(ASW.XTNDPRCE),SUM(ASW.EXTDCOST)
FROM ASW.dbLI80100A AS ASW LEFT OUTER JOIN
FAIRFIELDSQL.ASWLL.dbo.SY40100 AS FISCALASWLL ON ASW.FISCALYEAR = FISCALASWLL.YEAR1 AND
ASW.FISCALPERIOD = FISCALASWLL.PERIODID LEFT OUTER JOIN
ASW.dbo.RM00101 AS RM00101 ON ASW.CUSTNMBR = RM00101.CUSTNMBR
WHERE (ASW.FISCALYEAR = '2009') AND (ASW.FISCALPERIOD = '1') and FISCALASWLL.FORIGIN = 1
AND (ASW.CUSTNMBR <> 'ZANICHELLI')
GROUP BY ASW.BUSGROUP
ORDER BY BUSGROUP

 

[RiverGuy]

The only other options I can think of would be to:

1. Redesign your query so that you do the aggregations outside of the derived table like my example above. That way, your data is already "unioned" when you apply the filters.

or

2. Try to convert it to a stored procedure. I'm not sure if you can call a stored procedure like EXEC p_SomeProcName ? ? , but someone else might know right off hand.

 

[hlardieri]

Thanks

as a quick fix i ended up using a query for each line of code in my excel dashboard.(basically separating the unions)
I will try to recode using your suggestions

One more question My Excel dashboard has another sheet that
I created a sql crosstab query for it. It looks great but now I need to have a parameter for the year. I tried to just add the parameter but it is giving me an invalid descriptor index error. Can I not use parameters in a crosstab query? It basically looks like this (i did shorten it to display but it will repeat over for 12 months
select
SUM (case when ASI.FISCALYEAR = ? AND FISCALASI.FORIGIN = 1
AND ASI.FISCALPERIOD = '1' then ASI.XTNDPRCE else 0 end) ,
SUM (case when ASI.FISCALYEAR = ? AND FISCALASI.FORIGIN = 1
AND ASI.FISCALPERIOD = '2' then ASI.XTNDPRCE else 0 end) ,
,
sum(XTNDPRCE)
from FAIRFIELDSQL.ASI.dbLI80100A ASI left outer join
FAIRFIELDSQL.ASI.dbo.SY40100 FISCALASI on ASI.FISCALYEAR = FISCALASI.YEAR1 AND
ASI.FISCALPERIOD = FISCALASI.PERIODID
where ASI.FISCALYEAR = ? AND FISCALASI.FORIGIN = 1
AND ASI.FISCALPERIOD between '1' and '12'