August 4, 2009 at 12:22 pm
Hi,
I have a situation where I have bunch of 7-digit Encoded text in database like 0304182, now here
First two characters represent Product i.e. 03
Next two character represent Class i.e. 04 and
Last thress characters represent SubClass i.e. 182
Now I want to fill thress dropdowns (i.e. Product, Class, SubClass) with the distinct value from the database, where Subclass will populate on Class selection and Class will populate on Product selection.
So can anyone suggest me the SQL query to get it to work ? Hope you understand the situation. I am using SQL server 2005.
August 4, 2009 at 1:32 pm
steven (8/4/2009)
Hi,I have a situation where I have bunch of 7-digit Encoded text in database like 0304182, now here
First two characters represent Product i.e. 03
Next two character represent Class i.e. 04 and
Last thress characters represent SubClass i.e. 182
Now I want to fill thress dropdowns (i.e. Product, Class, SubClass) with the distinct value from the database, where Subclass will populate on Class selection and Class will populate on Product selection.
So can anyone suggest me the SQL query to get it to work ? Hope you understand the situation. I am using SQL server 2005.
Hi Steven,
I built this query for you, check it out, see if it does what you are looking for.
USE tempdb
GO
DROP TABLE Product
CREATE TABLE Product (
ID INT IDENTITY ( 1 , 1 ),
EncodedProduct VARCHAR(7))
INSERT INTO Product
(EncodedProduct)
SELECT '0101001'
UNION ALL
SELECT '0201001'
UNION ALL
SELECT '0301001'
UNION ALL
SELECT '0301002'
UNION ALL
SELECT '0102001'
UNION ALL
SELECT '0202001'
UNION ALL
SELECT '0302001'
UNION ALL
SELECT '0303001'
UNION ALL
SELECT '0401001'
UNION ALL
SELECT '0504001'
UNION ALL
SELECT '0101010'
UNION ALL
SELECT '0102010'
UNION ALL
SELECT '0103010'
UNION ALL
SELECT '0203010'
--This fills your PrdCode combo
Select distinct PrdCode from (
SELECT substring(EncodedProduct,1,2) AS PrdCode,
Substring(EncodedProduct,3,2) AS PrdClass,
Substring(EncodedProduct,5,3) AS PrdSubClass
FROM Product) as Prd
GO
--This fills your PrdClass combo based on the PrdCode selection (@PrdCode)
Declare @PrdCode varchar(2)
set @PrdCode = '01'
select distinct PrdClass from (
SELECT substring(EncodedProduct,1,2) AS PrdCode,
Substring(EncodedProduct,3,2) AS PrdClass,
Substring(EncodedProduct,5,3) AS PrdSubClass
FROM Product) as Prd
where PrdCode = @PrdCode
GO
Declare @PrdCode varchar(2),
@PrdClass varchar(2)
SET @PrdCode = '03'
SET @PrdClass = '01'
select distinct PrdSubClass from (
SELECT substring(EncodedProduct,1,2) AS PrdCode,
Substring(EncodedProduct,3,2) AS PrdClass,
Substring(EncodedProduct,5,3) AS PrdSubClass
FROM Product) as Prd
where PrdCode = @PrdCode
AND PrdClass = @PrdClass
Cheers,
J-F
August 4, 2009 at 1:53 pm
Thanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?
August 4, 2009 at 1:59 pm
steven (8/4/2009)
Thanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?
I don't think you can in this forum, well, just saying it will help people who are looking for the same answer.
Thanks for the feedback Steven, have a nice day,
Cheers,
J-F
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply