How to translate SQL "and" "or" in Business Central Query Object

(0) Share

Report

Posted on by Giuseppe Montanaro

Hi,

I need to translate a Transact-SQL in Query Object but i don't know how to translate conditions.

For Example:

select
m.id idMandati, m.Nome nomeMandato, coalesce(SF_DataAperturaPrimaLav(p.id),m.DataCessione) DataCessione,m.idGerp13 tipoMandato,
m.idGerp71 TipoCessionario, m.idAnagrafica idAnagCedente,
p.id, p.CodiceContabile, p.Nome, p.ContestatoDaCedente,
pc.id idCollegato, pc.idAnagrafica idAnagraficaCollegato, pc.TipoCollegato
from
mandati m, pratiche p, lavorazioni l, esiti_lista el, pratiche_collegati pc
where
m.id=911
and m.UtenteCancellazione is null
and m.id=p.idMandati and p.utentecancellazione is null
and l.idPratiche = p.id and l.UtenteCancellazione is null
and l.idEsiti_lista = el.id
and pc.idPratiche=p.id and (pc.Liberato=0 or (pc.Liberato=1 and pc.DataLiberazione>DataInizio)) #and pc.NoCR<>1
and (pc.DataCancellazione is null or pc.DataCancellazione>=DataFiltroCanc)
# and m.id = 1325
group by pc.id

Can someone help me?

Thanks

All responses (7)

Answers (1)

Verified answer

DAnny3211 9,264 Super User 2024 Season 1 on at

Like (1)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

Hi

Giuseppe

unfortunately the only solution is to redesign the query in AL

Check my answer if it was helpful

DAniele
Suggested answer

YUN ZHU 71,565 Super User 2024 Season 2 on at

Like (1)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

Hi, as far as I know, there is no tool to do this. You need to redesign Query according to your needs. In this case, you need to refer to the MS Docs provided by Marco.

Hope this helps.

Thanks.

ZHU
Inge M. Bruvik 32,746 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

The query function in Business Central can only access Business Central data. So that will not work if you try to access a table outside of Business Central. Then you will have to use some kind of integration tool. F.ex use dataverse or another kind of tool.

Are you running BC online or on prem?

You could f.ex. look at the Virtual table functionality.

docs.microsoft.com/.../powerplat-faq
Giuseppe Montanaro 5 on at

Like (0)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

Hi, this does not come from Business Central but i would like to transiate this into Business central code
Inge M. Bruvik 32,746 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

I think it will be easier to help you if you try to explain what Business Central tables you want to access and how you want to join them.

The sql sample you provided does not seem to come from a Business Central database.
Giuseppe Montanaro 5 on at

Like (0)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

Hi Marco,

thanks for your answer but i'm looking for some "complex" query and documentation doesn't really talk about that.

Regards,

Giuseppe.
Marco Mels on at

Like (0)

Report

RE: How to translate SQL "and" "or" in Business Central Query Object

Hello,

Maybe this helps somehow:

docs.microsoft.com/.../devenv-query-object

Thanks.

How to translate SQL "and" "or" in Business Central Query Object

Helpful resources

News and Announcements

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Leaderboard

Featured topics

Product updates

How to translate SQL "and" "or" in Business Central Query Object

Helpful resources

News and Announcements

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Subscribe to

Leaderboard

Featured topics

Product updates