Firebird query fails

[Anonymous]

Hey All,

I have a long and ugly query.

When I run it directly on the Firebird DB, it works well and I am getting the relevant expected results.

When I use it in Power BI, I am getting an error:

Details: "ERROR [HY000] [ODBC Firebird Driver][Firebird]arithmetic exception, numeric overflow, or string truncation
string right truncation"

How can I resolve this?
Where should I start?

Cheers!
A

[Anonymous · Accepted solution]

I found the problematic part.

I have a few lines of 'CASE'.

case when
field1 is not null then cast(field1 as varchar(11)) else null end field1,
case when
field2 is not null then cast(field2 as varchar(11)) else null end field2,
etc...

I removed the CAST function and then Power BI started to work as expected and retreive the data

case when
field1 is not null then field1 else null end field1,
case when
field2 is not null then field2 else null end field2,
etc...

Hope it wont cause issues in the future.

Any idea why it is not working with 'CAST'?

Cheers!

[Anonymous · Accepted solution]

I found the problematic part.

I have a few lines of 'CASE'.

case when
field1 is not null then cast(field1 as varchar(11)) else null end field1,
case when
field2 is not null then cast(field2 as varchar(11)) else null end field2,
etc...

I removed the CAST function and then Power BI started to work as expected and retreive the data

case when
field1 is not null then field1 else null end field1,
case when
field2 is not null then field2 else null end field2,
etc...

Hope it wont cause issues in the future.

Any idea why it is not working with 'CAST'?

Cheers!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

As searched, there may be several reasons as said in this document.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.