Query help

Question

Query help

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
June 15, 2022 at 2:50 am

#4049639

I have table and record as below. I have this database in DB2 and I replicate this databse in SQL server 2016 as well. In DB2, the same exact where clause returns rows, whereas in SQL server, it doesn't return anything. Is this because way SQL server does sorting is different than DB2? What can I do in SQl server to make it return results?
any help would be appreciated.
CREATE TABLE #temp (idaisl VARCHAR(10), basil VARCHAR(10), eaisl VARCHAR(10))
INSERT INTO #temp
SELECT 'V', 'A', '9'
SELECT * FROM #temp
WHERE idaisl BETWEEN basil AND eaisl

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What collation is the DB2 database using?

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

wburke 85918 SSChasing Mays Points: 619 More actions · Answer 2

Yes you are right EBCDIC and ASCII have different binary value sets. For example, "a" is less than "A" in EBCDIC, but "a" is greater than "A" in ASCII. Numeric characters are less than any alphabetic letter in ASCII but are greater than any letter in EBCDIC.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

And that leads us to something like this:

SELECT *
FROM #temp
WHERE idaisl COLLATE SQL_EBCDIC037_CP1_CS_AS
BETWEEN basil COLLATE SQL_EBCDIC037_CP1_CS_AS AND eaisl COLLATE SQL_EBCDIC037_CP1_CS_AS;

This reply was modified 2 years, 5 months ago by Phil Parkin.

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Alternatively, I think modifying the default collation on your database before creating tables and adding data to them should also do the trick (and then you'll be able to write queries without having the COLLATE keyword all over).

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

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 5

SQL_Surfer

SSCrazy Eights

Points: 8320

June 16, 2022 at 12:29 am

#4050143

This was a life saver. Thank you very much!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

SQL_Surfer wrote:

This was a life saver. Thank you very much!

Remember TempDB when you do this. If it has a different collation, you might be in for a heap of trouble with "mixed" collations.

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