Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Pass parameter from worsheet cell to Oracle

  1. #1

    Pass parameter from worsheet cell to Oracle



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Current code:
    Code:
    let
    SDate = Date.ToText(Date.From(fnGetParameter("SDate")),"mm/dd/yyyy"),
    varQuery = "select s.location_id,s.POS_date, s.pos_file_name, l.location_code,l.location_name from t_inv_hdr_pos s join t_location l on s.location_id = l.location_id where s.POS_date > to_date('" & SDate & "','mm/dd/yyyy')",
     Source = Oracle.Database("BRIAD.DB.CT", [Query=varQuery]),
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
     in
     #"Changed Type"


    Error:
    DataSource.Error: Oracle: ORA-01843: not a valid month

    Details:
    DataSourceKind=Oracle
    DataSourcePath=briad.db.ct
    Message=ORA-01843: not a valid month
    ErrorCode=-2146232008

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,319
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Can you select the varQuery step, and copy the contents of the result here? I'm curious to see how it compares to the original set you mentioned on the blog:

    Code:
    "select s.location_id,s.POS_date, s.pos_file_name,
    l.location_code,l.location_name
    from t_inv_hdr_pos s
    join t_location l on s.location_id = l.location_id
    where s.POS_date > to_date('03/21/2016','mm/dd/yyyy')"
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    The exact code is listed above that is currently in the Power Query.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,319
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    So when you click on the varQuery step on the right side in the Applied Steps area, the window on the left shows EXACTLY this?

    Code:
    select s.location_id,s.POS_date, s.pos_file_name,
    l.location_code,l.location_name
    from t_inv_hdr_pos s
    join t_location l on s.location_id = l.location_id
    where s.POS_date > to_date('03/21/2016','mm/dd/yyyy')
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Sorry, I misunderstood what you were asking me to do.

    select s.location_id,s.POS_date, s.pos_file_name, l.location_code,l.location_name from t_inv_hdr_pos s join t_location l on s.location_id = l.location_id where s.POS_date > to_date('00/01/2016','mm/dd/yyyy')

  6. #6
    select s.location_id,s.POS_date, s.pos_file_name, l.location_code,l.location_name from t_inv_hdr_pos s join t_location l on s.location_id = l.location_id where s.POS_date > to_date('00/01/2016','mm/dd/yyyy')


    Date in te parameter table is 04/0/2016

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,319
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    That's the issue right there then. 4/0/2016 is not a valid date as there is no month or day 0 in a date. (Unless you typo'd that.)

    Out of curiosity... what country are you in, and how do you read the following date? 04-05-2016 i.e. is that Apr 5 to you, or May 4? (Or more importantly... how does your computer see that?)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    I am in the United States, system reads the date as April 1, 2016

  9. #9
    With the code:
    let
    SDate = Date.ToText(fnGetParameter("SDate"),"mm/dd/yyyy"),

    I get the following error when I click on the SDate in the applied steps:

    Expression.Error: We cannot convert the value #datetime(2016, 4, 1, 0, 0, 0) to type Date.
    Details:
    Value=04/01/2016 12:00:00 AM
    Type=Type

  10. #10
    Ken,
    Thank you for your help. Once you showed me about clicking on the Applied Steps, I was able to debug the date issue.

    Here is the line for setting the SDate value:

    SDate = Date.ToText(Date.From(fnGetParameter("SDate"))),

    Thanks Again for all your time!

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •