'Querying Hstore data in ms Access
I am using MS Access to get data from a postgresql source. It's my first time using postgresql and hstore although I have some other experience in T-SQL.
I need to track Hstore key 'Stage_id' value.
My code is as follows;
SELECT Log.action_tstamp_stm, Opp.opportunity_code,
Log.raw_data, Log.raw_data_old,
Log.changed_fields, Log.changed_fields_old
FROM public_ea_crm_opportunity AS Opp
INNER JOIN logs_logged_actions AS Log
ON Opp.id = Log.transaction_id
WHERE Log.table_name="ea_crm_opportunity" AND Log.action="U"
I now need to filter that code to extract the values, however when I change the SELECT part to e.g. Log.raw_data -> 'Stage_id' AS raw_stage, an error comes up saying
Syntax error (missing operator) in query expression 'Log.raw_data -> 'stage_id''
and the > character is highlighted in the query when I close that message. The same error comes up if I try to add AND Log.raw_data ? 'stage_id' to the filters, to return rows containing that key.
What am I doing wrong? It seems syntactically correct per my various google searches trying to learn this. Is it that Access can't deal with this data type? If so, is there a way I can convert the whole lot to text then use string functions to extract the key values from that?
(yes I know you may be aghast at the suggestion, but the system this is replacing did exactly that)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
