Datajoiner/Oracle trim trailing blanks

[login to unmask email]

Datajoiner/Oracle trim trailing blanks
Hello List,

In Datajoiner, I map an Oracle database with all server options set to
default. I have an Oracle table containing 1282 rows, and all rows contain
non-blank data in a column called server. I run the following query in
Datajoiner and get 1282 rows returned (there's a single space between the
single quotes):
SELECT * FROM DBAP.DB_TABLE WHERE SERVER <> ' '

This is the query that Datajoiner sent to Oracle, which is not indexable in
Oracle. Not a problem on a small table but a big problem for much larger
tables:
SELECT * FROM "DBAP"."DB_TABLE" A0 WHERE (RPAD(A0."SERVER", 30, ' ') <>
RPAD(' ', 30, ' '))

I created server option varchar_no_trailing_blanks = y and ran it again.
This time the query returned 0 rows, not 1282. Oracle showed this was the
query that came from Datajoiner:
SELECT * FROM "DBAP"."DB_TABLE" A0 WHERE (A0."SERVER" <> RTRIM(' '))

My Oracle DBAs tell me that Oracle is RTRIM'ing the single blank, thus
converting it into WHERE SERVER <> NULL, and 0 is indeed the correct answer
because Oracle will always return 0 rows anytime a where clause says "=
NULL" or "<> NULL", because null can never be compared to anything. I went
into Oracle directly (not through Datajoiner) and tried it. If I say
"WHERE SERVER IS NOT NULL" I get 1282 rows. If I say "WHERE SERVER <>
NULL" I get 0 rows.

Since the real query is looking for rows where the value is not equal to a
single space, they're not getting the correct answer when I have
varchar_no_trailing_blanks on. But if I turn that option off then my
performance is shot.

Anyone have a solution to this?

Steve Toeniskoetter
American Electric Power



Tim R - CNF Ohling

Re: Datajoiner/Oracle trim trailing blanks
(in response to sjtoeniskoetter@AEP.COM)
Can you use the PASSTHRU option so that the code isn't converted by DJ?
(Page 8-11 in the V2.1.1 Application Programming and SQL manual.)

HTH,
Tim

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Friday, December 21, 2001 8:59 AM
> To: [login to unmask email]
> Subject: Datajoiner/Oracle trim trailing blanks
>
> Hello List,
>
> In Datajoiner, I map an Oracle database with all server options set to
> default. I have an Oracle table containing 1282 rows, and all rows
> contain
> non-blank data in a column called server. I run the following query in
> Datajoiner and get 1282 rows returned (there's a single space between the
> single quotes):
> SELECT * FROM DBAP.DB_TABLE WHERE SERVER <> ' '
>
> This is the query that Datajoiner sent to Oracle, which is not indexable
> in
> Oracle. Not a problem on a small table but a big problem for much larger
> tables:
> SELECT * FROM "DBAP"."DB_TABLE" A0 WHERE (RPAD(A0."SERVER", 30, ' ') <>
> RPAD(' ', 30, ' '))
>
> I created server option varchar_no_trailing_blanks = y and ran it again.
> This time the query returned 0 rows, not 1282. Oracle showed this was the
> query that came from Datajoiner:
> SELECT * FROM "DBAP"."DB_TABLE" A0 WHERE (A0."SERVER" <> RTRIM(' '))
>
> My Oracle DBAs tell me that Oracle is RTRIM'ing the single blank, thus
> converting it into WHERE SERVER <> NULL, and 0 is indeed the correct
> answer
> because Oracle will always return 0 rows anytime a where clause says "=
> NULL" or "<> NULL", because null can never be compared to anything. I
> went
> into Oracle directly (not through Datajoiner) and tried it. If I say
> "WHERE SERVER IS NOT NULL" I get 1282 rows. If I say "WHERE SERVER <>
> NULL" I get 0 rows.
>
> Since the real query is looking for rows where the value is not equal to a
> single space, they're not getting the correct answer when I have
> varchar_no_trailing_blanks on. But if I turn that option off then my
> performance is shot.
>
> Anyone have a solution to this?
>
> Steve Toeniskoetter
> American Electric Power
>
>
>
>
>