Pass parameter from worsheet cell to Oracle

J

jbaisch

New member
Joined
Apr 7, 2016
Messages
8
Reaction score
0
Points
0
Location
Arizona, USA
Excel Version(s)
2010 and 2016
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
 
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')"
 
The exact code is listed above that is currently in the Power Query.
 
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')
 
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')
 
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
 
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?)
 
I am in the United States, system reads the date as April 1, 2016
 
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
 
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!
 
You must log in or register to reply here.
Back
Top