How to identify the exact line number or SQL code which is causing bad character issue - Error Code 2620

Database
Enthusiast

How to identify the exact line number or SQL code which is causing bad character issue - Error Code 2620

Hi All,

Is there any way to identify the exact line number or SQL code which is throwing "SELECT Failed. 2620:  The format or data contains a bad character." issue?

Most of the cases I used to run SQL queries with 200+ lines and I'm getting this issue due to bad data model. Its taking huge amount of time to identify the exact line number which is causing this issue and we are casting it.

So it will be very helpful if we know the line number of the issue to fix it.

2 REPLIES
Junior Contributor

Re: How to identify the exact line number or SQL code which is causing bad character issue - Error Code 2620

Hi Sakthi,

only the position of a syntax error is returned (when you connect using .NET).

But in your case it's a runtime error and then the actual column causing that is not included.

When you check Explain you can search for (FLOAT, FORMAT '-9.99999999999999E-999') as it's usually a VarChar compared/joined to a numeric value.

Enthusiast

Re: How to identify the exact line number or SQL code which is causing bad character issue - Error Code 2620

Got it Dieter! Thanks for the quick help!! 

 25) We do an all-AMPs JOIN step from Spool 34 (Last Use) by way of an

     all-rows scan, which is joined to Spool 35 (Last Use) by way of an

     all-rows scan.  Spool 34 and Spool 35 are left outer joined using

     a single partition hash join, with a join condition of (

     "(Incentive_Id (FLOAT, FORMAT '-9.99999999999999E-999'))=

     (Incentive_Id)").  The result goes into Spool 3 (all_amps)

     (compressed columns allowed), which is built locally on the AMPs.

     The size of Spool 3 is estimated with no confidence to be 8,078

     rows (3,158,498 bytes).  The estimated time for this step is 0.02

     seconds.