Passing where condition from Power Query to PostgreSQL

[kuldeepjainesl]

I have a working solution in power query and wanted to add a dynamic where a clause passes from the power query itself. followed the approach shared at "https://powergi.net/2020/05/17/dynamic-sql-queries-with-excels-power-query/". Reached up to the last step where the source query step needs to modify for combined test string as where condition but at this step, the error is encountered as

"DataSource.Error: PostgreSQL: 42601: syntax error at or near "and"
Details:
DataSourceKind=PostgreSQL
DataSourcePath=XXXXXXXXX
Message=42601: syntax error at or near "and"
ErrorCode=-2147467259

my query which is unable to pass the string is
" Value.NativeQuery(PostgreSQL.Database("XXXXXXXXX"), "Select product_family9 , sr_date2 ,shipment_date_manufa28 ,diagnose_code1_descr16 ,diagnose_code2_descr18 from appuser.failure"&WhereClause)"

Whereclause is the combined string to be passed in the native query. Pls, help.

 

[Chihiro]

Without knowing what value is assigned to "WhereClause" variable when the error is thrown, hard to help you.
That error message is standard syntax error message from db side.

I suspect, there is invalid character or something else that's causing it, held in "WhereClause".

 

[kuldeepjainesl]

This is put in whereclause

"Where(product_family9 = 'Apex 100') and (diagnose_code1_descr16 = 'Nvm/Flash Corruption' or diagnose_code2_descr18 = 'Nvm/Flash Corruption')OR(product_family9 = 'Apex 150') and (diagnose_code1_descr16 = 'Nvm/Flash Corruption' or diagnose_code2_descr18 = 'Nvm/Flash Corruption')

To assist, if this where clause is tested with full expanded SQL, it works.

 

[Chihiro]

I'm not too familiar with PostgreSQL but shouldn't there be single space after "Where" and before "OR" ?

 

[kuldeepjainesl]

Aah, yes, it works now with added space, I was struggling since morning.

 

[AlanSidman]

crossposted: https://forums.excelguru.ca/threads...y-to-postgresql-stuck-in-the-last-step.11630/

 

[vletm]

kuldeepjainesl
Could You reread Forum Rules? Those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.