SQLState 42703 ERROR: Redshift

[thisiskl]

Hi folks, 

Has anyone had this problem? I'm using Redshift and "sell_in" column actually is not in the table. I did a rough search, and I don't think I used "sell_in" column in any of my measures, but I could be wrong. May I seek your help to fix it? 

DataSource.Error: ODBC: ERROR [42703] [Microsoft][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42703] ERROR: column "sell_in" does not exist in dm_imf_offline

Details:
DataSourceKind=AmazonRedshift

Many thanks!

[del-fra]

Hi @thisiskl,
I'm getting the same error and I know that the issue is because our field names use uppercase and spaces.
For example, we have the exchange_rates table

set enable_case_sensitive_identifier to true;
select
    "Currency",
    "Ratio To GBP",
    "Ratio To USD"
from fake_db.fake_schema.exchange_rates;

When querying it directly in the editor, I know I have to add the following statement to make it work.

set enable_case_sensitive_identifier to true;

When connecting from Tableau, I can add the statement in the Initial SQL, but in Power BI I can't find a solution as when I try to add it into the "SQL statement" box, it doesn't like it.

I'm sorry if this doesn't give a solution, but maybe it adds some more context and someone knows how to help.

[HarishKM]

@del-fra  Hey,
I believe that,you're experiencing in Power BI with case-sensitive field names is common when interacting with Redshift or similar databases that handle identifiers in a case-sensitive manner. Here are a couple of suggestions you might try:

1. Consider transforming column names in Power BI during the import process to standardize them. You can use Power Query Editor to rename columns to a consistent format (e.g., lowercase without spaces).
2.  Create a database view with aliases for columns that remove spaces and standardize the case. This way, you can query the view instead:

Sample SQL query for your reference

CREATE VIEW standardized_exchange_rates AS

   SELECT

       "Currency" AS currency,

       "Ratio To GBP" AS ratio_to_gbp,

       "Ratio To USD" AS ratio_to_usd

   FROM fake_db.fake_schema.exchange_rates;

a) Attempt to use direct query access in Power BI but adapt column names in the editor as an initial processing step.

b) Ensure data source credentials and configuration allow transformations or adjustments according to case sensitivity rules.

c) Unfortunately, Power BI doesn't support pre-execution SQL commands like SET enable_case_sensitive_identifier directly, but you can automate script execution through your DB environment if possible.

Thanks

Harish M
Kindly give Kudos and accept it as solution if its solves your problem