March 7, 2013 at 8:00 am
Hi Guys!
I'm attempting to write a query that is using two variables, one will specify an ID to pull back, and that seems to be working fine...
The second variable allows the user to either specify a column TransType: 'BUY' 'SELL' or 'ALL'
Obviously if the user specifies 'BUY' I'd like only the 'BUY' from that column to be returned and vice versa...
I've attempted to do this by creating temp tables to solve this query... it however is not functional, any suggestions?
Begin
declare @idnum varchar
select @idnum = 1
declare @TC varchar(50)
select @TC = 'BUY'
select B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
WHERE TransCode = @TC
IF @TC = 'all'
BEGIN
select B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
END
END
March 7, 2013 at 8:28 am
What do you mean by not functional?
I am not sure you need the temp tables. Does this help?
IF (@TC = 'BUY')
BEGIN
UPDATE....WHERE A.TransType = 'BUY'
END
IF (@TC = 'SELL')
BEGIN
UPDATE....WHERE A.TransType = 'SELL'
END
IF (@TC = 'ALL')
BEGIN
UPDATE....WHERE A.TransType IN ('BUY','SELL')
END
Before you start updating data please test with SELECT statements first to validate.
March 7, 2013 at 10:51 am
That query was not functioning properly.
The below query is actually pulling back the proper data. However, when I use 'ALL' as my variable, it is running the query twice (the way I have my IF statement setup) any ideas on how to beat this?
Begin
declare @idnum varchar
select @idnum = 1
declare @TC varchar(50)
select @TC = 'BUY'
select B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
WHERE TransCode = @TC
IF @TC = 'all'
BEGIN
select B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
END
END
March 7, 2013 at 11:06 am
What are the possible values for field transcode?
1) Buy or sell; or
2) Buy, Sell or All
Is 'All' actually a transcode or does all indicate that you want both buys and sells.
Can you can post your question according to these guidelines?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 7, 2013 at 11:16 am
Chrissy,
I took a look at the guidelines, and I can't seem to figure out exactly how to post the data the way they've shown. I've done my best to make it presentable and easy to read. To answer your question; 'ALL' is not a field within TransCode, there are several different data including 'BUY' and 'SELL'. When I use 'ALL' as a variable, I would just like the column to return everything back. (i.e - BUY, SELL, HOLD, CONSTRAIN, ON ACCOUNT, etc.)
BEGIN
DECLARE @idnum varchar
SELECT @idnum = 1
DECLARE @TC varchar(50)
SELECT @TC = 'BUY'
SELECT B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
WHERE TransCode = @TC
IF @TC = 'all'
BEGIN
SELECT B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
END
[EDIT] P.S - None of my formatting is actually showing up, my apologies
March 7, 2013 at 11:25 am
Is the purpose of the @TC parameter to:
1) Control the number of records returned, or
2) Control the appearance of the data returned (the number of records returned will always be the same no matter what the parameter value is)
March 7, 2013 at 11:28 am
The parameter is to limit the data.
If 'BUY' is entered for the parameter, only buys would be returned
If 'SELL' is entered, only sells would be returned
however, if you enter 'ALL' a combination of BUY, SELL, CONSTRAIN, HOLD, ON ACCOUNT, etc. would be returned (thus being the entire column)
March 7, 2013 at 11:29 am
That clarified matters. Standby...
March 7, 2013 at 11:43 am
Below is the simplest way.
There are alternative ways where you don't have to repeat the SELECT statement. I would go tend to use the simpler way unless the SELECT statement is highly complicated and I don't want to repeat it.
CREATE TABLE #Test (TranCode varchar(20))
INSERT INTO #Test (TranCode) VALUES ('BUY')
INSERT INTO #Test (TranCode) VALUES ('SELL')
INSERT INTO #Test (TranCode) VALUES ('CONSTRAIN')
INSERT INTO #Test (TranCode) VALUES ('HOLD')
INSERT INTO #Test (TranCode) VALUES ('ON ACCOUNT')
DECLARE @TC varchar(50)
--SELECT @TC = 'BUY'
SELECT @TC = 'ALL'
IF @TC = 'BUY' OR @TC = 'SELL'
BEGIN
SELECT
*
FROM #Test
WHERE
TranCode = @TC
END
ELSE
BEGIN
SELECT
*
FROM #Test
END
DROP TABLE #Test
March 7, 2013 at 11:51 am
Alternate way below. If you can confirm that we have clarified the problem and that the solution works maybe one of the more experienced posters can speak to best practices in dealing with this particular issue...
CREATE TABLE #Test (TranCode varchar(20))
INSERT INTO #Test (TranCode) VALUES ('BUY')
INSERT INTO #Test (TranCode) VALUES ('SELL')
INSERT INTO #Test (TranCode) VALUES ('CONSTRAIN')
INSERT INTO #Test (TranCode) VALUES ('HOLD')
INSERT INTO #Test (TranCode) VALUES ('ON ACCOUNT')
DECLARE @TC varchar(50)
--SELECT @TC = 'BUY'
--SELECT @TC = 'SELL'
SELECT @TC = 'ALL'
IF @TC = 'ALL' SET @TC = NULL
SELECT
*
FROM #Test
WHERE
TranCode = @TC OR @TC IS NULL
DROP TABLE #test
March 7, 2013 at 12:47 pm
Chrissy,
Thanks so much for all your help. Using the IF statements worked. I built it down to be a little more concise just using Boolean logic
Begin
declare @idnum varchar
select @idnum = 1
declare @TC varchar(50)
select @TC = 'all'
select B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = @idnum
WHERE
@TC = 'ALL'
OR
( @TC IN ('BUY','SELL') AND TransCode = @TC )
END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply