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