Pass parameter from worsheet cell to Oracle

[jbaisch]

Current 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

 

[Ken Puls]

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:

"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')"

 

[jbaisch]

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

 

[Ken Puls]

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?

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')

 

[jbaisch]

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')

 

[jbaisch]

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

 

[Ken Puls]

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?)

 

[jbaisch]

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

 

[jbaisch]

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

 

[jbaisch]

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!

 

[Ken Puls]

Excellent, glad you got it sorted!