Value.NativeQuery()

[Captuleio]

Olá, estou tentando fazer adicionar parametros de substituição na função Value.NativeQuery() para conexão Oracle; mas não estou conseguindo escrever corretamente a linguagem M.

Modelo:

let
Fonte = Oracle.Database("Servidor",[CommandTimeout=#duration(0, 4, 30, 0)]),
Query = Value.NativeQuery(Fonte,"select p.cd_empresa, p.nm_empresa from Empresa p where p.dt_cadastro >= @StartDate and p.dt_cadastro < @EndDate",[StartDate=”RangeStart”, EndDate=”RangeEnd”],[EnableFolding=true]),
#"Tipo Alterado" = Table.TransformColumnTypes(Query,{{"CD_EMPRESA", type text}})
in
#"Tipo Alterado"

Poderiam me auxiliar no descritivo correto.

[Smauro · Accepted solution]

Hi @Captuleio , sorry, my understanding of portuguese is just enough to see that you're trying to pass parameters using Value.NativeQuey().

A little bit of M:

[
   RangeStart = #datetime(2020,7,23,15,0,0),
   Quotes = "RangeStart",
   Actual = RangeStart,
   ToText = DateTime.ToText(RangeStart, "yyyy/MM/dd HH:mm:ss")
]

returns

[
    RangeStart = 23/07/2020 15:00:00 as datetime,
    Quotes = RangeStart as text,
    Actual = 23/07/2020 15:00:00 as datetime,
    ToText = 2020/07/23 15:00:00 as text
]

Now, parameters are more easily passed as text. Try this

Value.NativeQuery(
        Fonte,
        "select p.cd_empresa, p.nm_empresa from Empresa p where p.dt_cadastro >= to_date(@StartDate, 'YYYY/MM/DD HH24:MI:SS') and p.dt_cadastro < to_date(@EndDate, 'YYYY/MM/DD HH24:MI:SS')",
        [
            StartDate = DateTime.ToText( RangeStart, "yyyy/MM/dd HH:mm:ss" ),
            EndDate = DateTime.ToText( RangeEnd, "yyyy/MM/dd HH:mm:ss" )
        ],
        [EnableFolding = true] )

Regards,

Spyros

[Captuleio]

Hello!!!

Configuração abaixo:

let
#"Fonte" =
Value.NativeQuery(
Oracle.Database("Servidor",[CommandTimeout=#duration(0, 4, 30, 0)]),
"select p.cd_empresa, p.nm_empresa, p.dt_cadastro
from Empresa p
where to_date(p.dt_cadastro, 'YYYY/MM/DD HH24:MI:SS') >= to_date(@StartDate, 'YYYY/MM/DD HH24:MI:SS')
and to_date(p.dt_cadastro, 'YYYY/MM/DD HH24:MI:SS') < to_date(@EndDate, 'YYYY/MM/DD HH24:MI:SS')",
[ StartDate = DateTime.ToText( RangeStart, "yyyy/MM/dd HH:mm:ss" ),
EndDate = DateTime.ToText( RangeEnd, "yyyy/MM/dd HH:mm:ss" ) ]
)
in
#"Fonte"

Return errors:

DataSource.Error: Oracle: ORA-00936: missing expression
Detalhes:
DataSourceKind=Oracle
DataSourcePath=Servidor
Message=ORA-00936: missing expression
ErrorCode=-2147467259

[Captuleio]

Hello, others solution!!!

let
#"StartDate" = #datetime(2001, 1, 1, 0, 0, 0),
#"Fonte" =
Table.SelectRows(
Value.NativeQuery(
Oracle.Database("Servidor"),
"select p.cd_empresa, p.cd_pessoa, p.cd_grupoempresa, p.dt_cadastro
from Empr p
where p.cd_empresa >= 1 "),
each [DT_CADASTRO] >= #"StartDate")
in
#"Fonte"