NOT IN with OpenRowSet

Question

NOT IN with OpenRowSet

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Ok. I don't have your "v_Manufac" view to test against but give this a try.

--===== Note that the following characters are replaced with an empty string if present in the file.
     -- NCHAR(9)  = TAB character
     -- NCHAR(10) = LINEFEED or NEWLINE character
     -- NCHAR(13) = CARRIAGE RETURN character
 SELECT v.ModelName0
   FROM v_Manufac v
  WHERE v.ModelName0 NOT IN
        (
         SELECT ModelNameExclude = s.value
           FROM OPENROWSET(BULK 'c:\temp\SampleUnicode.txt', SINGLE_nCLOB) x
          CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(x.BulkColumn,NCHAR(9),N''),NCHAR(10),N''),NCHAR(13),N''), ',') s
        )
  ORDER BY v.ModelName0
;

The "spaces" you were running across aren't spaces at all... they're embedded line terminators, which the REPLACES take out of the picture.

The sub-select in the NOT IN can be executed separately if you want to see whats happening.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TJ_T Old Hand Points: 350 More actions · Answer 2

Hi Jeff, It works great! I did some initial testing and two thumbs up.

I will test further and if any issues pop up, I will post it.

Thanks again

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

As some of the techs I work with would say, "Cool beans". Thanks for the feedback on this.

As a bit of a sidebar, this is yet another proof that correct identification of the problem is 90% of the solution. Thank you for weathering all the questions that I asked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions