Problems with OR-statement after DB change

[FrankInBerlin]

Hi everybody!

I have big problems with an OR-statement after we changed a database view.
We have a Oracle 8 database and use CR 8.5.
I created a report using a view and some tables.
The statement in the select expert looked like this:

({view.name_1} = ?Name or
{view.name_2} = ?Name or
{view.name_3} = ?Name ) and
table.cond <>1

This worked fine!
Then we added another field to the view called 'name_s'

I used the 'verify Database' option and added the new field:

({view.name_s}= ?Name or
{view.name_1} = ?Name or
{view.name_2} = ?Name or
{view.name_3}= ?Name ) and
table.cond <>1

Now I get only records where ({view.name_s}= ?Name is true. :-(
If I change the order to:

({view.name_1}= ?Name or
{view.name_2} = ?Name or
{view.name_3} = ?Name or
{view.name_S}= ?Name ) and
table.cond <>1

I get all records except those where ({view.name_s}= ?Name is true.

I also ran the SQL-Statement form CR directly on the DB and the results were perfect.
I removed the view and added it again; no improvement!

I am running out of ideas..

Please help...

Frank

 

[Naith]

Hey there Frank,

Try not to rely on the Select Expert at all. Go into the Report menu, go to Edit Selection Formula, and Record and amend the selection criteria yourself.

The Select Expert is notorious for assuming it's making your life easier by exerting it's own assumptions on your criteria - which often is not what you want.

If you're still getting the same kind of play after putting the criteria in your example yourself, try and break it up and see what the outcome is:

As an example:

(table.cond <>1 and
({view.name_s}= ?Name or
{view.name_1} = ?Name) or
(table.cond <>1 and
({view.name_2} = ?Name or
{view.name_3}= ?Name ))

Each time you change the order of your statement, you want to check the SQL. You'll know you've hit the mark when you can see your conditions all being passed to the database. At the moment, it doesn't sound like your database pass is sound.

Naith

 

[FrankInBerlin]

Hi Naith,

I also always work directly with the Selection formula, sorry for not making this clear.
I tried everything you mentiond, but the situation is unchanged.
All SQLs copied from CR to SQL*Plus are working fine so I assume the DB is returning the right data.
But I noticed that for some ?Name values the data look better than for others and the data change with the order of the statements (from bad to worse and back).

With my best..

Frank

 

[jambu]

Are you sure you havent done something like this? That would explain why you are only getting name_s=?name rows.

{view.name_s}= ?Name or
({view.name_1} = ?Name or //open bracket on this line.
{view.name_2} = ?Name or
{view.name_3}= ?Name ) and
table.cond <>1

And then this might explain the second condition.

({view.name_1}= ?Name or
{view.name_2} = ?Name or
{view.name_3} = ?Name) or //close bracket on this line.
{view.name_S}= ?Name and
table.cond <>1

I'd also check the changes to the view and the type of the new field, also try Naith's bit by bit approach.

 

[dgillz]

why are you using:

?Name

instead of:

{?Name}

That is the only thing that looked out of place to me. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Naith]

Frank,

When you respond to dgillz, can you also specify what SQL is being passed to the database? (Database/Show SQL) And if it isn't passing, confirm what driver you are using to connect to your database.

In my tests, this should pass to the database without problem, but if need be, I can help you force it to the database if it isn't going already. But it won't be as pretty as using 'OR'.

Naith

 

[FrankInBerlin]

Hi everybody,

first of all I would like to thank everybody for their input.
But things are still unchanged.:-(
I checked all brackets and they are OK.
I changed the order and split it up as suguested by Naith, nothing works!
The SQL form CR for the first statement looks like this:

SELECT DISTINCT
&quot;VIEW&quot;.&quot;NAME_1&quot;, &quot;VIEW&quot;.&quot;NAME_2&quot;, &quot;VIEW&quot;.&quot;NAME_3&quot;, &quot;VIEW&quot;.&quot;NAME_S&quot;, &quot;TABLE1&quot;.&quot;DATA5&quot;, &quot;TABLE1&quot;.&quot;DATA4&quot;, &quot;TABLE1&quot;.&quot;DATA1&quot;, &quot;TABLE1&quot;.&quot;DATA3&quot;, &quot;TABLE2&quot;.&quot;DATA2&quot;
FROM
&quot;XXX&quot;.&quot;VIEW&quot; &quot;VIEW&quot;,
&quot;XXX&quot;.&quot;TABLE1&quot; &quot;TABLE1&quot;,
&quot;XXX&quot;.&quot;TABLE2&quot; &quot;TABLE2&quot;
WHERE
&quot;VIEW&quot;.&quot;KEY1&quot; = &quot;TABLE1&quot;.&quot;KEY1&quot; AND
&quot;TABLE1&quot;.&quot;KEY2&quot; = &quot;TABLE2&quot;.&quot;KEY2&quot;(+) AND
&quot;TABLE1&quot;.&quot;DATA4&quot; >= TO_DATE ('05-02-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
&quot;TABLE1&quot;.&quot;DATA4&quot; <= TO_DATE ('05-02-2003 23:59:59', 'DD-MM-YYYY HH24:MI:SS') AND
&quot;TABLE1&quot;.&quot;DATA1&quot; <> 1. AND
(&quot;VIEW&quot;.&quot;NAME_S&quot; = 'Doe' OR
&quot;VIEW&quot;.&quot;NAME_1&quot; = 'Doe' OR
&quot;VIEW&quot;.&quot;NAME_2&quot; = 'Doe' OR
&quot;VIEW&quot;.&quot;NAME_3&quot; = 'Doe')
ORDER BY
&quot;TABLE2&quot;.&quot;DATA2&quot; ASC

If I run this in SQL*PLUS I get excactly the data I want.
In CR I get only date where &quot;VIEW&quot;.&quot;NAME_S&quot; = 'Doe' is true.

From CR I use 'more data sources'|'Oracle server' to connect to the database.

With my best,

Frank

 

[Naith]

Very helpful response, Frank. Thanks for the SQL. If you've literally copied and pasted the SQL from Crystal's Database/Show SQL area, without amending anything but your object details, then this SQL looks like it couldn't be passing any better. So I find it curious that you say you're only getting a partial representation from the fields in the OR condition of the query. You didn't amend the SQL for the post other than change the object names, did you?

If this is the SQL that Crystal passes, then let's see what happens when you don't pass the SQL to the database. Change your selection criteria in your report to:

Table.Cond <> 1 and
({?Name} in [{view.name_1},{view.name_2},{view.name_3},{view.name_S}])

Depending on how you're connecting to your database, this should pass everything but the name, letting Crystal sort through this part. This is really to get an idea of how your returned data is affected.

Some areas of passing are impacted by whether you're connecting to a datasource natively, or via ODBC, which is kind of what I was trying to find out from my last post. The way to find out what driver you're using is to check out the Database menu, and look at the greyed out &quot;From&quot; area of Convert Database Driver.

Naith

 

[FrankInBerlin]

Hi Naith,

I tryed the suggested change; I did not get any data back!
I also tried the SQL again and got all data (as expected).

I checked the Database driver: Oracle 7.x (pdsora7.dll)

Another thing I tried:
Table.Cond <> 1 and
({?Name} in [{view.name_1},{view.name_2},{view.name_3}]

Again no data in CR!
I can see in both cases that data are processed in the lower right hand corner of the screen, but the result is 0.

Weired!!!:-/

Frank

 

[jambu]

Frank
are you sure that you are connecting to the same database and using the same view? I have done that before connecting to a different database with a view that was slightly different. Also that might account for a diffence in data.
If I remember correctly it turned out to be some mistake in either the hosts file or the .ora file that caused the different connections.
If you have multiple databases (like dev,uat,live) try logging on to each in turn using sql*plus and compare the results with the results in crystal.

Do you change anything between running that query in crystal and in sql*plus?

 

[FrankInBerlin]

Dear Jambu,

I always work on the same database.
No margin of error there.
We do have multiple databases but they are offline right now.

Also I don't make any changes to the SQL.
I just copy it in an editor, save it and run it.

With my best,

Frank

 

[Naith]

Can you make a copy of your report, and switch the driver so that you're connecting natively, if you're currently connecting via ODBC - or vice versa, whichever is applicable.

Re-run the report, and check your results.

Naith

 

[jambu]

Could the following be causing a problem? the dot after the 1? I would imagine that oracle and crystal would treat it as just 1 but maybe there is something crystal doesnt like about it.
&quot;TABLE1&quot;.&quot;DATA1&quot; <> 1. AND

Presumably you do make changes to the sql when you run it in sql*plus as you will replace the Name parameter with a literal and the date range parameters (I assume these are parameters) with literals too.
You could try using the same literals in crystal instead of parameters. If that works you can check how your parameters are being formatted.

 

[MColeman]

I've had problems with using 'OR' in the record selections also. Here is one way I've been able to get around the problem.

In Report, Edit Selection Formula, Record

if {view.name_s}= ?Name and table.cond <> 1 then true
else
if {view.name_1} = ?Name and table.cond <> 1 then true
else
if {view.name_2} = ?Name and table.cond <> 1 then true
else
if {view.name_3}= ?Name and table.cond <> 1 then true
else
false

 

[FrankInBerlin]

Hi everybody,

I re-ran the report using ODBC instead of connecting natively, no improvement.

I don't think that the dot is causing any problems as long as the database is configured for the English/US number format. I had problems with this dot in the begining, and got that fixed by changing my german windows to the US number format.

For the parameter fields: I copyed the SQL after I have entered my parameters, so the statement does already contain the literals and the SQL can run unchanged in SQL+plus.

MColemans suggestion did not work either: Depending on the position of the Name_s part I got only the name_s date (name_s is at the begining) or everything except name_s data(name_s is at the end).

I will try an recreate the whole report from scratch today, see if this brings any improvement.

Thanks to everybody!

Frank
PS: My apologies for all the typos, English is not my first language!