Forum Discussion

Captuleio's avatar
Captuleio
Regular Visitor
6 years ago
Solved

Value.NativeQuery()

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 Font...
Need Help
  • Smauro's avatar
    Smauro
    6 years ago

    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

Smauro's avatar
Smauro
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
    Captuleio
    Regular Visitor
    6 years ago

    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's avatar
      Captuleio
      Regular Visitor
      6 years ago

      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"