The below will work, but for what purpose? Nothing is being selected
from the new version of the table with the correlation name D. Also,
there is no joining of the b and d representation of the table, so you
will get a cartesian product. Lastly, what is the point of the outer
joins to those tables when there is nothing being selected from them.
Taking a guess that you just shortened up the original query for
posting to the list and would suggest you try the following:
select count(distinct(b.ActionItemId)) as Total_Records
from ActionItem b
LEFT JOIN actionstmts d
ON b.actionitemid d.actionitemid
LEFT JOIN ActionCaseType c
ON c.actionitemid=b.actionitemid where
Upper(actionname) like Upper('%')
Dave Nance
First Health Services, Corp.
(804)527-6841
>>> "akhileshmittal"
<db2udbdba-ezmlmshield-x20388975.[Email address protected] 9/29/05
6:48:42 AM >>>
select count(distinct(b.ActionItemId)) as Total_Records from ActionItem
b
LEFT JOIN actionstmts d ON b.actionitemid d.actionitemid, ActionItem d
LEFT
JOIN ActionCaseType c ON c.actionitemid=d.actionitemid where
Upper(actionname) like Upper('%')
This will work!!
"Amit Singal " <db2udbdba-ezmlmshield-x48917622.[Email address
protected] on
29 Sep 2005 16:11
To: "LazyDBA Discussion" <[Email address protected]
cc:
Our Ref: Your Ref:
Subject: Join
select count(distinct(b.ActionItemId)) as Total_Records from ActionItem
b
LEFT JOIN actionstmts d ON b.actionitemid =d.actionitemid,ActionItem b
LEFT
JOIN ActionCaseType c ON c.actionitemid=b.actionitemid where
Upper(actionname) like Upper('%')
error:-- A reference to column "B.ACTIONITEMID" is ambiguous.
Kindly suggest the solution
Regards
Amit Singal
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
******************************************************************
This message originated from the Internet. Its originator may or may
not be who they claim to be and the information contained in the
message and any attachments may or may not be accurate.
******************************************************************
-----------------------------------------
***********************************************************************
This e-mail is confidential. It may also be legally privileged.
If you are not the addressee you may not copy, forward, disclose
or use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return e-mail.
Internet communications cannot be guaranteed to be timely,
secure, error or virus-free. The sender does not accept liability
for any errors or omissions.
***********************************************************************
"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
==============================================================================
DB2 & UDB email list listserv db2-l LazyDBA home page