[development-axapta] Re: SubQuery in Axapta
Thanks,
I already tried it and worked. :)
rgds,
--- In development-axapta@yahoogroups.com, moldova2axapta
<[EMAIL PROTECTED]> wrote:
>
> Hi
>
> You can't sub query in Axapta but you can write that connection
and
> Resultset
>
> connection connection;
> Statement stmt;
> str sqlStr;
> Str 3 FirmId;
> ResultSet resultSet;
> connection = new Connection();
> stmt = connection.createStatement();
> firmId = CompanyInfo::find().dataAreaId;
> sqlStr = 'select * from custpaymschedline a, custpaymsched b '
> + 'where a.parentrecid=b.recid and b.extrecid=-1999372754 and
a.duedate= '
> +' (select max(duedate) from custpaymschedline cl where
> cl.parentrecid=a.parentrecid and dataareaId = \''+FirmId+'\') '
> + ' a.DataAreaId= \''+FirmId+'\' and b.DataAreaID =
\''+FirmId+'\'';
> resultset=stmt.executeQuery(SqlStr);
> while (resultset.next())
> {
> var_num=resultset.getreal(1) ;
> var_str = resultset.getstring(2);
> }
>
>
>
> Anton Tjiptadi yazm?s,:
> > Hi guys,
> >
> > did anyone knows how to use subquery in X++ development?
> >
> > for example, I have a query like this (in ANSI SQL) :
> >
> > select * from custpaymschedline a, custpaymsched b
> > where a.parentrecid=b.recid and b.extrecid=-1999372754 and
a.duedate=
> > (select max(duedate) from custpaymschedline cl where
> > cl.parentrecid=a.parentrecid)
> >
> > the purpose is I want to get a payment line which the due date is
> > maxmimum, if I dont use the subquer -> select max, what I
got is
> > a number of lines with many duedate. I already run this query in
SQL
> > analyzer and worked.
> >
> > .. or maybe in axapta there's another approach.. ?
> >
> > many thansk in advance.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > SPONSORED LINKS
> > Computer part
> > <http://groups.yahoo.com/gads?
t=ms&k=Computer+part&w1=Computer+part&w2=Programming+languages&w3=Mic
rosoft+axapta&w4=Support+exchange&c=4&s=90&.sig=yLpvcLTIDJ5FTkRJGsO11
w>
> > Programming languages
> > <http://groups.yahoo.com/gads?
t=ms&k=Programming+languages&w1=Computer+part&w2=Programming+language
s&w3=Microsoft+axapta&w4=Support+exchange&c=4&s=90&.sig=cuhEClK4dU4wa
pXFmKisbQ>
> > Microsoft axapta
> > <http://groups.yahoo.com/gads?
t=ms&k=Microsoft+axapta&w1=Computer+part&w2=Programming+languages&w3=
Microsoft+axapta&w4=Support+exchange&c=4&s=90&.sig=yfeG_U6QaLfPOZZIud
02Fg>
> >
> > Support exchange
> > <http://groups.yahoo.com/gads?
t=ms&k=Support+exchange&w1=Computer+part&w2=Programming+languages&w3=
Microsoft+axapta&w4=Support+exchange&c=4&s=90&.sig=hy8yRGMzrmxdphyITT
UeqA>
> >
> >
> >
> > -
---
> > YAHOO! GROUPS LINKS
> >
> > * Visit your group "development-axapta
> > <http://groups.yahoo.com/group/development-axapta>" on the
web.
> > * To unsubscribe from this group, send an email to:
> > [EMAIL PROTECTED]
> >
subject=Unsubscribe>
> > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> > Service <http://docs.yahoo.com/info/terms/>.
> >
> >
> > -
---
> >
>
YAHOO! GROUPS LINKS
Visit your group "development-axapta" on the web.
To unsubscribe from this group, send an email to: [EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[development-axapta] SubQuery in Axapta
Hi guys, did anyone knows how to use subquery in X++ development? for example, I have a query like this (in ANSI SQL) : select * from custpaymschedline a, custpaymsched b where a.parentrecid=b.recid and b.extrecid=-1999372754 and a.duedate= (select max(duedate) from custpaymschedline cl where cl.parentrecid=a.parentrecid) the purpose is I want to get a payment line which the due date is maxmimum, if I dont use the subquer -> select max, what I got is a number of lines with many duedate. I already run this query in SQL analyzer and worked. .. or maybe in axapta there's another approach.. ? many thansk in advance. SPONSORED LINKS Computer part Programming languages Microsoft axapta Support exchange YAHOO! GROUPS LINKS Visit your group "development-axapta" on the web. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[development-axapta] Export Financial Statement setup data
Hi, In Report Financial Statement (menu GL - Reports - Transaction - Periodic), can the setup data (setup rows and colums) transported to another database, so we don't need to setup the report again ? Thanks in advance, SPONSORED LINKS Computer part Programming languages Microsoft axapta Support exchange YAHOO! GROUPS LINKS Visit your group "development-axapta" on the web. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[development-axapta] running code or script before import data
Hi, I want to create and run some code before I execute Data Import Export (menu Administration - Period).. how can I do that ? for example : I want to import data to table CustTable, but first before Import/ Export started inserting my data, I want to delete existing data with createddate the same as the Import action happen. offcourse I can use overwrite method Insert in that table, so before Inserting, they can search the data first, and then delete it, but that means the delete function will run as many time as my data are, instead running just once. Thanks in advance, SPONSORED LINKS Computer part Programming languages Microsoft axapta Support exchange YAHOO! GROUPS LINKS Visit your group "development-axapta" on the web. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[development-axapta] using number sequence
Hi guys, can anyone help me what's the easy and fastest way for using number sequence. I already create a number sequence in Axapta setup, now I want to use it in one of my coding, for example when I create a new document. I already see the example in some of the existing Axapta class, but still confuss... :( or maybe I'm looking at the wrong/confusing example..hehe... thanks in advance. 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.
[development-axapta] Parsing variables from a FORM to a CLASS
Hi all, how to parsing variable inputed in the FORM to a CLASS ? I created a FORM and it that form there's a button to call a CLASS. I have a textbox for user input in the FORM and I want to parsing it so that inputed text known in the CLASS. I appreciate any help ? thanks in advance. 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.
[development-axapta] Run Report from a FORM
Hi, how to run reports from a Form. I plan to create 2 or more report and it will run under 1 form, so in that form will have 2 or more button, and when I clicked the button, it will run the corresponding report. thanks in advance, 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.
[development-axapta] Re: Joining 3 tables with QueryBuildDataSource
Hi andrew, thanks I tried your way, but first it 'cause error in the first QBDS in the QueryRange, it says "Invalid Range", and then I switch the table order so it looks like this : qbds = q.addDataSource(tablenum(InventJournalTrans)); qbds1 = qbds.addDataSource(tablenum(InventTable)); qbds1.joinMode(joinmode::InnerJoin); qbds1.addLink(fieldnum(InventTable, ItemId), fieldnum (InventJournalTrans, ItemId)); qrange = qbds1.addRange(fieldnum(InventTable, ESG_ProductType)); qrange.value(PrdType); strange this happens, when I debug the query, it shows a wrong link : SELECT * FROM InventTable WHERE InventJournalTrans.LineNum = InventTable.Height AND ((ESG_ProductType = 11)) where's the 'LineNum' and 'Height' come from ? thx, Tonz --- In development-axapta@yahoogroups.com, "ozzage" <[EMAIL PROTECTED]> wrote: > > > --- In development-axapta@yahoogroups.com, "Anton Tjiptadi" > <[EMAIL PROTECTED]> wrote: > > > > > > Hi, > > I want to join 3 tables with QueryBuildDataSource (QBDS), the > > statement is like this : > > > > > > ...the goal is, I want join table InventJournalTrans to InventDim > > with key InventDimId and join table InventJournalTrans to > > InventTable with key ItemId, but. > > > > OK, you're first joining InventDim to InventJournalTrans, which is > fine... > > qbds1 = qbds.addDataSource(tablenum(InventDim)); > > BUT then you're joining InventTable to InventDim, NOT to > InventJournalTrans > > > qbds2 = qbds1.addDataSource(tablenum(InventTable)); > > qbds1 here is the InventDim datasource. > > The order is important, even though they're inner joins. > > What you can do is start by adding InventTable to the query as qbds > > > qbds = q.addDataSource(tablenum(InventTable)); > > Then join InventJournalTrans as qbds1 > > > qbds1 = qbds.addDataSource(tablenum(InventJournalTrans)); > > (set up the links etc for the InventJournalTrans datasource here) > > and finally join InventDim as qbds2 to qbds1 (InventJournalTrans) > > > qbds2 = qbds1.addDataSource(tablenum(InventDim)); > > (set up the links etc for the InventDim ds here) > > Hope that helps > > Andrew Jones 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.
[development-axapta] QueryBuildDataSource
Hi all, I wrote a QueryBuildDataSource like this : qbds = q.addDataSource(tablenum(InventJournalTrans)); qbds1 = qbds.addDataSource(tablenum(InventTable)); qbds1.joinMode(joinmode::InnerJoin); qbds1.addLink(fieldnum(InventTable, ItemId), fieldnum (InventJournalTrans, ItemId)); . . strange thing is when I debug it, the query link is different from what I wrote -> SELECT * FROM InventTable WHERE InventJournalTrans.LineNum = InventTable.Height ... where's the 'LineNum' and 'Height' come from ? thx, Tonz 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.
[development-axapta] Joining 3 tables with QueryBuildDataSource
Hi, I want to join 3 tables with QueryBuildDataSource (QBDS), the statement is like this : qbds = q.addDataSource(tablenum(InventJournalTrans)); qbds1 = qbds.addDataSource(tablenum(InventDim)); qbds1.joinMode(joinmode::InnerJoin); qbds1.addLink(fieldnum(InventDim, InventDimId),fieldnum (InventJournalTrans, InventDimId)); qrange = qbds1.addRange(fieldnum(InventDim, InventLocationId)); qrange.value(WareId); qbds2 = qbds1.addDataSource(tablenum(InventTable)); qbds2.joinMode(joinmode::InnerJoin); qbds2.addLink(fieldnum(InventJournalTrans, ItemId), fieldnum (InventTable, ItemId)); qrange = qbds2.addRange(fieldnum(InventTable, ESG_ProductType)); qrange.value(PrdType); - ...the goal is, I want join table InventJournalTrans to InventDim with key InventDimId and join table InventJournalTrans to InventTable with key ItemId, but. strange thing is the result query is like this : SELECT * FROM InventJournalTrans JOIN * FROM InventDim WHERE InventJournalTrans.InventDimId = InventDim.inventDimId AND ((InventLocationId = 01)) JOIN * FROM InventTable WHERE InventDim.InventLocationId = InventTable.ItemId AND ((ESG_ProductType = 11)) ---> failure is the join between InventJournalTrans and InventTable went in to the wrong key, was I making a mistake in QBDS ?? can anyone help me ? thanks in advance, 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.
[development-axapta] Select Top 10 in Axapta ?
Hi, has anyone knows how to "Select Top 99..." in Axapta ? just like in SQL -> "Select Top 20 * from InventTable" thanks in advance Yahoo! Groups Sponsor ADVERTISEMENT 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.
