cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

Firebird query fails

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"

2019-05-27 16_32_21-Window.png

 

How can I resolve this?
Where should I start?

 

Cheers!
A

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Firebird query fails

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!

 

View solution in original post

Highlighted
Community Support
Community Support

Re: Firebird query fails

Hi @AClerk 

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.

View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

Re: Firebird query fails

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!

 

View solution in original post

Highlighted
Community Support
Community Support

Re: Firebird query fails

Hi @AClerk 

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.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors