469,578 Members | 1,671 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

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

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
Nov 4 '08 #1
16 2248
FishVal
2,653 Expert 2GB
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
Nov 4 '08 #2
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
Nov 4 '08 #3
FishVal
2,653 Expert 2GB
...
There are no null values in the column specified - they are empty strings....
How do you know this?
Nov 4 '08 #4
Even if I adda Where clause to check for the
[Updated Deals alive in a month].[Last Roll Date] <> ' ',
its not working :((((((

Maria
Nov 4 '08 #5
How do you know this?
I queried the table and view to check for ' ' and Is null values.
Nov 4 '08 #6
FishVal
2,653 Expert 2GB
Do the queries (each build on a single dataset) including join expression as calculated field run without error?
Nov 4 '08 #7
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
Nov 4 '08 #8
FishVal
2,653 Expert 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. 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];
  2.  
Expand|Select|Wrap|Line Numbers
  1. SELECT cstr([3m CDOR rates].Date), trim(cstr([3m CDOR rates].[Date])) AS Expr1 from [3m CDOR rates];
  2.  
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
Nov 4 '08 #9
What does it mean? :)

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

Expand|Select|Wrap|Line Numbers
  1. 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];
  2.  
Expand|Select|Wrap|Line Numbers
  1. SELECT cstr([3m CDOR rates].Date), trim(cstr([3m CDOR rates].[Date])) AS Expr1 from [3m CDOR rates];
  2.  
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
Nov 4 '08 #10
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
Hi,
Here is a ssnap shot of the result - there are 232 rows in actual.

Expr1000 Expr1001
2008/08/26 2008/08/26
2008/08/29 2008/08/29
2008/09/30 2008/09/30
#Error
#Error
#Error
2008/08/27 2008/08/27
#Error
#Error
2008/10/17 2008/10/17
#Error
#Error
... and it follows...
Maria
Nov 4 '08 #11
FishVal
2,653 Expert 2GB
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
Aahh.

How could I overlook it. Sure. With LEFT OUTER JOIN it fetches Nulls in right side dataset fields where left side dataset records doesn't match any in right side dataset.

So, just use Nz() or Iif() function to detect Null and either replace it or skip converting to string in fields clause of the SQL statement.

Regards,
Fish
Nov 4 '08 #12
Hi,
Here is a ssnap shot of the result - there are 232 rows in actual.

Expr1000 Expr1001
2008/08/26 2008/08/26
2008/08/29 2008/08/29
2008/09/30 2008/09/30
#Error
#Error
#Error
2008/08/27 2008/08/27
#Error
#Error
2008/10/17 2008/10/17
#Error
#Error
... and it follows...
Maria
Hi,
I understand that Outer Joins consume more resources and so take more time, but my application flashes, hangs or closes by itself. it stops responding.
Nov 4 '08 #13
Aahh.

How could I overlook it. Sure. With LEFT OUTER JOIN it fetches Nulls in right side dataset fields where left side dataset records doesn't match any in right side dataset.

So, just use Nz() or Iif() function to detect Null and either replace it or skip converting to string in fields clause of the SQL statement.

Regards,
Fish
Hi,

But there are empty string values in the records of the View for which i am getting "#Error" values in the corresponding rows of the second column. Please suggest on how to deal with this?
Nov 4 '08 #14
FishVal
2,653 Expert 2GB
Hi,

But there are empty string values in the records of the View for which i am getting "#Error" values in the corresponding rows of the second column. Please suggest on how to deal with this?
The reason to convert values to string in join criteria is clear, but why do you need to convert to strings values you fetch?
Nov 4 '08 #15
The reason to convert values to string in join criteria is clear, but why do you need to convert to strings values you fetch?
Hi,
I am sorry..that was my mistake as it is not required so i removed it. But still............. :((((
Nov 4 '08 #16
FishVal
2,653 Expert 2GB
Ok.

Seems, I have no more clever ideas.
Would you like to attach a sanitized copy of your database to the thread?
Nov 4 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Andrei Ivanov | last post: by
3 posts views Thread by Ian Boyd | last post: by
3 posts views Thread by rjaw | last post: by
6 posts views Thread by Jim Devenish | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.