SQL Designer Problem

[beginnerkid]

When I try to run SQL from SQL Designer, it does't return any records whe any of the field has null vlaue, I am querying on Oracle database.
Any help is much appreciated

 

[kenhamady]

Even if those fields aren't included in the WHERE clause at all? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[databaseman]

Try adding an if/then clause where if isnull(field) then "null". That way you should get someting even when the field is null.

Lotsa Luck!

Databaseman Database Man
Defender of Life, Liberty and the pursuit of accurate database reporting! Follow my free tek-tip adventures at:

http://www.hammerman.com/dbman.html

 

[kenhamady]

You can't use the IsNull() function in the SQL designer. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[jjames]

Are you linking 2 or more tables?

 

[Ngolem]

could be a outter Join problem

 

[beginnerkid]

No I am querying on a Oracle view. It treats it as a table anyway I believe.

 

[kenhamady]

Post your SQL statement.
Do you mean when you use the field anywhere in the statement, or when you use it in the WHERE clause? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[beginnerkid]

Here my SQL looks like :


Select "Table"."Column1","Table"."Column2","Table"."Column3","Table"."Column4" from "Username""Table"
where "Table"."Column1"='Some string"
and
(&quot;Table&quot;.&quot;Column1&quot; <= value1 or (&quot;Table&quot;.&quot;Column1&quot; >= value2)

Here none of the where cluase fields are null. even if any of the listed fields in select clause is null SQL query fails to show records.

Bye the way I am using Seagate Info 7.0.2

Thanks and regards

 

[beginnerkid]

Please Ignore typing mistake which caused the SQL query written here syntectically wrong. There is no problem in SQL query as I ran it in SQL*Plus successfully


Thanks

 

[kenhamady]

It doesn't show any records AT ALL or it doesn't show the records with a null value?

If the same exact SQL statement, using the EXACT same ODBC connection to the view, gives you different results when run from SQL Plus vs The SQL Designer, then you have a bug in the CR SQL designer. Surprising since it isn't doing much except sending the SQL.

Is the SQL plus connecting differently? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[beginnerkid]

It does not show any records at all. Nither SQL plus connecting differently.

 

[kenhamady]

I am not sure I understand your last answer?

Does SQL Plus use the exact same ODBC connection?

Does it behave the same or differently than the CR SQL designer?

If they both behave the same, the problem is with the view iteslf. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[beginnerkid]

I am sorry I wasnt so clear in my last answer. Yes I use same odbc connection for SQL plus as I am using for SQL designer.

It gives me records when I run the SAME query in SQL plus but doesn't give records when I run it in SQL designer.


Thanks & Regards

 

[kenhamady]

That has to be a bug in the SQL designer.
As far as I know, they should behave the same.

When running from INFO do you need different permissions in the database than if you are in SQL plus? Can you run the Info SQL designer against any other views in the database? Against tables? Maybe one table in the view is restricted somehow? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/