Application crashes when doing Left join between two views - Need help urgently

[techystud]

Hi,

I am doing a Left Join between two views wherein the column used in the ON condition has empty string rows in the first view. while running the query the application flashes, shows #ERROR value for some of the rows and hangs.
its a simple piece of code but its not working......

SELECT cstr([Updated Deals alive in a month].[Last Roll Date]), cstr([3m CDOR rates].Date) from [Updated Deals alive in a month]
Left outer join [3m CDOR rates]
on trim(cstr([Updated Deals alive in a month].[Last Roll Date])) = trim(cstr([3m CDOR rates].Date))

need help urgently...
Maria

[FishVal]

Hello, Maria.

CStr() function couldn't get Null as argument.
At the same time Nz() could not be used because it will cause join on Null values.
I suppose you should first filter your tables to get records where join fields are not Null and then join resulting datasets.

BTW, why don't you want to join on the fields as they are without converting them to strings?

Regards,
Fish

[techystud]

Hi,

There are no null values in the column specified - they are empty strings. Also to mention that the first column in the ON condition is from a view and is of datatype string whereas the second 'Date' column is from a table and its datatype is 'Date/Time'. thats why I am converting them to strings.
How do I go about it?

Maria

[FishVal]

...
There are no null values in the column specified - they are empty strings....

How do you know this?

[techystud]

Even if I adda Where clause to check for the
[Updated Deals alive in a month].[Last Roll Date] <> ' ',
its not working :((((((

Maria

[techystud]

How do you know this?

I queried the table and view to check for ' ' and Is null values.

[FishVal]

Do the queries (each build on a single dataset) including join expression as calculated field run without error?

[techystud]

Do the queries (each build on a single dataset) including join expression as calculated field run without error?

No...
Inner join gives result but before that the application hangs for while

[FishVal]

No...
Inner join gives result but before that the application hangs for while

What does it mean? :)

I've mentioned to check whether db engine properly calculates join expressions for all records. Something like the following.

SELECT cstr([Updated Deals alive in a month].[Last Roll Date]), trim(cstr([Updated Deals alive in a month].[Last Roll Date])) AS Expr1 from [Updated Deals alive in a month];
 

SELECT cstr([3m CDOR rates].Date), trim(cstr([3m CDOR rates].[Date])) AS Expr1 from [3m CDOR rates];
 

Additionally, reserved words like "Date" should be enclosed in square brackets.

No...
Inner join gives result but before that the application hangs for while

Do you say the same query with INNER JOIN instead of OUTER JOIN runs fine though in a noticeable period of time?
OUTER JOINs expected to consume more resources and perform more calculations. How many records do you have in the datasets? What is your computer configuration, Access/Windows version?

Additionally, what is behind the view you use in the query?

Regards,
Fish

[techystud]

What does it mean? :)

I've mentioned to check whether db engine properly calculates join expressions for all records. Something like the following.

SELECT cstr([Updated Deals alive in a month].[Last Roll Date]), trim(cstr([Updated Deals alive in a month].[Last Roll Date])) AS Expr1 from [Updated Deals alive in a month];
 

SELECT cstr([3m CDOR rates].Date), trim(cstr([3m CDOR rates].[Date])) AS Expr1 from [3m CDOR rates];
 

Additionally, reserved words like "Date" should be enclosed in square brackets.



Do you say the same query with INNER JOIN instead of OUTER JOIN runs fine though in a noticeable period of time?
OUTER JOINs expected to consume more resources and perform more calculations. How many records do you have in the datasets? What is your computer configuration, Access/Windows version?

Additionally, what is behind the view you use in the query?

Regards,
Fish

Hi,
yes, the wo queries as mentioned works fine.
the view contains 232 record in all with 143 empty string records and 89 data records. The other table does not contain any empty string records. So, ideally I should get 232 records on doing the Left Join..but its not happening.
Also, I could notice from the intermediate results that its trying to retrieve the matching records in both the view and the table, but for an empty string record in the view it is showing "#Error" in the second column.
Moreover,I am working on MS Access 2003 on Windows XP ..
...
Maria