X++ Query Question

Finance and Operations/AX Developer Forum
1

Hi Guys,

Could anyone help me out on this one?

Here’s the scenario:

  • I have 2 queries

  • The queries refer to the same table

  • The difference with the 2 queries is their WHERE clauses

Here’s the queries:

Query 1

select * from ProdRoute
order by CreatedDate asc, CreatedTime asc
where ProdRoute.CreatedDate >= str2date(“9/13/2010”, -1)
&& ProdRoute.CreatedDate <= str2date(“9/14/2010”, -1)
&& ProdRoute.CreatedTime >= 24540
&& ProdRoute.OprNumNext == 0

Query 2

select * from ProdRoute
order by CreatedDate asc, CreatedTime asc
where ProdRoute.CreatedDate == str2date(“9/15/2010”, -1)
&& ProdRoute.CreatedTime < 24540
&& ProdRoute.OprNumNext == 0

Question:

How would I combine these 2 queries into 1 so that I get their results combined?

Thanks in advance!

Regards,

Matt

2

u cannot combine these two queries - if the expected outcome of two queries is different.

in the first query you are trying to get the values for date 13 and 14 but for the second query your are trying to get the values for only 15

you can use this- if the intended output is one - that is for dates 13 and 14 ,15

select ProdRoute
order by CreatedDate asc, CreatedTime asc
where (ProdRoute.CreatedDate >= str2date(“9/13/2010”, -1)
&& ProdRoute.CreatedDate <= str2date(“9/15/2010”, -1))
&& ProdRoute.CreatedTime == 24540

&& ProdRoute.OprNumNext == 0

3

Thanks for your answer Kranthi.

Anyway, if you must know I’m trying to get all ProdRoute transactions from a certain time, let’s say 6:45am, in 13th to 6:45am of the 15th. Is there another way to do this?

Sorry, I’m just running out of ideas on this. [:S]

4

Reading through my original post, I believe it may not be so clear.

I’m trying to get a time range. Say from 6am of the 13th to 6am of the 17th.

Sorry for the vagueness.

5

try to use this query

while select table2 where
(table2.dates >= mkdate(13,09,2010))
&& (table2.dates <= mkdate(15,09,2010))
&& (table2.dates != mkdate(15,09,2010) || (table2.dates == mkdate(15,09,2010) && table2.time <= 24300))
&& (table2.dates != mkdate(13,09,2010) || (table2.dates == mkdate(13,09,2010) && table2.time >= 24300))
{
print table2.numbers;
}

6

Thanks for the suggestion Kranthi :slight_smile: I’ll try it out too. However, I did found out a way to this as suggested from another source.