Question on select into temp & UNION

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

SQL Server 2005 Forums

Transact-SQL (2005)

Question on select into temp & UNION

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-21 : 11:50:36

I need help on an alternate method instead of using IF for getting my final output. Is ExecSQl a good option to use?

I need to have the output selected based on the parameter value I pass for @Type which is one among several parameters that I pass to the stored proc will have any one of the below 4 values.

'Inquiry Volume', 'Trade Volume', 'Inquiry Count', 'Trade Count'

I should have only one final select as output for a crystal report.

I need the UNION as I have a column called GroupSort which combines data for Single and Multiple Clients selection.

I get the error below as I believe I can do s select INTO just once.

Msg 2714, Level 16, State 1, Line 600
There is already an object named '#Final' in the database.

IF @Type = 'Trade Volume'
BEGIN
SELECT

GroupSort,
@ClientTypeValueName AS GroupType ,
NULL,
NULL,
NULL,
TradeVol,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY
INTO #Final
FROM

#GraphResultsSingle

UNION

SELECT
GroupSort,
GroupType ,
NULL,
NULL,
NULL,
TradeVol,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

FROM
#GraphResultsMultiple
END
ELSE
BEGIN
IF @Type = 'Inquiry Volume'

SELECT

GroupSort,
@ClientTypeValueName AS GroupType ,
NULL,
InquiryVol,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

INTO #Final
FROM
#GraphResultsSingle

UNION

SELECT
GroupSort,
GroupType ,
NULL,
InquiryVol,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

FROM
#GraphResultsMultiple
END
select * from #Final
GO

ELSE
BEGIN
IF @Type = 'Trade Count'

SELECT

GroupSort,
@ClientTypeValueName AS GroupType ,
NULL,
NULL,
TradeCount,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

INTO #Final
FROM
#GraphResultsSingle

UNION

SELECT
GroupSort,
GroupType ,
NULL,
NULL,
TradeCount,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

FROM
#GraphResultsMultiple
END
ELSE
BEGIN
IF @Type = 'Inquiry Count'

SELECT

GroupSort,
@ClientTypeValueName AS GroupType ,
InquiryCount,
NULL,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

INTO #Final
FROM
#GraphResultsSingle

UNION

SELECT
GroupSort,
GroupType ,
InquiryCount,
NULL,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

FROM
#GraphResultsMultiple
END

select * from #Final
GO

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-21 : 11:55:12

I guess I can create the table #Final once and the use INSERT instead of SELECT INTO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 12:06:51

Here is another way to try:


select * 
into #Final
from
(
--@Type = 'Trade Volume'
SELECT
GroupSort,
@ClientTypeValueName AS GroupType ,
NULL,
NULL,
NULL,
TradeVol,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsSingle
where @Type = 'Trade Volume'

UNION

SELECT
GroupSort,
GroupType ,
NULL,
NULL,
NULL,
TradeVol,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsMultiple
where @Type = 'Trade Volume'

UNION

--@Type = 'Inquiry Volume'
SELECT
GroupSort,
@ClientTypeValueName AS GroupType ,
NULL,
InquiryVol,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsSingle
where @Type = 'Inquiry Volume'

UNION

SELECT
GroupSort,
GroupType ,
NULL,
InquiryVol,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsMultiple
where @Type = 'Inquiry Volume'

UNION

--@Type = 'Trade Count'
SELECT
GroupSort,
@ClientTypeValueName AS GroupType ,
NULL,
NULL,
TradeCount,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsSingle
where @Type = 'Trade Count'

UNION

SELECT
GroupSort,
GroupType ,
NULL,
NULL,
TradeCount,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsMultiple
where @Type = 'Trade Count'

UNION

--@Type = 'Inquiry Count'
SELECT
GroupSort,
@ClientTypeValueName AS GroupType ,
InquiryCount,
NULL,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsSingle
where @Type = 'Inquiry Count'

UNION

SELECT
GroupSort,
GroupType ,
InquiryCount,
NULL,
NULL,
NULL,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut, 
StartPreviousFY,
EndPreviousFY
FROM #GraphResultsMultiple
where @Type = 'Inquiry Count'
) as dt

-- and then you can do
select * from #Final

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-21 : 12:50:41

Hi thanks.

I tried that but I get these error messages.
I tried qualifying the NULL values with clumn names like InquiryCount As NULL b ut that did not work either.

Thanks

Msg 8155, Level 16, State 2, Line 739
No column was specified for column 3 of 'dt'.
Msg 8155, Level 16, State 2, Line 739
No column was specified for column 4 of 'dt'.
Msg 8155, Level 16, State 2, Line 739
No column was specified for column 5 of 'dt'.
Msg 1038, Level 15, State 5, Line 739
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 1038, Level 15, State 5, Line 739
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 1038, Level 15, State 5, Line 739
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-21 : 13:15:07

I tried the INSERT but for some reaosn I get the below error and I am unable to fix this.

CREATE TABLE #Final
(

GroupSort int,
GroupClientType varchar(160),
--ClientTypeValueName varchar(160),
InquiryCount float,
InquiryVol money,
TradeCount float,
TradeVol money ,
ProductName varchar(500),
ClientName varchar(100),
Client varchar(200) ,
ProductType varchar(100),
ClientTypeName varchar(8000),
SizeBucket int,
DateBreakOut varchar(20),
StartPreviousFY varchar(12),
EndPreviousFY varchar(12)
)
IF @Type = 'Trade Volume'
BEGIN
INSERT #Final
(

)
SELECT

GroupSort,
GroupType ,
NULL,
NULL,
NULL,
TradeVol,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

FROM

#GraphResultsSingle

UNION

SELECT
GroupSort,
ClientTypeValueName ,
NULL,
NULL,
NULL,
TradeVol,
ProductName ,
ClientName,
Client,
ProductType,
ClientTypeName ,
SizeBucket,
DateBreakOut,
StartPreviousFY,
EndPreviousFY

FROM
#GraphResultsMultiple

END

Msg 215, Level 16, State 1, Line 553
Parameters supplied for object '#Final' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-21 : 15:07:09

Remove the braces after the INSERT statement. You dont need them

INSERT into #Final
SELECT ....FROM table

INSERT into #Final (column1 , column2...etc)
SELECT col1, col2..etc FROM table

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-21 : 15:21:03

Thanks. i actually added the column names and it worked.

Now I have the below layout:

GroupSort GroupType TradeVolume
1 Single 3412
1 Single 2567
1 Single 1234
2 Multiple 3400
2 Multiple 2500
2 Multiple 1200

Since I am doing a 3D bar graph in Crystal comparing All Vs Single for TradeVolume, i would like to have the layout as

GroupSort GroupType TradeVolumeSingle TradeVolumeMultiple
1 Single 3412 3400
1 Single 2567 2500
1 Single 1234 1200

Where i need the values for Trade Volume for the second group (Multiple) as another column.

Is SELF joining to the temp tabel the best and easy way?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-21 : 15:30:35

I dont see any field that is common between the both to do a self-join.

Infact your result set doesn't make much sense to me..You still have Group Sort as 1 and have the values corresponding to "Multiple". Can you explain how you arrived at this result set.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-21 : 15:48:01

Oops.

I will add an identity column to the temp table

CREATE TABLE #Final
(

i int IDENTITY (1, 1) NOT NULL ,
GroupSort int,
GroupClientType varchar(160),
--ClientTypeValueName varchar(160),
InquiryCount float,
InquiryVol money,
TradeCount float,
TradeVol money ,
ProductName varchar(500),
ClientName varchar(100),
Client varchar(200) ,
ProductType varchar(100),
ClientTypeName varchar(8000),
SizeBucket int,
DateBreakOut varchar(20),
StartPreviousFY varchar(12),
EndPreviousFY varchar(12)
)

GroupSort GroupType TradeVolume
1 Single 3412
1 Single 2567
1 Single 1234
2 Multiple 3400
2 Multiple 2500
2 Multiple 1200

Since I am doing a 3D bar graph in Crystal comparing Trade Volume for All Vs Trade Volume for Single based on the selection type say TradeVolume(or Inquiry Volume), i would like to have the layout as

GroupSort GroupType TradeVolumeSingle TradeVolumeMultiple
1 Single 3412 3400
1 Single 2567 2500
1 Single 1234 1200

Where i need the values for Trade Volume for the second group (Multiple) as another separate column and these values belong to group 2(Multiple).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources