Converting TSQL into X++

(0) Share

Report

Posted on by Mav

Hi Experts,

Please suggest how do i convert below tsql to x .

Aim is to get most recently created ID's (distinct) from myTable in which ID column is not unique index & multiple records are expected to have same Id.

select top 100 
    id,max(CREATEDDATETIME)
    from MyTable
    where id != ''
    group by id
    order by max(CREATEDDATETIME) desc

Thanks

Mav

I have the same question (0)

All responses (23)

Answers (0)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

You can easily use groupping and aggregations in X++, but you can't sort by an aggregation function. To achieve the same result, create a view returning the unsorted result and use this view in a select statement with 'order by'.

Was this reply helpful? Yes No
Mav on at

Like (0)

Report
Hi Martin,

I did created a view for MyTable as MyTableView with data source as simple query for My Table.

Can you please share x code of how to use this view in select statement to get the same result as TSQL query

Shared below is x & tsql i get for using the newly developed view, you will notice that the part order by createddatetime1 desc in x was lost in transit during converting x to tsql.

main() { select generateonly firstonly100 Id,maxof(CreatedDateTime1) from myTableView group by Id order by CreatedDateTime1 desc; // this was lost in transit from x to sql var sql = myTableView.getSQLStatement(); Info(sql); }

SELECT TOP 100 MAX(T1.CREATEDDATETIME1),T1.ID FROM MYTABLEVIEW T1 WHERE ((PARTITION=5637144576) AND (DATAAREAID=N'mycompany')) GROUP BY T1.ID

Thanks

Mav

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
You misundertand my point. Your code is still trying to do the gropping and sorting at once, which can't be done, as I mentioned above. Just using a view instead of table doesn't solve anything - my suggestion is using the view to split the problem to two parts.

Firstly, create a view where you'll use groupping and aggregation (MAX()). Because it'll be done in the view, you won't do it in X (as you're currently doing in your ccode). What you'll do in the select statement is merely sorting the view:

select myView order by MaxCreatedTime;

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

Ok my bad, will try that .

Quick question this view is expected to have humongous data.

So there is a need to make 1 trip to dB and store all that data in runtime .

Can i use insert_record on temp table to achieve above objective , I foresee 2 issues in that approach.

1. Making insert_recordset for temptable work with createddate1 (custom created date time filed in my view which works in the view ) &

2. Since I need to continuously parse this data I have to use multiple buffers of this temptable in subsequents while loops , how to ensure that data in temptable remains after changing its buffer name , will buffername.data() help in addressing that issue

If not then request you to share approach of achieving objective of making minimum dev trips to get humongous data and then keep parsing/traversing that data in subsequent while loops.

Thanks
Mav

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

You never mentioned any temporary table before. I thought that MyTable is a persistent table that you want to query. Isn't it the case? Your current questions look very different than your original requirement ("Aim is to get most recently created ID's (distinct) from myTable").

If it is a normal table, please forget all temporary tables, insert_recordsets and while loops - it would make the solution much slower. If perfomance is your concern, switching to such a design goes directly against your needs.

If MyTable indeed is a temporary table, let's forget all the previous discussion for a moment. Please tell us from the beginning what you're doing and then we can start talking about how to implement this different thing.

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

Hi Martin,

MyTable is regular table which has humongous data row wise and column wise.

MyTableView is view for above table, which has custom createddatetime1 filed which holds value of mytable.createddatetime.

Objectives

1. Get most recent 100 created Id’s.

2. Get all columns data for these 100 Id’s

3. Store above data set from step 2 say data set 2 in runtime I.e min trips to db.

4. Using data set 2 get filtered data , filter based on column “type” this filtered data is dat set 4

5. Do something with data set 4

6. Filter data set 4 with “sub type “ column to generate dat set 6.

7. do something with data set 6

Thanks

Mav

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

1. and 2. are trivial.

But what do you mean by the third point? You said you wanted to select the data but here you're talking about some storing (without explaining what you mean by that). Please give us more information.

Also, note that there is no universal solution for "do something". If you need our help with this part, you'll have to give us a much better description of your requirements.

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

Storing the data I.e on runtime like insert recordset into temp table so as to have that data available in run time and eliminating or minimizing the dev sql trips.

Do something is where I call my custom

Business logic which is expecting the filtered data . I can take part of that part .

Thanks

Mav

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

All these things seems to be unrelated the topic of this thread, which is about converting thre query. Feel free to create new threads for your new questions.

If your question about the query is answered, please verify the answer(s). If it isn't, please explain which part is still unclear.

Was this reply helpful? Yes No
Mav on at

Like (0)

Report
Hi Martin,

The grouping by 'ID' in view does not seems to be working

When i use the code, like you suggested , i do see recent ID but duplicate Id's despite having group by in my view.

My view is using simple query property of which are

1. Group by

data source: myTable

field: Id

2.Having

data source : myTable

field :Created date time

Type : Max

When i open the view using table browser i can see the

Code used

while select firstonly100 myTableView order by myTableView.CreatedDateTime1 desc { Info(strFmt('%1 :%2 :', myTableView.id,myTableView.CreatedDateTime1 )); }

Was this reply helpful? Yes No

Join the conversation

Community site session details

Converting TSQL into X++

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Converting TSQL into X++

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates