LEFT used in subquery-CodePudding

My problem is quite simple. The following query returns the error:

Mensagem 537, Level 16, state 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function..

Code:

Select  
  c.name "Nome Conta",
  c.code "Código Conta",
  sq1.[Nome Conta] Tipo

From
  (chart_tag ct inner join chart c on ct.id_chart = c.id_chart) inner join
        (Select  
            c1.name "Nome Conta",
            LEFT(c1.code, (charindex('00', c1.code)-1)) "Código Conta"

         from chart_tag ct1 inner join chart c1 on ct1.id_chart = c1.id_chart

         where ct1.id_tag = 18159 and
               c1.id_type_chart = 1942 and
               c1.only_accrual = 1 and 
               c1.code not in ('99', '98')) sq1 on LEFT(c.code, 1) = sq1.[Código Conta]

Where
  ct.id_tag = 18159 and
  c.id_type_chart = 1942 and
  c.only_accrual = 0

order by
  c.code

The first inner join returns the following table:

Name	Code
Caixa Geral	1111001
Caixa Departamentos/Operador	1111005
Valores Recebidos a Depositar	1111025
Bancos Conta Movimento	1112001
Bancos Conta Movimento - Vincul	1112005
Bancos Conta Subvenções	1112021
Bancos Conta Doações	1112022
Bancos Conta Contribuições	1112023
Aplicações Financeiras Imediata	1113001
Aplicações Financeiras Vinc.	1113005

Executing the From subquery separately no errors are returned. The following table is generated:

Name	code
ATIVO	1
ATIVO CIRCULANTE	11
CAIXA E EQUIVALENTES	111
CAIXA	1111
BANCOS	1112
APLICAÇÕES FINANCEIRAS	1113

From

Name	code
ATIVO	1000000
ATIVO CIRCULANTE	1100000
CAIXA E EQUIVALENTES	1110000
CAIXA	1111000
BANCOS	1112000
APLICAÇÕES FINANCEIRAS	1113000

Subquery:

Select  
  c1.name "Nome Conta",
  LEFT(c1.code, (charindex('00', c1.code)-1)) "Código Conta"
    
from
  chart_tag ct1 inner join chart c1 on ct1.id_chart = c1.id_chart
    
where
  ct1.id_tag = 18159 and
  c1.id_type_chart = 1942 and
  c1.only_accrual = 1 and 
  c1.code not in ('99', '98')

Changing the main logic, which is to remove the '0' to the right of the "code" column, to use replace the query is executed normally and returning the expected result, except for some lines that deviate from the pattern, containing '0' in the middle of the string , and not just right. Because of this, I thought I'd use the current logic, which is returning the error. Knowing this, the LEFT to which the error refers is that of the subquery, however, as already mentioned, as it is an isolated subquery, which does not depend on external factors, I do not understand the reason for this error.

CodePudding user response：

By the way, if you're simply trying to remove the trailing 0 from the number, have you considered:

REPLACE((RTRIM(REPLACE(c1.code, '0', ' ')),' ', '0')

Change all the 0 to spaces, TRIM, and change residual spaces back..

There'll be more ways to skin the cat;

REVERSE(CAST(REVERSE(c1.code) as INT))

(but reverse is generally regarded as a bit nasty/slow) etc..

CodePudding user response：

So, here's how to debug this:

When you don't use LEFT, you don't get the error
The error message complains that the length passed to LEFT is invalid
The problem is with the LEFT function
The length is passed as the second argument

Replace the c1.code with some value:

Select  
  LEFT('100', (charindex('00', '100')-1)) "Código Conta"

It works, gives you 1

But.. What if the string you seek isn't found in the value?

Select  
  LEFT('199', (charindex('00', '199')-1)) "Código Conta"

"Invalid length..." error

So what length are we passing? Extract just the length calc:

Select  
  charindex('00', '199')-1)
  --LEFT('199', (charindex('00', '199')-1)) "Código Conta"

It gives -1, and -1 isn't a valid number of chars to remove from the left of something...

So what do you want to do when the needle isn't found in the haystack? Perhaps take the whole string with no substringing? Or maybe adjust the WHERE clause to only allow codes that end with a 00, so the seek always works?

You decide.. This will give you the whole thing if '00' isn't found:

Select  
  LEFT(
    c1.code, 
    COALESCE(
      NULLIF(
        charindex('00', c1.code)-1,
        -1
      ),
      LEN(c1.Code)
    )
  ) "Código Conta"

If the seek returns -1 meaning "asn't found", NULLIF converts the -1 to null, and COALESCE then converts the null to the LEN of the string, i.e. LEFT returns the whole thing

This will give empty string if the string isn't found; the only difference is use use of 0 in the coalesce - it asks LEFT to give the leftmost 0 chars:

Select  
  LEFT(
    c1.code, 
    COALESCE(
      NULLIF(
        charindex('00', c1.code)-1,
        -1
      ),
      0
    )
  ) "Código Conta"

Or, as mentioned, use a WHERE c1.code LIKE '%' to ensure youre only looking at codes that really do have 00 in them

CodePudding user response：

If it does not have the substring '00'

LEFT(c1.code, (charindex('00', c1.code)-1))

Will give an error since it return 0 and -1 is not valid

This will work:

CASE WHEN charindex('00', c1.code) > 0 
     THEN LEFT(c1.code, (charindex('00', c1.code)-1))
     ELSE c1.code
END

This will also work:

LEFT(c1.code '00', (charindex('00', c1.code '00')-1))

I believe the 2nd one will be faster but it may not be based on your data profile so if performance really matters test both.