August 9, 2005 at 1:20 pm
TABLE:BL_TRUCK_BOL_DEST
COLUMNS: bol_no, dest_bol_no, rev_no
ARGS:@bol_no, @dest_bol_no, @rev_no
I want to select * from BL_TRUCK_BOL_DEST.
IF bol_no is null OR bol_no = '' then I want the where to be
where dest_bol_no = dest_bol_no(arg)
else where bol_no = bol_no(arg)
something like this:
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE (case bol_no
when bol_no = '' or Is null bol_no then dest_bol_no = @dest_bol_no
else @bol_no
end
)
AND BL_TRUCK_BOL_DEST.rev_no = @rev_no
August 9, 2005 at 1:47 pm
any way you try to write this query it will cause ugly table scans on your query plan
try this instead
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no = @bol_no
union
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no = ''
and dest_bol_no = @dest_bol_no
union
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no is NULL
and dest_bol_no = @dest_bol_no
August 9, 2005 at 2:05 pm
If I do that:
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no = ''
union
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no = ''
and dest_bol_no = '2BT'
union
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no is NULL
and dest_bol_no = '2BT'
I get other records that are not dest_bol_no = '2BT' because
there are other records w/ bol_no = ''
August 9, 2005 at 2:11 pm
I suspect its the first query in the union
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no = ''
I thought you wanted
SELECT *
FROM BL_TRUCK_BOL_DEST
WHERE bol_no = @bol_no
August 9, 2005 at 2:16 pm
Here's an example of my data
bol_no.....dest_bol_no
00001.....2BT
00001....1BT
.............4BT
.............5BT
If I give it @bol = 00001 - I want back 2BT & 1BT
If I give it @bol = '',@dest_bol = '4BT' - I want back 4BT
August 10, 2005 at 9:25 am
So are you saying, if you supply a value for @Bol and no value for @Dest_bol, then you want 2BT & 1BT.
But if you supply a '' for @bol, and for @dest_bol you supply '4BT' then you only want 4BT records?
August 10, 2005 at 9:36 am
SET NOCOUNT ON
DECLARE @BL_TRUCK_BOL_DEST TABLE
(
bol_no VARCHAR(10),
dest_bol_no VARCHAR(10)
)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('00001', '2BT')
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('00001', '1BT')
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('', '4BT')
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('', '5BT')
DECLARE @bol_no VARCHAR(10)
DECLARE @dest_bol_no VARCHAR(10)
SELECT @bol_no = '00001', @dest_bol_no = ''
/* Query */
SELECT * FROM @BL_TRUCK_BOL_DEST
WHERE
bol_no =
CASE
WHEN @bol_no = '' OR @bol_no IS NULL
THEN bol_no
ELSE @bol_no END AND
dest_bol_no =
CASE
WHEN @bol_no = '' AND @dest_bol_no <> ''
THEN @dest_bol_no
ELSE dest_bol_no END
SELECT @bol_no = '', @dest_bol_no = '4BT'
/* Same Query */
SELECT * FROM @BL_TRUCK_BOL_DEST
WHERE
bol_no =
CASE
WHEN @bol_no = '' OR @bol_no IS NULL
THEN bol_no
ELSE @bol_no END AND
dest_bol_no =
CASE
WHEN @bol_no = '' AND @dest_bol_no <> '' AND @dest_bol_no IS NOT NULL
THEN @dest_bol_no
ELSE dest_bol_no END
Regards,
gova
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply