Query Help - SQL Server Forums

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)

Query Help

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-16 : 05:05:35

Hi,

Mine Below Query is taking too much time. I want to remove UNION ALL Condition.

Is there any alternative that i can use instead of Union All?

SELECT
TradeVisionFileId SETId,
MatchDate MatchDate,
EntityId EntityId,
BuyerId CustomerId,
BuyerState CustomerState,
Done Done,
FarDate MaturityDate,
Amount Amount,
BuyerBro CustomerBro,
BuyerAmt CustomerAmt,
TradeVisionId TradeVisionID,
'BUYER' Customer_Role,
SellerState Other_Party_State,
Currency Currency_Id
FROM dbo.TradeVisionRR TRR
WHERE
TradeVision_Version = dbo.GET_TRADEVISIONRR_MAX_VERSION(@p_Location_Code,@p_Sector_Id,@p_Match_Date,TRR.TRADEVISIONID)
AND EntityId IN ( Select Distinct Entity_Id From Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))

------------------------------------
UNION ALL
------------------------------------

SELECT
TradeVisionFileId SETId,
MatchDate MatchDate,
EntityId EntityId,
BuyerId CustomerId,
BuyerState CustomerState,
Done Done,
NearDate MaturityDate,
Amount * (-1) Amount,
BuyerBro * (-1) CustomerBro,
BuyerAmt * (-1) CustomerAmt,
TradeVisionId TradeVisionID,
'SELLER' Customer_Role,
SellerState Other_Party_State,
Currency Currency_Id
FROM dbo.TradeVisionRR TRR
WHERE
TradeVision_Version = DBO.GET_TRADEVISIONRR_MAX_VERSION(@p_Location_Code,@p_Sector_Id,@p_Match_Date,TRR.TRADEVISIONID) AND
EntityId IN ( Select Distinct Entity_Id From DBO.Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))

------------------------------------
UNION ALL
------------------------------------

SELECT
TradeVisionFileId SETId,
MatchDate MatchDate,
EntityId EntityId,
SellerId CustomerId,
SellerState CustomerState,
Done Done,
FarDate MaturityDate,
Amount * (-1) Amount,
SellerBro * (-1) CustomerBro,
SellerAmt * (-1) CustomerAmt,
TradeVisionId TradeVisionID,
'SELLER' Customer_Role,
BuyerState Other_Party_State,
Currency Currency_Id
FROM DBO.TradeVisionRR TRR
WHERE
TradeVision_Version = DBO.GET_TRADEVISIONRR_MAX_VERSION(@p_Location_Code,@p_Sector_Id,@p_Match_Date,TRR.TRADEVISIONID) AND
EntityId IN ( Select Distinct Entity_Id From DBO.Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))

------------------------------------
UNION ALL
------------------------------------

SELECT
TradeVisionFileId SETId,
MatchDate MatchDate,
EntityId EntityId,
SellerId CustomerId,
SellerState CustomerState,
Done Done,
NearDate MaturityDate,
Amount Amount,
SellerBro CustomerBro,
SellerAmt CustomerAmt,
TradeVisionId TradeVisionID,
'BUYER' Customer_Role,
BuyerState Other_Party_State,
Currency Currency_Id
FROM DBO.TradeVisionRR TRR
WHERE
TradeVision_Version = DBO.GET_TRADEVISIONRR_MAX_VERSION(@p_Location_Code,@p_Sector_Id,@p_Match_Date,TRR.TRADEVISIONID) AND
EntityId IN ( Select Distinct Entity_Id From DBO.Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))

-- Regards
Prashant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:11:25

All the select batches looks same. Then how are you distingusihing b/w SELLER,BUYER...?

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-16 : 05:21:15

Thanks visakh.

Ya, at first look it looks like same. Basically i want to devide mine one row into four rows depending upon the combinations of "FarDate, NearDate, BuyerId, SellerId".

That is Mine
1st Row contains data for the combinations of (BuyerId, FarDate)
2nd Row contains data for the combinations of (BuyerId, NearDate)
3rd Row contains data for the combinations of (SellerId, FarDate)
4th Row contains data for the combinations of (SellerId, NearDate)

I think i am clear with the requirenments........

Please, help me....

-- Regards
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:24:25

Nope. you're not. We will find it eaiser if you illustrate what you want by giving some sample data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-16 : 06:10:39

[code]CREATE TABLE #TradeVersion
(
TRADEVISIONID {Data type here},
Data {Data type here}
)

INSERT #TradeVersion
(
Data
)
SELECT DISTINCT,
TRADEVISIONID,
dbo.GET_TRADEVISIONRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, TRADEVISIONID)
FROM dbo.TradeVisionRR

DECLARE @Entity_Id {Data type here}

SELECT @EntityID = Entity_Id
FROM Fn_Get_Allowed_Entity_List(@p_Location_Code, @p_Sector_Id, @p_Match_Date, @p_UserID)

SELECT trr.TradeVisionFileId AS ASSETId,
trr.MatchDate AS MatchDate,
trr.EntityId AS EntityId,
CASE y.id
WHEN 1 THEN trr.BuyerId
WHEN 2 THEN trr.BuyerId
WHEN 3 THEN trr.SellerId
WHEN 4 THEN trr.SellerId
END AS CustomerId,
CASE y.id
WHEN 1 THEN trr.BuyerState
WHEN 2 THEN trr.BuyerState
WHEN 3 THEN trr.SellerState
WHEN 4 THEN trr.SellerState
END AS CustomerState,
trr.Done AS Done,
CASE y.id
WHEN 1 THEN trr.FarDate
WHEN 2 THEN trr.NearDate
WHEN 3 THEN trr.FarDate
WHEN 4 THEN trr.NearDate
END AS MaturityDate,
trr.Amount * y.theSign AS Amount,
CASE y.id
WHEN 1 THEN trr.BuyerBro
WHEN 2 THEN trr.BuyerBro
WHEN 3 THEN trr.SellerBro
WHEN 4 THEN trr.SellerBro
END * y.theSign AS CustomerBro,
CASE y.id
WHEN 1 THEN trr.BuyerAmt
WHEN 2 THEN trr.BuyerAmt
WHEN 3 THEN trr.SellerAmt
WHEN 4 THEN trr.SellerAmt
END * y.theSign AS CustomerAmt,
trr.TradeVisionId AS TradeVisionID,
y.Customer_Role,
CASE y.id
WHEN 1 THEN trr.SellerState
WHEN 2 THEN trr.SellerState
WHEN 3 THEN trr.BuyerState
WHEN 4 THEN trr.BuyerState
END AS Other_Party_State,
trr.Currency AS Currency_Id
FROM dbo.TradeVisionRR AS trr
INNER JOIN #TradeVersion AS tv ON tv.TRADEVISIONID = trr.TRADEVISIONID
AND tv.Data = trr.TradeVision_Version
CROSS JOIN (
SELECT 1 AS id, 'Buyer' AS Customer_Role, 1 AS theSign UNION ALL
SELECT 2, 'Seller', -1 UNION ALL
SELECT 3, 'Seller', -1 UNION ALL
SELECT 4, 'Buyer', 1 UNION ALL
) AS y
WHERE TRR.EntityId = @EntityID[/code]

E 12°55'05.25"
N 56°04'39.16"

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-17 : 06:19:56

Hi,

Thanks a lot Peso.

It's working for me.....

-- Regards
Prashant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources