NOT IN with OpenRowSet

Question

Post reply

NOT IN with OpenRowSet

TJ_T

Old Hand

Points: 352
More actions
May 9, 2019 at 12:26 pm

#3638986

Hi,
I am trying to use a text file to exclude a list of things. Thus I was hoping to use OpenRowSet to create the file. The query runs, it just doesn't exclude anything.
DECLARE @Job_numbers VARCHAR(max)
SELECT @Job_numbers=BulkColumn
FROM OPENROWSET(BULK 'c:\temp\SampleUnicode.txt', SINGLE_nCLOB) x;
SELECT ModelName0
FROM v_Manufac
WHERE ModelName0 NOT IN
(
SELECT @Job_numbers
)
ORDER BY ModelName0

Viewing 15 posts - 1 through 15 (of 17 total)

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

TJ_T Old Hand Points: 352 More actions · Answer 1

TJ_T

Old Hand

Points: 352

May 9, 2019 at 12:29 pm

#3638987

sorry, I meant READ file not Create file

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

Can you run just the OPENROWSET query to verify what is being read from the file?

"NOT IN (subquery)" doesn't work correctly if the subquery includes any NULL values. Do any NULLs show up in the OPENROWSET query?

andycadley SSCertifiable Points: 5296 More actions · Answer 3

@Job_Numbers is a single string of text, using NOT IN on it will not break it apart at commas - it's equivalent to writing:

WHERE ModelName0 <> @Job_numbers

I suspect you really want something like

WHERE ModelName0 NOT IN

(

SELECT value FROM String_Split(@Job_numbers, ',')

)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

TJ_T wrote:

Hi, I am trying to use a text file to exclude a list of things.

WHY a text file? Without additional information here, I don't see the merit in doing such a thing. Life would be much simpler if such data were in a table. If the data is originally provided in a text file, it would be much better to simply import that into a table (temporary or otherwise) in most cases. Again, that recommendation is in the absence of a complete picture but I can't think of a situation where I wouldn't follow that recommendation on this particular task.

--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)

TJ_T Old Hand Points: 352 More actions · Answer 5

This almost works:

WHERE ModelName0 NOT IN

(

SELECT value FROM String_Split(@Job_numbers, ',')

)

But when you run the query it builds a list, but after the first row there is space at the start of each new row, and thus the rest of the models are not excluded because of the space I think

Example

ModelNameo

GRX

TRX

VRX

WXR

TJ_T Old Hand Points: 352 More actions · Answer 6

The OPENROWSET is doing this:

(GRX, TRX, VRX, WXR) and I want this:

(GRX,TRX,VRX,WXR) - No spaces

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

TJ_T wrote:

This almost works: WHERE ModelName0 NOT IN ( SELECT value FROM String_Split(@Job_numbers, ',') ) But when you run the query it builds a list, but after the first row there is space at the start of each new row, and thus the rest of the models are not excluded because of the space I think Example ModelNameo GRX TRX VRX WXR

So do an LTRIM on value!

--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)

TJ_T Old Hand Points: 352 More actions · Answer 8

Yes I thought of LTRIM - just wasn't sure how to do it with this query:

DECLARE @Job_numbers VARCHAR(max)

SELECT @Job_numbers=BulkColumn

FROM OPENROWSET(BULK 'c:\temp\SampleUnicode.txt', SINGLE_nCLOB) x;

SELECT ModelName0

FROM v_Manufac

WHERE ModelName0 NOT IN

(

SELECT value FROM String_Split(@Job_numbers, ',')

)

ORDER BY ModelName0

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

TJ_T wrote:

Yes I thought of LTRIM - just wasn't sure how to do it with this query:

That was my main question and you've not answered it yet. Why on Earth are you storing such information in a text file instead of a table?

--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)

TJ_T Old Hand Points: 352 More actions · Answer 10

I have no choice. I can't - not allowed to store in a table.

I have to use a text file 🙁

I have been trying LTRIM and no luck. I am reading about REPLACE now

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

TJ_T wrote:

I have no choice. I can't - not allowed to store in a table. I have to use a text file 🙁 I have been trying LTRIM and no luck. I am reading about REPLACE now

Ok... so you've been hamstrung by someone's requirements. Since they haven't yet fixed the ability to attach files on this site, is the file small enough for you to post the contents of the file? Of course, make sure there's nothing sensitive in the file. We CAN get this done. We just need a bit of data to go on.

--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)

TJ_T Old Hand Points: 352 More actions · Answer 12

The text file just contains a list of Models:

GRX,

TRX,

VRX,

WRX,

NRX,

PRX

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 13

TJ_T wrote:

The text file just contains a list of Models: GRX, TRX, VRX, WRX, NRX, PRX

So each entry is on a separate line in the file? And all lines but the first have a space before them on that separate line? Please confirm.

If that IS the case, that explains a whole lot about why you're having problems and it's a super easy fix. I just need to make sure before spending time on it.

--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)

TJ_T Old Hand Points: 352 More actions · Answer 14

Yes each model is on a separate line in the file followed by a comma. Commas could be removed