Passing numeric parameter in Oracle custom SQL

[Anonymous]

Hi,

I want to pass a numeric parameter into my custom SQL like below

Select .....

where.....

and FRS.SNAPSHOT_DATE_KEY > "&_SnapshotDateParam_&"

....

I have the parameter _SnapshotDateParam_ set as 20190501

However I suspect it is trying to pass the SQL through as

Select .....

where.....

and FRS.SNAPSHOT_DATE_KEY> &20190501&

....

Due to the following error I get:

Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=select.......

.....

Right=20190705

I have tried several different permeations to get this working, to no avail!

Please help!

[v-shex-msft]

Hi @Anonymous ,

It seems like power query not allow you direct merge numeric value and text value, you can add Text.From function to force convert parameter value fix this.

let
   Source= Sql.Database("testDB", "test", [Query="Select * from Calendar where Datekey >"&Text.From(DatekeyPara)])
in 
   Source

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

Both are numeric though? 

It appeears that the paramter is somehow being transformed into text

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

Current you can only merge t-sql string with text value. For your requirement, it means you need to add some t-sql convert type functions to convert these text type parameters.

Regards,

Xiaoxin Sheng