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

I have the same question (0)

All responses (7)

Answers (1)

Marco Mels on at

Like (0)

Report

Hello,

Maybe this helps somehow:

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

Thanks.

Was this reply helpful? Yes No
Giuseppe Montanaro 5 on at

Like (0)

Report

Hi Marco,

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

Regards,

Giuseppe.

Was this reply helpful? Yes No
Inge M. Bruvik 1,105 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Giuseppe Montanaro 5 on at

Like (0)

Report

Hi, this does not come from Business Central but i would like to transiate this into Business central code

Was this reply helpful? Yes No
Inge M. Bruvik 1,105 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,331 Super User 2025 Season 2 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

DAnny3211 11,397 on at

Like (1)

Report

Hi

Giuseppe

unfortunately the only solution is to redesign the query in AL

Check my answer if it was helpful

DAniele

Was this reply helpful? Yes No