Hi Matt,
>I cut out the cases where the address has changed but the custtable
has not..
What do you mean with this?
The select I gave should show all records from custTable and all
records (that match) from addressTable (so it CAN show more then 1
record from addresstable for 1 record from CustTable).
Is it possible your SQL version worked because it covers all
dataarea's of Axapta?
Regards,
Nico
--- In [email protected], "Matt Benic" <[EMAIL PROTECTED]>
wrote:
>
> 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.
