June 5, 2018 at 10:50 am
My question is listed in the code.
Declare @C CHAR(1);
Select @C='1'
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( MEM VARCHAR(10), CLAIM1_PRESENT CHAR(1), CLAIM2_PRESENT CHAR(1) );
INSERT INTO #t(MEM, CLAIM1_PRESENT, CLAIM2_PRESENT )
Select
'ABC' ,NULL, NULL
UNION
Select
'F21' ,'1', NULL
UNION
Select
'F22' ,NULL, '1'
SELECT *
FROM
#t
--WHERE
--????
/*
QUESTION: Please construc the WHERE CLAUSE
WHEN @C=1 Then I would only want to see those rows which have a '1' in either CLAIM1_PRESENT or CLAIM2_PRESENT ( The output will have 2 records )
WHEN @C IS NULL OR SOME OTHER VALUE ( OTHER THAN '1' ) THEN SELECT ALL THE RECORDS ( So we should have a set of 3 records )
*/
June 5, 2018 at 10:56 am
That's not a question, that's a request. You've posted enough times to know the volunteers here aren't here to do your work for you (you're the one that's gets paid to do that).
It looks like you haven't tried to solve this yourself yet, I suggest trying to do so first, and post what you have tried. We can then point you in the right direction if you haven't succeeded. If you have any errors, don't forget to post those. If it's not working as you expect then make sure you explain why.
Of course, if you want us to do your work for you I'm sure there are several users here who would be more than happy to offer you paid services privately (provided it isn't homework, that's only for you to do, otherwise you'll never learn). If that's the case, reply and let us know that's what you're after, and they can send you a private message outlining their fees to you (although that's probably against the Terms of Use; specifically 2.1(i)). 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 5, 2018 at 11:22 am
Mr Thom
I tried and it failed... That is why I asked you
Declare @C CHAR(1);
Select @C='3'
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( MEM VARCHAR(10), CLAIM1_PRESENT CHAR(1), CLAIM2_PRESENT CHAR(1) );
INSERT INTO #t(MEM, CLAIM1_PRESENT, CLAIM2_PRESENT )
Select
'ABC' ,NULL, NULL
UNION
Select
'F21' ,'1', NULL
UNION
Select
'F22' ,NULL, '1'
SELECT *
FROM
#t
WHERE
( CASE WHEN ( @C='1') THEN 1 ELSE 0 END ) = ( CASE WHEN CLAIM1_PRESENT = '1' OR CLAIM2_PRESENT = '1' THEN '1' ELSE 0 END )
June 5, 2018 at 11:25 am
Thanks... I figured....
No further responses or support needed......
Thank you for your effort.
Declare @C CHAR(1);
Select @C='1'
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( MEM VARCHAR(10), CLAIM1_PRESENT CHAR(1), CLAIM2_PRESENT CHAR(1) );
INSERT INTO #t(MEM, CLAIM1_PRESENT, CLAIM2_PRESENT )
Select
'ABC' ,NULL, NULL
UNION
Select
'F21' ,'1', NULL
UNION
Select
'F22' ,NULL, '1'
SELECT *
FROM
#t
WHERE
( CASE WHEN ( @C='1') THEN 1 ELSE 0 END ) = ( CASE WHEN (( CLAIM1_PRESENT = '1' OR CLAIM2_PRESENT = '1') AND (@C='1') ) THEN '1' ELSE 0 END )
--????
/*
QUESTION: Please construc the WHERE CLAUSE
WHEN @C=1 Then I would only want to see those rows which have a '1' in either CLAIM1_PRESENT or CLAIM2_PRESENT ( The output will have 2 records )
WHEN @C IS NULL OR SOME OTHER VALUE ( OTHER THAN '1' ) THEN SELECT ALL THE RECORDS ( So we should have a set of 3 records )
*/
June 5, 2018 at 11:47 am
mw_sql_developer - Tuesday, June 5, 2018 11:25 AMThanks... I figured....
No further responses or support needed......
Thank you for your effort.Declare @C CHAR(1);
Select @C='1'IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t( MEM VARCHAR(10), CLAIM1_PRESENT CHAR(1), CLAIM2_PRESENT CHAR(1) );INSERT INTO #t(MEM, CLAIM1_PRESENT, CLAIM2_PRESENT )
Select
'ABC' ,NULL, NULL
UNION
Select
'F21' ,'1', NULL
UNION
Select
'F22' ,NULL, '1'SELECT *
FROM
#t
WHERE
( CASE WHEN ( @C='1') THEN 1 ELSE 0 END ) = ( CASE WHEN (( CLAIM1_PRESENT = '1' OR CLAIM2_PRESENT = '1') AND (@C='1') ) THEN '1' ELSE 0 END )
--????/*
QUESTION: Please construc the WHERE CLAUSE
WHEN @C=1 Then I would only want to see those rows which have a '1' in either CLAIM1_PRESENT or CLAIM2_PRESENT ( The output will have 2 records )
WHEN @C IS NULL OR SOME OTHER VALUE ( OTHER THAN '1' ) THEN SELECT ALL THE RECORDS ( So we should have a set of 3 records )
*/
Well, that will scale horribly and not use any indexes you may have on your actual table.
June 5, 2018 at 11:57 am
Here is your answer, and it may not be much better as it is considered a catch-all query:
DECLARE @C CHAR(1);
SELECT @C = '1';
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE [#t];
CREATE TABLE [#t] (
[MEM] VARCHAR(10)
, [CLAIM1_PRESENT] CHAR(1)
, [CLAIM2_PRESENT] CHAR(1)
);
INSERT INTO [#t] ([MEM], [CLAIM1_PRESENT], [CLAIM2_PRESENT])
SELECT 'ABC', NULL, NULL UNION ALL
SELECT 'F21', '1', NULL UNION ALL
SELECT 'F22', NULL, '1';
SELECT
*
FROM
[#t]
WHERE
(@C = '1' AND ([CLAIM1_PRESENT] = '1' OR [CLAIM2_PRESENT] = '1')) OR ISNULL(@C,'') <> '1';
June 5, 2018 at 12:23 pm
Thom A - Tuesday, June 5, 2018 12:12 PMPerhaps the solution would, instead, be to use dynamic SQL, rather that a Catch all WHERE clause:
That would be a good way to it.
June 6, 2018 at 1:52 am
IF @C = '1'
SELECT MEM,CLAIM1_PRESENT,CLAIM2_PRESENT
FROM #t
WHERE CLAIM1_PRESENT = '1' OR CLAIM2_PRESENT = '1'
ELSE
SELECT MEM,CLAIM1_PRESENT,CLAIM2_PRESENT
FROM #t;
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply