Question on selecting multiple values from a colum

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 selecting multiple values from a colum

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 09:12:23

Hi,

I have a question on the following:

I have a temp table that I am inserting data into from say a product table as below. The below query will insert one value for Product and Protocol.

However we need to display the name of product for the follwoing values in a drop down list and there a a GUI tool that enables user to select multiple values:

values are 1,2,3,17,4,40,6,7,19,20

and 1 will correspondingly display name High Grade and so on.

Similarly I have another table Protocol and the names for multiple selected values of 1,2,3,4 need to show up on teh GUI in a drop down.

I have the sample code I plan on using for the multiple product selection. How do I implement this code in the temp table insert? Should I combine the single and multiple product selection or should I separately insert in the temp table for either single or multipl , the flexibility that users should have.?

How do I combine the below code if this is correct to the temp table inser?

DECLARE @ProductTBL TABLE
(
ProductID int
)

INSERT @ProductTBL
SELECT DISTINCT
ProductID
FROM
Product
WHERE
ProductID IN (SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product)

IF (SELECT COUNT(*) FROM @ProductTBL) = 1
BEGIN
SET @ProductSelect =
(SELECT [Description]
FROM
Product
WHERE ProductID in (SELECT ProductID FROM @ProductTBL))
END
ELSE
BEGIN
SET @ProductSelect = '*Multiple Products'
END

SELECT
MISInquiryID = trd.MISInquiryID,
ClientID = trd.PPivotalCompanyID,
DealerId = CPPivotalCompanyID,
ProtocolID = trd.ProtocolID,
CPLevel = trd.Level,
TradeMonth = right('0' + convert(varchar(2), datepart(mm, trd.TradeDate)), 2),
TradeMonthName = datename(mm, trd.TradeDate),
TradeYear = convert(varchar(4), datepart(yy, trd.TradeDate)),
Product = prd.ShortName,
ClientType = dlr.MA_Type,
[TEMP_SizeBucket]= CASE
WHEN iql.USDSize <1000 THEN '<1000'
WHEN iql.USDSize >= 1000 AND iql.USDSize <5000 THEN '>= 1000 & <5000'
WHEN iql.USDSize >=5000 AND iql.USDSize <10000 THEN '>= 5000 & <10000'
ELSE '>= 10000' END,
[TEMP_IsBuy] = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END

INTO
#Trade
FROM
Trade trd
INNER JOIN
pivotal..Company dlr
ON
trd.CPPivotalCompanyID = dlr.Company_Id
INNER JOIN Product prd
ON
trd.ProductID = prd.ProductID
INNER JOIN
Inquiry inq
ON
trd.MISInquiryID = inq.MISInquiryID
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

where

datepart(yy, trd.TradeDate) = datepart(yy, @EndDate)
and trd.ProtocolID = @ProtocolID --(values 1,2,3,4)
and trd.IsVolume = 1
--and NOT trd.CPPivotalCompanyID in (select DealerID from #Final)
--and NOT trd.CPPivotalCompanyID in (select DealerID from #Final)
and prd.ProductID = @ProductID --(values 1,2,3,17,4,40,6,7,19,20)
and dlr.MA_Type = @ClientType
and trd.ListID = @ListID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 09:17:37

you dont need the IF else part. instead in your final query just add a join to @ProductTBL ON ProductID=prd.ProductID and remove the condition
and prd.ProductID = @ProductID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 09:27:21

Thanks, I will change accordingly and test this. One more question:

From the above code for the tablevar something looks wrong.

PARAM_VALUE is a varchar(4096). @Product is declared as a varchar(50) and assigned a value '300'. The below code gives a conversion error since one of the values for PARAM-VALUE is 'All Dealers'. Apparently this piece of code is in a stored procedure and I am not sure how it works without conversion errors. Is this correct?
SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 09:30:10

quote:
Originally posted by sqlnovice123

Thanks, I will change accordingly and test this. One more question:

From the above code for the tablevar something looks wrong.

PARAM_VALUE is a varchar(4096). @Product is declared as a varchar(50) and assigned a value '300'. The below code gives a conversion error since one of the values for PARAM-VALUE is 'All Dealers'. Apparently this piece of code is in a stored procedure and I am not sure how it works without conversion errors. Is this correct?
SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product

it will be correct as long as it has only numerical values in PARAM_VALUE for passed value of @Product

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 10:05:13

I still get the conversion error. How do I fix this? The proc is exec as

Execute rptAvgSpreadToCoverRatio '20090801', '20090901','20000505','2222961','Broker-Dealer', 1, 1, '1',900

/* ALTER PROCEDURE [dbo].[rptAvgSpreadToCoverRatio]
(
@StartDate datetime, --= '20060501'
@EndDate datetime, --= '20060630'
--@BeginTradeDate datetime, -- 20100801
--@EndTradeDate datetime, -- 20100901
@ThresholdDate datetime, --TradeDate from Trade Table for New/Historic Dealer Historic Dealer: Date (Check First Trade. If First Trade is after selected date, then this is a NEW Dealer. If First Trade is before selected date, then this is a HISTORIC Dealer)
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@ClientType varchar(30), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@ProductID int, --map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ProtocolID tinyint, --Single Select Limit to SpreadProtocol=1, PriceProtocol=2, YieldProtocol=3, DMProtocol=4
@IsBuy varchar(20), --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100) --<1million, 1-5 million, 5 million-10 million, >10 million

) */
select convert(int,PARAM_VALUE) from MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = 300

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'All Dealers' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:06:51

then you need to do like below


select convert(int,PARAM_VALUE) from MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY	= 300
AND ISNUMERIC(PARAMLIST_KEY) = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 10:29:41

That still did not work. I get the same error

select convert(int,PARAM_VALUE) from MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = 300
AND ISNUMERIC(PARAMLIST_KEY) = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:39:06

quote:
Originally posted by sqlnovice123

That still did not work. I get the same error

select convert(int,PARAM_VALUE) from MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = 300
AND PARAM_VALUE <> 'All Dealers'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 10:43:21

Yes, I tried this before but I have to check if it is OK to eliminate this one.

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 11:27:02

I get the foll error for the below query. Not sure how to fix this.

DECLARE @Product varchar(50)
SET @Product = '18650'
DECLARE @ProductTBL TABLE
(
ProductID int
)

INSERT @ProductTBL
SELECT DISTINCT
ProductID
FROM
Product
WHERE
ProductID IN (SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = @Product
AND PARAM_VALUE <> 'All Dealers')

select * from @ProductTBL

SELECT
MISInquiryID = trd.MISInquiryID,
ClientID = trd.PPivotalCompanyID,
DealerId = CPPivotalCompanyID,
ProtocolID = trd.ProtocolID,
CPLevel = trd.Level,
TradeMonth = right('0' + convert(varchar(2), datepart(mm, trd.TradeDate)), 2),
TradeMonthName = datename(mm, trd.TradeDate),
TradeYear = convert(varchar(4), datepart(yy, trd.TradeDate)),
Product = prd.ShortName,
ClientType = dlr.MA_Type,
[TEMP_SizeBucket]= CASE
WHEN iql.USDSize <1000 THEN '<1000'
WHEN iql.USDSize >= 1000 AND iql.USDSize <5000 THEN '>= 1000 & <5000'
WHEN iql.USDSize >=5000 AND iql.USDSize <10000 THEN '>= 5000 & <10000'
ELSE '>= 10000' END,
[TEMP_IsBuy] = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END

--INTO
--#Trade
FROM
Trade trd
INNER JOIN
pivotal..Company dlr
ON
trd.CPPivotalCompanyID = dlr.Company_Id
INNER JOIN Product prd
ON
trd.ProductID = prd.ProductID
INNER JOIN
Inquiry inq
ON
trd.MISInquiryID = inq.MISInquiryID
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

INNER JOIN
@ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

where

datepart(yy, trd.TradeDate) = '2009'--datepart(yy, '2009')
and trd.ProtocolID = 1--@ProtocolID --(values 1,2,3,4)
and trd.IsVolume = 1
--and NOT trd.CPPivotalCompanyID in (select DealerID from #Final)
--and NOT trd.CPPivotalCompanyID in (select DealerID from #Final)
--and prd.ProductID = @ProductID --(values 1,2,3,17,4,40,6,7,19,20)
and dlr.MA_Type = 'Broker-Dealer'--@ClientType
and trd.ListID = '2222961'--@ListID

Some values are as below. 201-1610

Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the varchar value '201-1610' to data type int.

I would like to grab values to see the following in the list for example for ProductID say 1, 2,3,4,5,6,7

Product

USHG
FRN
EM
HY
SYND
AGNCY
AGNCYF

Right now the tablevar shows 3,2,1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 11:30:17

which field has the value 201-1610?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-02 : 11:40:02

The PARAM_VALUE field from the MARPTGEN_PARAMLIST table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 11:42:39

in that case you need to first filter out non numeric values from PARAM_VALUE before doing convertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources