Forum Discussion
Value.NativeQuery()
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
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
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"