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
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.

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

3 Replies

  • Smauro's avatar
    Smauro
    Solution 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"