December 16, 2015 at 8:32 am
Hi,
I am probably considered a beginner at SQL compared to most of you!
Here is code that I have that works:
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId like (''%123456'')'
I now have a long list of PATIENT.PAT_ExternalIds and I would like to have a list of all patients in the database that match the numbers on the list exactly. No matter what I do, I can't seem to figure out the right combination of "ORs" and placement of "s and 's and ( )s to get it to work without an error message.
PAT.ExternalId is string, not numeric.
This does not work:
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId in
(''123456'',
''654321'')'
This does not work:
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId = ('123456'OR
'654321')
Does anyone have any ideas? Thank you!
Danielle
December 16, 2015 at 8:41 am
dmboyce1 (12/16/2015)
Hi,I am probably considered a beginner at SQL compared to most of you!
Here is code that I have that works:
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId like (''%123456'')'
I now have a long list of PATIENT.PAT_ExternalIds and I would like to have a list of all patients in the database that match the numbers on the list exactly. No matter what I do, I can't seem to figure out the right combination of "ORs" and placement of "s and 's and ( )s to get it to work without an error message.
PAT.ExternalId is string, not numeric.
This does not work:
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId in
(''123456'',
''654321'')'
This does not work:
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId = ('123456'OR
'654321')
Does anyone have any ideas? Thank you!
Danielle
Your best idea might be to create a temp table (#Ids) containing a single 'ExternalId' column, then populate that with the Ids you want to match.
Once you have that in place
select columns
from table t
join #Ids i on t.PATIENT.PAT_ExternalId = i.ExternalId
By performing the join, you are doing the filtering naturally (and in a high-performance way).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 16, 2015 at 8:42 am
Danielle
What is "does not work" - returns the wrong results, or gives an error? Please can we see your whole query? You seem to have an odd number of inverted commas in each example.
Even ignoring the above, your second example isn't syntactically correct - it would have to be something like this:
set @PAT_Filter=N'AND (PATIENT.PAT_ExternalId = '123456'OR
PATIENT.PAT_ExternalId = '654321')
John
December 16, 2015 at 8:43 am
Why doesn't "IN" work? This works:
DECLARE @t TABLE (myid INT, mystring VARCHAR(20))
INSERT @t VALUES (1, '123456'), (2, '1234532'), (3, '345456'), (4, '12346')
SELECT * FROM @t WHERE mystring IN ('1234532', '345456')
If you provide some data and results, and a query, perhaps we can help.
Ideally I'd like to see a test that shows some results, something like:
CREATE PROCEDURE [misc procs].[test can this IN function work]
AS
BEGIN
-- assemble
DECLARE @t TABLE (myid INT, mystring VARCHAR(20))
INSERT @t VALUES (1, '123456'), (2, '1234532'), (3, '345456'), (4, '12346')
SELECT TOP(0)
*
INTO #expected
FROM @t
INSERT #expected
( myid, mystring )
VALUES
(2, '1234532'), (3, '345456')
-- act
SELECT myid, mystring
INTO #actual
FROM @t WHERE mystring IN ('1234532', '345456')
-- assert
EXEC tsqlt.AssertEqualsTable
@Expected = N'#expected'
, -- nvarchar(max)
@Actual = N'#actual'
, -- nvarchar(max)
@FailMsg = N'Table error' -- nvarchar(max)
END
GO
EXEC tsqlt.run '[misc procs].[test can this IN function work]'
December 16, 2015 at 8:47 am
What you're currently doing uses a fixed set of comparison values with dynamic SQL. But what you really need is a fixed SQL statement supporting a dynamic set of values.
If you insert your set of comparison values into a table variable, then you can inner join that table with the PATIENT table using the LIKE predicate and partial comparison.
DECLARE @IDS TABLE (ID VARCHAR(20) NOT NULL PRIMARY KEY);
INSERT INTO @IDS VALUES ( '123456', '654321' );
SELECT *
FROM PATIENT AS P
INNER JOIN @IDS AS IDS ON P.PAT_ExternalId LIKE '%' + IDS.ID;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 16, 2015 at 8:53 am
As the OP requested an exact match, why are we suggesting using LIKE here?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply