Combining select with UNION not giving same result

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)

Combining select with UNION not giving same result

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 13:07:03

Hi,

The below query needs teh result combined. I tried UNION but it did not help. I get the result as below but I believed UNION could help solve this. What am I doing wrong?

DECLARE @HideEntityDetails TABLE (UDFTransID int, HideEntityDetails bit)
INSERT INTO @HideEntityDetails
SELECT
OwnerID as UDFTransID, Convert(bit,UDFValue)
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')
--UNION
SELECT t.TransferID AS UDFTransID,hed.HideEntityDetails from @HideEntityDetails hed
LEFT JOIN Transfers t
ON hed.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6
select * from @HideEntityDetails

UDFTransID HideEntityDetails
14784 1
14785 1

UDFTransID HideEntityDetails
14759 1
33246 1
33251 1

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 13:18:24

I also forgot to mention that I am using SQL 2005. Also the first select is from a function. Does UNION work with multi selects from function and table?

Thanks
sqlnovice123

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 13:29:43

DECLARE @HideEntityDetails TABLE (UDFTransID int, HideEntityDetails bit)

INSERT INTO @HideEntityDetails
SELECT
OwnerID as UDFTransID, Convert(bit,UDFValue)
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')
INSERT INTO @HideEntityDetails
SELECT t.TransferID AS UDFTransID,hed.HideEntityDetails from @HideEntityDetails hed
LEFT JOIN Transfers t
ON hed.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6
select * from @HideEntityDetails

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 13:34:31

Hi,

I did the same thing but my Boss felt that it is inefficient. Seems there is a beter way to write the query without the second INSERT.

Thanks
sqlnovice123

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 13:45:00

Cant you do a join with of UDF & table transfer?Something like this



DECLARE @HideEntityDetails TABLE (UDFTransID int, HideEntityDetails bit)

INSERT INTO @HideEntityDetails

SELECT
OwnerID as UDFTransID, Convert(bit,UDFValue)
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')hed
LEFT JOIN Transfers t
ON hed.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6

Since I have no I idea on what your data looks like its just a shot in the dark.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 13:54:11

I get the below error:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'UDFTransID'.

Just so you know this is teh function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GFS_FN_GetUDFValues]
(@udfLevel nvarchar(30), @udfCode nvarchar(100))
RETURNS TABLE
AS
RETURN
SELECT
DF.PshipID
,DF.OwnerID
,DF.UDFValue
,DF.SubEntityID
,DF.InvestorClassID
,FI.[Type]
,VAL.UDFItemChoiceValue
,VAL.UDFItemChoiceOrder
FROM
UserDefinedFieldItem FI
INNER JOIN UserDefinedField DF ON FI.UDFItemID = DF.UDFItemID
LEFT JOIN UserDefinedFieldItemChoice VAL on DF.UDFValue = VAL.UDFItemChoiceID
WHERE
FI.UDFLevel = @udfLevel AND
FI.UDFCode = upper(@udfCode)

The Transfers table has UDFTransID defined as an INT type.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 13:57:52

Is there a way to get it to work with a subquery? If there is not otehr way other than the double INSERT then I guess I will stick to that approach.

Thanks
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 14:32:19

I tried taking the SQL from teh function and just combined with UNION as one combines two SQL queries. It still returns 3 rows as opposed to 5 that I should see, not sure why?

DECLARE @udfLevel nvarchar(30)
DECLARE @udfCode nvarchar(100)
SET @udfLevel = 'Transfer'
SET @udfCode = 'HideEntityDetails'
DECLARE @HideEntityDetails TABLE (UDFTransID int, HideEntityDetails bit)

SELECT

DF.OwnerID
,Convert(bit,DF.UDFValue)

FROM
UserDefinedFieldItem FI
INNER JOIN UserDefinedField DF ON FI.UDFItemID = DF.UDFItemID
LEFT JOIN UserDefinedFieldItemChoice VAL on DF.UDFValue = VAL.UDFItemChoiceID
WHERE
FI.UDFLevel = @udfLevel AND
FI.UDFCode = upper(@udfCode)
UNION
SELECT t.TransferID AS UDFTransID,hed.HideEntityDetails from @HideEntityDetails hed
LEFT JOIN Transfers t
ON t.ParentTransferID = hed.UDFTransID--hed.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6

select * from @HideEntityDetails

OwnerID (No column name)
14759 1
33246 1
33251 1

I need to get the below

UDFTransID HideEntityDetails
14759 1
33246 1
33251 1
14784 1
14785 1

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 15:05:25

Could it be that UNION does not work with table variables? It does not make sense, but yet it seems simple. I have worked with unions on temp and work tables, but not with selects from function or table variables.

Any help is really appreciated. I am told to get this to work.

Thanks
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 15:18:10

Could it have anything to do with the settings on SQL? Even the below query does not work with UNION and it should as it uses temp tables?

drop table #HideEntityDetails
DECLARE @HideEntityDetails TABLE (UDFTransID int, HideEntityDetails bit)
CREATE TABLE #HideEntityDetails(UDFTransID int, HideEntityDetails bit)
--INSERT INTO #HideEntityDetails
SELECT
OwnerID as UDFTransID, Convert(bit,UDFValue)
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')
--select * from #HideEntityDetails
UNION

SELECT t.TransferID AS UDFTransID,hed.HideEntityDetails AS HideEntityDetails
FROM #HideEntityDetails hed
LEFT JOIN Transfers t
ON hed.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-28 : 15:33:54

You have commented the INSERT into #HideEntityDetails in the first query....and you are trying to Join with #HideEntityDetails in the second SELECT which wont give you any rows...as #HideEntityDetails is empty?? Or am I missing something?

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 15:39:24

Sorry that was a typo.

drop table #HideEntityDetails
CREATE TABLE #HideEntityDetails(UDFTransID int, HideEntityDetails bit)
INSERT INTO #HideEntityDetails
SELECT
OwnerID as UDFTransID, Convert(bit,UDFValue) AS HideEntityDetails
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')
UNION

SELECT t.TransferID AS UDFTransID,hed.HideEntityDetails AS HideEntityDetails
FROM #HideEntityDetails hed
LEFT JOIN Transfers t
ON hed.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6

select * from #HideEntityDetails

The above query gives me only three rows as opposed to 5 rows that I am expecting as was the case when I used two inserts.

UDFTransID HideEntityDetails
14759 1
33246 1
33251 1

should give me

UDFTransID HideEntityDetails
14759 1
33246 1
33251 1
14784 1
14785 1

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 15:58:36

Initially I presumed one cannot use UNION with selects from a function and a table variable. So I used UNION with select from a function and a temp table. This still does not give the result set that I need. Is teh function the cause or has it to do with my settings. Is there another option that I can use?

Thanks
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 16:15:01

I tried the subquery approach but that did not work. I get a syntax error.

SELECT t.TransferID AS UDFTransID,hed.HideEntityDetails AS HideEntityDetails
INTO #HideEntityDetails
FROM (SELECT OwnerID as UDFTransID, Convert(bit,UDFValue) AS HideEntityDetails
INTO #HideEntityDetails
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')) AS d

LEFT JOIN Transfers t
ON d.UDFTransID = t.ParentTransferID
WHERE t.StatusID = 6

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'INTO'.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-28 : 16:58:17

I just did a quick scan, but will this work?

SELECT 
	t.TransferID AS UDFTransID,
	hed.HideEntityDetails 
from 
(
	SELECT OwnerID as UDFTransID, Convert(bit,UDFValue) AS HideEntityDetails
	FROM dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails')
) AS hed
LEFT JOIN 
	Transfers t
	ON hed.UDFTransID = t.ParentTransferID
WHERE 
	t.StatusID = 6

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-07-28 : 17:23:38

I used a CASE statement

SELECT
UDFTransID = CASE WHEN fn.OwnerID = t.ParentTransferID THEN t.TransferID ELSE fn.OwnerID END
, HideEntityDetails = Convert(bit,UDFValue)
FROM
dbo.GFS_FN_GetUDFValues('Transfer','HideEntityDetails') fn
LEFT JOIN Transfers t
ON fn.OwnerID = t.ParentTransferID
AND t.StatusID = 6

UDFTransID HideEntityDetails
14784 1
14785 1
33251 1
33246 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources