[development-axapta] Re: query with or array field

Fri, 10 Feb 2006 02:41:26 -0800

Hi Agus

I'm not sure if you know, but you can also use an X++ select statement
as the basis for a form data source, just as you can with a Query object!!

I completely agree with your comments about the "standard way" of
doing things, but maybe this provides you with an alternative anyway.

Hopefully this description and example code snippet is useful and
somewhat interesting.  This is snipped from an email I sent to a
colleague so it's formatted a bit strangely.

----

Here's the init() of a form datasource I'm using to show this concept
to somebody.  This is based on a simple form with just an InventTable
grid.

The query is just an example to show the sort of thing which can be
done.  In this case it limits the records to only those without
attached inventory transactions.

You can see with the SQL trace that the form punches out a single SQL
query based on the X++ query which I have supplied.  Also the field
list is obviously being used as everything except for itemid and name
appear empty in the grid.  Normally it would be better to select all
of the fields, I think.

// This is the DATASOURCE init()
public void init()
{
    InventTrans inventTrans;
    ;

    super();

    // This query will be used by the form datasource.  Note that on
the datasource, the AutoQuery property must
    // be set to false or it will not use the standard query from the
form design and ignore this one.
    //
    // nofetch keyword stops it from actually RUNNING the query here.
It will still be executed by the form datasource
    // but without 'nofetch' it gets run twice.
    //
    // index hints work as expected.
    //
    // It still adds the bloody order by if we don't add our own, but
we can use one in the statement (as below) and it
    // will use that instead.
    //
    // It also adds the firstfast statement (for Oracle) to the
generated query, and we can't get rid of that without the first rows fix.

    select nofetch itemId, itemName from inventTable
    order by itemName
    notexists join inventTrans
    where inventTrans.ItemId == inventTable.ItemId;
}

I have not overridden any other methods.  The only thing I've done is
to turn the AutoQuery property to No on the datasource.

Note that "inventTable" as used in the query is the auto-variable
referrring to the form datasource.  "inventTrans" is declared locallly
as you can see.



--- In development-axapta@yahoogroups.com, Agus Riyadi <[EMAIL PROTECTED]>
wrote:
>
> Hi Barry,
>
> The reason is simply I want to follow the common way to change form data
> source query, which is passing new query object to the data source.
If it
> could, then we can do it in a loop and further can be used later for
larger
> field array,which is a good programming practice I think.
>
> But it seems can't be done right now, as Andrew Jones said.
> I will use select statement instead.
>
> Thanks  Barry and Andrew for your comment.
>
>
> Regards,
>
> Agus
>
> On 2/9/06, Bayliss, Barry <[EMAIL PROTECTED]> wrote:
> >
> > Agus,
> >
> > Is there a reason why you are trying to do this using the Query
> > structure?  Why not use
> >
> > While
> > Select  ProdTable
> > Where   (ProdTable.dimension[1] == 'Admin' ||
ProdTable.dimension[2] ==
> > 'fina')
> > {
> >        .....
> > }
> >
> >
> > Barry.
> >
> >
> > -----Original Message-----
> > From: development-axapta@yahoogroups.com
> > [mailto:[EMAIL PROTECTED] On Behalf Of ozzage
> > Sent: Tuesday, 7 February 2006 6:47 PM
> > To: development-axapta@yahoogroups.com
> > Subject: [development-axapta] Re: query with or array field
> >
> > Hi Agus
> >
> > I have never seen a solution to your problem.  As far as I know, it's
> > simply not possible to reference array fields when utilising the
> > extended query syntax.
> >
> > I live in hope that somebody will correct me :)
> >
> > Regards
> >
> > Andrew Jones
> >
> > --- In development-axapta@yahoogroups.com, Agus Riyadi <agusacil@>
> > wrote:
> > >
> > > Hi Solary,
> > >
> > > I sill haven't solved the problem.
> > > Thanks for your reply, but that won't work since the first and third
> > > parameter for strfmt will be blank because it is supplied with blank
> > value
> > > rather than column name. When I change it to colum name -which the
> > same as
> > > the first job I posted- it raises query extended failure.
> > >
> > > Regards,
> > >
> > > Agus
> > >
> > >
> > > On 2/6/06, solary_luo <luoqf515@> wrote:
> > > >
> > > > Hi,
> > > >
> > > > Have you resolved?  If not, you can try it.
> > > >
> > > > Replace the strRange sentence like below:
> > > >
> > > > strRange = strfmt('(%1 == "%2") || (%3 ==
"%4")',prodtable.dimension
> > > > [1],'admin',prodtable.dimension[2],'fina');
> > > >
> > > > Regards,
> > > >
> > > >
> > > >
> > > >
> > > > --- In development-axapta@yahoogroups.com, "Agus Riyadi"
> > > > <agusacil@> wrote:
> > > > >
> > > > > Dear All,
> > > > >
> > > > > There have been a lot of discussion about query with or, but I
> > > > don't
> > > > > find one that address to array field.
> > > > >
> > > > > I have a query like this, which I need to change it to a query
> > > > > object :
> > > > >
> > > > > select prodtable where prodtable.dimension[1] == 'admin'
> > > > >         || prodtable.dimension[2] == 'fina';
> > > > >
> > > > > I tried this job to buid a query object :
> > > > >
> > > > > static void job11(Args _args)
> > > > > {
> > > > >
> > > > >     ProdTable                   ProdTable;
> > > > >     QueryBuildDataSource        ds;
> > > > >     Query                       q;
> > > > >     QueryRun                    qr;
> > > > >     QueryBuildRange             range;
> > > > >     Str                         strRange;
> > > > >     ;
> > > > >
> > > > >     q = new Query();
> > > > >     ds = q.addDataSource(tablenum(ProdTable));
> > > > >     range = ds.addRange(fieldnum(ProdTable,Dimension));
> > > > >
> > > > >     strRange = strfmt('(%1 == "%2") || (%3 == "%4")',
> > > > >         'dimension[1]','admin',
> > > > >         'dimension[2]','fina');
> > > > >
> > > > >     range.value(strRange);
> > > > >
> > > > >     qr = new QueryRun(q);
> > > > >
> > > > >     while(qr.next())
> > > > >     {
> > > > >         PRODTABLE = qr.getNo(1);
> > > > >         info(prodTable.ProdId);
> > > > >     }
> > > > >
> > > > > }
> > > > >
> > > > > But an error raised :
> > > > >
> > > > > Query extended range failure: Right parenthesis expected
near pos
> > > > 0.
> > > > >
> > > > >
> > > > > Can anyone give a clue to correct that code, or there any
> > > > > alternatives ?
> > > > > Thanks for any comments.
> > > > >
> > > > >
> > > > > Regards,
> > > > >
> > > > > Agus
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>

SPONSORED LINKS
Computer part Programming languages Microsoft axapta
Support exchange

YAHOO! GROUPS LINKS



Reply via email to