a problem to get null date from table 1

[Plato2]

I'm using the following formula to get null dates from table:
isnull({TITLE_XREF.delete_date_time}) and isnull({TITLE_XREF.cancel_date_time})

but this doesnt return any rows.

does anybody know why?

Thanks in advance

 

[pelajhia]

you may wish to change to

isnull(fielda) OR
isnull(fieldb)

rather than 'and'

Unless you are wanting to definitely only see values where BOTH are null.
If that is the case, you should verify that there are records which meet your sql criteria by querying the db outside of crystal.

 

[synapsevampire]

Also check to make sure that you don't have the File->Convert Null values to default turned on

Your post is skimpy, trying posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

Many databases maintain dates that you would expect to be null as a value (such as 1/1/1700), right click the field and select browse data to learn what's in there.

-k

 

[Plato2]

I think I know why this error happens there is a problem with left join and now it gives dll error. And I don't know how to trace the error.
I tried to create another report and it gives the same error when I use left join.

Is there is a way to solve the problem with outer join or left joins?

Thanks

 

[pelajhia]

Not sure what version of cr you're using, but you may go to Database->show sql query, copy and paste that to something like sql plus or another such tool and try and work out the query you want to use before putting it in crystal.
Normally you would use an inner join if you wanted ONLY records from both tables where there is a match in the linked fields.
Left Outer is when you wish to return all records in the main/lookup table and include records from the secondary table when there is a match.
Are you familiar with what the various joins can/will do? If not reading up on that could be a good starting point.

Please provide more background info on the report, as suggested above, if you want better feedback

 

[synapsevampire]

I understand that you're busy, but you need to supply technical information to get meaningful help.

Otherwise you're probably wasting your time and those that are kind enough to assist you.

This is basic info that a tech support person would ask, if you want a BLOG, try a chat room.

-k

 

[Plato2]

I'm using Crystal Report 8.5 and when I try to do left join between two tables(I use Visual Linking Expert) it gives ODBC error - error in dll.

I don't know why is this error. Is this Crystal Report 8.5 bug or something?

When I want to see what sql query was generated, I go to Database->show sql query but show sql query option is not availabale

Is there is any way I can test what sql query was generated by Visual Linking Expert

Thanks in advance

 

[pelajhia]

I think we should ignore the dll error for now and start at the beginning. You were not getting the dll error to begin with - certainly you should not receive this if you are just moving from an inner join to a l.o. - in a typical install/database situation, however there are still some questions as to whether this is the case.

You have CR8.5 on your workstation. What type of database is the data stored in (oracle, sql, excel file,etc.)? Are you grabbing data from only one datasource, or more than one?
What are the two main tables (you can 'bogus' the names) and a few fields, including the ones you want to link. You can throw in datatypes for the linked fields; that would be helpful to know (check both tables!)

If you switch back to inner join, can you get to the 'show sql' menu item?

 

[synapsevampire]

Look at the 4 pieces of information I requested, and then look at your response.

I don't believe that you're really interested in getting a solution to this problem, I think that "you" want to resolve it.

A DLL error might mean that you're using a bad driver or some such, but wither you don't know what a database is, or how one might learn what was used in your report, or you don't think that databases and connectivity have anything to do with your report, which is silly.

If you're using a native connection to a database, trty switching to ODBC, the Database->Show SQL Query should work then.

-k

 

[pelajhia]

I would tend to agree with synapsevampire, here. You do seem to be ignoring the basics and jumping all over the place.
You sound pretty unfamiliar with crystal and databases in general, however that is never a bar to learning around this forum. Not being willing to come across with even meager details, however, will be a problem.

Makes me wonder if Plato is really your name

 

[Plato2]

I am using 2 ODBC connections for the report. Each ODBC connection connects to 2 different databases on the same SQL server.

There are 2 main tables I'm trying to link
1)TITLE
2)TITLE_BILLING

The field that links these 2 tables is:
title_no and datatype is int.
The link I'm trying to implement is
TITLE.title_no*=TITLE_BILLING.title_no

I tried to remove left outer join today but it still gives ODBC error-error in dll.

This problem looks weird and confusing to me because yesterday the report worked with inner join.

Any ideas on why it can happen and how to resolve this problem would be appreciated.

Thanks in advance

 

[synapsevampire]

That explains the problem, Crystal doesn't allow for that - wasn't that simple once you bothered to post technical information?

You have options, create a View on the SQL Server to join the disparate tables and expose the View as the data source for Crystal.

Another option might be to hard code the SQL in using an ADO connection, but this is less flexible.

-k

 

[Plato2]

Thank you this solution helped me to solve this problem!