How can I evaluate the size for when it returns the value whatever I want?-CodePudding

I have this query in which I check with the NVL function if a field is null that it returns the other one and so on respectively, but I want to make sure that regardless of the data that it returns it is not greater than 10 and if it is, then that it cuts the string in that limit it does not matter what. But I get an "Invalid arguments" error and I don't know how to do it ...

SELECT NVL(
         CASE
         WHEN LENGTH(NA.NAME_FORMAL) > 10
         THEN SUBSTRB (NA.NAME_FORMAL,1,10) 
         END
       ),
       Length(BI.PAYERNAME)),
       BI.EMPLID
FROM   BI_REQADR_VW BI
       left JOIN FORMAL_NAMES NA
       ON BI.EMPLID = NA.EMPLID
WHERE  BI.EMPLID = '00000PROOF';

CodePudding user response：

The way you explained it, should be substr of NVL, not vice versa (as it is simpler) (apart from syntax error).

SELECT SUBSTR (NVL (na.name_formal, bi.payername), 1, 10), 
       bi.emplid
  FROM bi_reqadr_vw bi LEFT JOIN formal_names na ON bi.emplid = na.emplid
 WHERE bi.emplid = '00000PROOF';