why is this query skipping on STRING_SPLIT

Question

I am trying to split one of the data on GLACCTNBR column into multiple columns but some record did not get split correctly and end up with a Null value.

WITH C AS(
      SELECT distinct  
      m.ACCTID
      ,m.ITEMDESC
      ,m.GLACCTNBR
      ,m.TOTAL
      ,m.DEPFILENBR
      ,m.DEPFILESEQ
      ,m.ITEMACCTID
      ,m.AMOUNT
      ,value 
      ,ROW_NUMBER() OVER(PARTITION BY m.GLACCTNBR ORDER BY (SELECT 1)) as rn
     FROM TG_ITEM_DATA m 
     inner join TG_TRAN_DATA t on m.DEPFILENBR = t.DEPFILENBR 
     AND m.DEPFILESEQ = t.DEPFILESEQ 
     AND m.EVENTNBR = t.EVENTNBR 
     AND m.TRANNBR = t.TRANNBR 
     AND t.VOIDDT IS NULL 
     AND NOT(t.ITEMIND='T') 
     AND m.GLACCTNBR IS NOT NULL 
     CROSS APPLY STRING_SPLIT(m.GLACCTNBR, '|') AS BK
     WHERE ( 

    (m.DEPFILENBR=2019261 AND m.DEPFILESEQ=1)) 
     AND m.ACCTID IS NOT NULL
)
SELECT distinct depfilenbr, depfileseq,acctid,GLACCTNBR,ITEMACCTID,AMOUNT,
      [1] AS PLUDEP
      ,[2] AS FUND
      ,[3] AS REVCD
      ,[4] AS SUBREVCD
      ,[5] AS BALSJT

FROM C
PIVOT(
    max(VALUE)
    FOR RN IN([1],[2],[3],[4],[5])  
) as PVT

GROUP BY DEPFILENBR,depfileseq,acctid,GLACCTNBR,ITEMACCTID,AMOUNT,[1],[2],[3],[4],[5]

depfilenbr  depfileseq  acctid  GLACCTNBR           ITEMACCTID  AMOUNT  PLUDEP  FUND    REVCD   SUBREVCD    BALSJT
2019261              1  5053    08|48R|3800|00|0000 50-0028      38.41      08   48R     3800         00      0000
2019261              1  5053    08|48R|3896|00|0000 50-001           0    NULL  NULL     NULL       NULL      NULL

To clarify: do you know the actual value of records for which are causing the NULL? If, for example, GLACCTNBR had greater or less than five items- that could cause a NULL. In other words, you say, "some record did not get split correctly" -- which ones? and what was the value for those? Try running just the query inside your CTE... — Alan Burstein, Sep 19 '19 at 23:49
In my answer you'll find a better approach for splitting with v2016+. Just to answer your question: This line `ROW_NUMBER() OVER(PARTITION BY m.GLACCTNBR ORDER BY (SELECT 1))` is a hack anyway, and it will return unexpectedly, if there is another `GLACCTNBR` with the same values in another row. You might use another partitioning condition (`PARTITION BY mACCTID,m.GLACCTNBR` ???) — Shnugo, Sep 20 '19 at 08:02
@AlanBurstein, I just found a simple way to use the [JSON-splitting in a type-safe way](https://stackoverflow.com/a/38275075/5089204) (UPDATE 2 section). Might be interesting for you... — Shnugo, Sep 20 '19 at 08:05

Shnugo · Answer 1 · 2019-09-20T08:04:54.770

It's always worth to mention, that delimited data should be avoided in any case. It is violating 1.NF and a real pain in the neck in queries. If there is a chance to change this issue on the input side, you should do this first.

Since you are using STRING_SPLIT() you must be on v2016+.

Much better than this weird STRING_SPLIT(), which is not returning the fragment's position and does not guarantee to return in the expected order, is a trick with JSON:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|part2|part3';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('["',REPLACE(@SomeDelimitedString,'|','","'),'"]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,JSON_VALUE(@JsonArray,'$[0]') AS TheFirstFragment
      ,JSON_VALUE(@JsonArray,'$[1]') AS TheSecondFragment
      ,JSON_VALUE(@JsonArray,'$[2]') AS TheThirdFragment

The result

part1|part2|part3   ["part1","part2","part3"]   part1   part2   part3

The idea in short:

We use some simple string-operations to transform the delimited string to a JSON-array. Now we can use JsonPath to grab the fragment by its position.

You might read this answer for more details (the UPDATE section for v2016+ and the UPDATE 2 section for type-safe results).

I cannot test this, but you can try something along this:

  SELECT   
   m.ACCTID
  ,m.ITEMDESC
  ,m.GLACCTNBR
  ,m.TOTAL
  ,m.DEPFILENBR
  ,m.DEPFILESEQ
  ,m.ITEMACCTID
  ,m.AMOUNT
  ,JSON_VALUE(JsonArray,'$[0]') AS PLUDEP 
  ,JSON_VALUE(JsonArray,'$[1]') AS FUND 
  ,JSON_VALUE(JsonArray,'$[2]') AS REVCD 
  ,JSON_VALUE(JsonArray,'$[3]') AS SUBREVCD 
  ,JSON_VALUE(JsonArray,'$[4]') AS BALSJT 
 FROM TG_ITEM_DATA m 
 inner join TG_TRAN_DATA t on m.DEPFILENBR = t.DEPFILENBR 
 AND m.DEPFILESEQ = t.DEPFILESEQ 
 AND m.EVENTNBR = t.EVENTNBR 
 AND m.TRANNBR = t.TRANNBR 
 AND t.VOIDDT IS NULL 
 AND NOT(t.ITEMIND='T') 
 AND m.GLACCTNBR IS NOT NULL 
 CROSS APPLY (SELECT CONCAT('["',REPLACE(m.GLACCTNBR,'|','","'),'"]')) BK(JsonArray)
 WHERE m.DEPFILENBR=2019261 
   AND m.DEPFILESEQ=1 
   AND m.ACCTID IS NOT NULL
--GROUP BY m.DEPFILENBR,m.depfileseq,m.acctid,m.GLACCTNBR,m.ITEMACCTID,m.AMOUNT,PLUDEP,FUND,REVCD,SUBREVCD,BALSJT

why is this query skipping on STRING_SPLIT

1 Answers1