Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |