Thanks, but the problem is that I want to select from custtable and address
if either of the two have been changed, not just if custtable has been
changed, so if I limit the custtable select before doing the join, I cut out
the cases where the address has changed but the custtable has not..
Regards,
Matt Benic
Axapta and .Net Developer
UTi Sun Couriers Division
"The universal aptitude for ineptitude
makes any human accomplishment an incredible miracle."
- Col. John P. Stapp
www.smallfrymobile.com <http://www.smallfrymobile.com>
-----Original Message-----
From: nicolaan [mailto:[EMAIL PROTECTED]
Sent: 09 March 2005 05:17 PM
To: [email protected]
Subject: [development-axapta] Re: Outer join in X++
Hi Matt,
I think this should work.
while SELECT CUSTTABLE
WHERE (ADDRESS.ADDRTABLEID == 77) &&
(CUSTTABLE.MODIFIEDDATE > mkdate(1, 3, 2005))
OUTER JOIN ADDRESS
where CUSTTABLE.RECID == ADDRESS.ADDRRECID
ADDRESS.ADDRESSTYPE == 'Invoice'
Axapta works different with joins.
First you define your "where clause" for the main table and after
your join you specify an extra "where" with your join specifications.
So the 2nd where is your "on" keyword.
I searched for "outer join" in classes to get an example from Axapta.
Regards,
Nico
--- In [email protected], "Matt Benic" <[EMAIL PROTECTED]>
wrote:
>
> Hi all,
> I am trying to design a query in X++ which works in SQL. The SQL
version is:
>
> SELECT ADDRESS.*, CUSTTABLE.*
> FROM CUSTTABLE LEFT OUTER JOIN ADDRESS
> ON CUSTTABLE.RECID = ADDRESS.ADDRRECID
> WHERE (ADDRESS.ADDRTABLEID = 77) AND
> (CUSTTABLE.MODIFIEDDATE > CONVERT(DATETIME, '2005-
03-01
> 00:00:00', 102)) AND
> (ADDRESS.ADDRESSTYPE = 'Invoice')
>
> Which returns a single row. I am trying to use the following in X++
and it
> returns every row:
>
> while select custTable outer join address
> where (address.AddrTableId == tableNum(CustTable) &&
> address.AddrRecId == custTable.RecId) &&
> (address.AddressType == "Invoice") &&
> (custTable.modifiedDate > sinceDate ||
> address.modifiedDate > sinceDate)
> { //do stuff with adddress and custTable }
>
> I can't seem to find the equivalent of the 'on' keyword in X++, how
would I
> achieve this? I don't want to manually go through each custTable
record and
> do the check in X++, I would rather let the DB do the work.
>
> Thanks,
>
> Matt Benic
> Axapta and .Net Developer
> UTi Sun Couriers Division
>
> "The universal aptitude for ineptitude
> makes any human accomplishment an incredible miracle."
> - Col. John P. Stapp
> www.smallfrymobile.com
Yahoo! Groups Links
| Yahoo! Groups Sponsor | |
|
|
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/development-axapta/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
