Alias in SELECT statement

Finance and Operations/AX Developer Forum
1

I am very new to x++. Is it possible to use an alias in a select statement? I want to create a new record on a table based on an existing record, and I am trying to modify the insert statement. x++ doesnt seem to like “select x as y”

CRMTemplate_Headers TemplateHdrFrom;
CRMTemplate_Headers TemplateHdrTo;
CRMTemplate_Headers head;

str 20 nwTmpId;
int tmpltid;

;

select maxof(TemplateNumID) from head ;
tmpltid = head.TemplateNumID+1;
nwTmpId = strfmt(“CRM_Template_%1”,head.TemplateNumID+1);

ttsBegin;
insert_recordset TemplateHdrTo(TemplateID, Name, QuoteType, Active, StartDate, EndDate, TemplateNumID
select nwTmpId as TemplateID, Name, QuoteType, Active, StartDate, EndDate, tmpltid as TemplateNumID
from TemplateHdrFrom
where TemplateHdrFrom.TemplateID == templateId;
ttsCommit;

When I save I get "Table nwTmpId does not exist ", I have tried adding parenthesis etc and only get syntax errors.

Help would be greatly appreciated.

2

I suppose I could do this as a “while” select then .insert if anyone can help with the syntax (please?) [*-)]

3

try

select x y , z from table. that might work.

4

thanx for the response, could you be a little more specific, please?

5

I’m trying it a different way but now I get a ‘TemplateHdrFrom table does not exist’ error on the

maxid = select maxof(TemplateNumID) from TemplateHdrFrom).TemplateNumID; line_._

Im so confused…[+o(]

void copyTemplateHeadr(int templateId)
{

CRMTemplate_Headers TemplateHdrFrom;
CRMTemplate_Headers TemplateHdrTo;

int maxid;

;

maxid = (select maxof(TemplateNumID) from TemplateHdrFrom).TemplateNumID;

ttsBegin;

while select * from gravTemplateHdrFrom where gravTemplateHdrFrom.TemplateNumID == templateId
{
TemplateHdrTo.TemplateID = maxid+1
//insert statement here
}

ttsCommit;

}

6

The best think you can do is take a look at the select statement syntax specification. For example, the AX2012 version is here.

7

not intending to sound ungrateful, but you say that as if I havnt spent the better part of 2 days trying to resolve what appears to be a simple issue to someone with more experience

8

Query : select maxof(TemplateNumID) from TemplateHdrFrom).TemplateNumID

in sql we can write the same query with alias

try this statement

select maxof(TemplateNumID) MaxTemplateNumID from TemplateHdrFrom).TemplateNumID

if you get an error.

create a Query in AOT . include this statement in that query object . and call that query object

``

      Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryHavingFilter       havingFilter;
    QueryRun                queryRun;

you can refer this link for example.
http://daxmusings.blogspot.com/2011/09/query-and-new-related-objects-in-ax.html

hope this helps
9

You asked for syntax, so I gave it to you. If you’ve already read it, you wouldn’t need this thread about aliases at all.

And regarding

maxid = select maxof(TemplateNumID) from TemplateHdrFrom).TemplateNumID;

you have to enclose the whole select to parentheses and use table name instead of a buffer defined outside the statement, so your example would look like:

maxid = (select maxof(TemplateNumID) from CRMTemplate_Headers).TemplateNumID;

Again, it’s clearly documented on MSDN - see Select Statements on Fields.

You could have spared two days…

10

Again not meaning to sound ungrateful…

  1. I DID already read it, many times, and still did not see my syntax error
  2. If it’s clear, it’s clear to you. To me, it’s clear as MUD

If this forum is NOT for newbies cutting their teeth on x++ please let me know now so I dont waste anymore time, otherwise I request for the sake of others like myself please try to remember what it was like for you when you were just starting to learn.

Between the response I managed to work out a solution, and I thank you all for your time.