I need to find Generic_Code from the table. Data in Length_Min, Length_Max, and Length columns are varchar data. The query is not return any data. Length contains '00:15:18' so my query has to pick up Generic_Code = '3000623WVD'. I am wondering what am I doing wrong here. The first subquery returns all five rows instead of only last three. Please help.
Table:
Length_Min Length_Max Generic_Code
0:00:00 0:04:59 3000621WVD
0:05:00 0:14:59 3000622WVD
0:15:00 0:29:59 3000623WVD
0:30:00 0:44:59 3000624WVD
0:45:00 0:59:59 3000625WVD
The query I use:
Select * from web_video_products_xref xr where xr.Length_Min < (select LENGTH from web_video_products wp where wp.Product_Code = @PrdCode) and xr.Length_Max > (select LENGTH from web_video_products wp where wp.Product_Code = @PrdCode)
February 13, 2024 at 7:45 pm
It seems like this is a character comparison. If so, "00:15:18" would not compare correct "0:...", since you have two leading zeros vs one leading zero. If you're going to compare char values, the lens and formats must be absolutely consistent.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2024 at 7:49 pm
For example, when the lengths are consistent, it seems to produce the desired result:
DROP TABLE IF EXISTS #xr;
CREATE TABLE #xr ( length_min varchar(8) NOT NULL, length_max varchar(8) NOT NULL, generic_code varchar(20) NULL );
INSERT INTO #xr VALUES
('00:00:00', '00:04:59', '3000621WVD'),
('00:05:00', '00:14:59', '3000622WVD'),
('00:15:00', '00:29:59', '3000623WVD'),
('00:30:00', '00:44:59', '3000624WVD'),
('00:45:00', '00:59:59', '3000625WVD');
DROP TABLE IF EXISTS #wp;
CREATE TABLE #wp ( product_code varchar(30) NOT NULL, LENGTH varchar(8) NULL );
INSERT INTO #wp VALUES('abc', '00:15:18');
DECLARE @prdcode varchar(30);
SET @prdcode = 'abc';
Select * from /*web_video_products_xref*/ #xr xr where xr.Length_Min < (select LENGTH from /*web_video_products*/ #wp wp where wp.Product_Code = @PrdCode) and xr.Length_Max > (select LENGTH from /*web_video_products*/ #wp wp where wp.Product_Code = @PrdCode)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
The first three rows in the table DO all match the first "<" condition: 00:05:xx and 00:10:xx are less than 00:15:xx. Only the second condition limits the result to a single row.
Also, you should >= and <= otherwise an exact match, like 00:15:00, won't get a hit at all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2024 at 8:27 pm
Thank you, Scott, so much. I corrected my data in #xr table and it works. I do appreciate your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply