Union Problem - distinguishing between rows returned 1

[jmcd2000]

I want to produce a table of results - instead of having to write a separate query per value.

The problem is I have two select statements bringing back the same value from a table but it doesn't distinguish from which select statement the value comes from - any advice would be appreciated. The following query berings back two columns - CertifiedValue and Row...

(
SELECT TOP 11 TF.Value AS CertifiedValue, TF.Row
FROM TurnoverForecast TF,
TurnoverForecastSet TFS
WHERE TFS.TurnoverForecastSetID =
TF.TurnoverForecastSetID
AND TFS.MonthlyReviewID= @MonthlyReviewID
AND TF.Row BETWEEN 2 AND 12
AND TF.[Column] = 'CCt'
UNION
SELECT TOP 11 TF.Value AS ProfitValue, TF.Row
FROM TurnoverForecast TF,
TurnoverForecastSet TFS
WHERE TFS.TurnoverForecastSetID = TF.TurnoverForecastSetID
AND TFS.MonthlyReviewID= @MonthlyReviewID
AND TF.Row BETWEEN 2 AND 12
AND TF.[Column] = 'Pft')
ORDER BY TF.Row

 

[SQLSister]

Ad the [Column] value to your union statements, that will distiguish which one came from where. BTW, column is a very poor name for a column. As is Row. Try not to use reserved words as column Names.

 

[nigelrivett]

Just put a literal in the two selects to distiguish them.
It means that you may get duplicates across the two selects now.

(
SELECT TOP 11 sel = 'select1', TF.Value AS CertifiedValue, TF.Row
FROM TurnoverForecast TF,
TurnoverForecastSet TFS
WHERE TFS.TurnoverForecastSetID =
TF.TurnoverForecastSetID
AND TFS.MonthlyReviewID= @MonthlyReviewID
AND TF.Row BETWEEN 2 AND 12
AND TF.[Column] = 'CCt'
UNION
SELECT TOP 11 sel = 'select2', TF.Value AS ProfitValue, TF.Row
FROM TurnoverForecast TF,
TurnoverForecastSet TFS
WHERE TFS.TurnoverForecastSetID = TF.TurnoverForecastSetID
AND TFS.MonthlyReviewID= @MonthlyReviewID
AND TF.Row BETWEEN 2 AND 12
AND TF.[Column] = 'Pft')
ORDER BY TF.Row


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jmcd2000]

Thanks you both for your help.

Btw - I didn't design the dbase so can't avoid using row and column!

 

[Lekar]

Maybe you can rewrite your query in this way, just check the Top n value:

[tt]
SELECT TOP 22 TF.Value AS CertifiedValue, TF.Row, TF.[Column]
FROM TurnoverForecast TF,
TurnoverForecastSet TFS
WHERE TFS.TurnoverForecastSetID =
TF.TurnoverForecastSetID
AND TFS.MonthlyReviewID= @MonthlyReviewID
AND TF.Row BETWEEN 2 AND 12
AND TF.[Column] In ('CCt', 'Pft')
ORDER BY TF.Row
[/tt]

The TF.[Column] will distinguish from which where the value comes from.

Hope this help.

- Lenin.

 

[jmcd2000]

Thanks Lekar.
That does help.