SQL Parser and Formatter issue

Toad for Oracle - Beta
1

Latest Beta.

Query1

with Q
    as
        (select T.*, floor((row_number() over (order by 0) - 1) / :BLOCK_ROWS_QT) GRP
           from (select rownum RID, S.*
                   from (  select --+ parallel(8)
                                  trunc(I.IMPDATE, 'dd') IMPDATE,
                                  I.EDRPOU,
                                  case
                                      when instr(I.EMAIL, '#') > 0 then substr(I.EMAIL, 1, instr(I.EMAIL, '#') - 1)
                                      else I.EMAIL
                                  end EMAIL,
                                  I.SENDERIP IP
                             from IMPFILESJRNL I
                            where     trunc(current_date, 'dd') - interval '1' day <= I.IMPDATE
                                  and I.IMPDATE < trunc(current_date, 'dd')
                         group by trunc(I.IMPDATE, 'dd'),
                                  I.EDRPOU,
                                  I.EMAIL,
                                  I.SENDERIP
                         order by EDRPOU, EMAIL, SENDERIP) S) T)
  select min(Q.RID) RID_MIN,
         max(Q.RID) RID_MAX,
         cast(min(Q.IMPDATE) as timestamp) MODTS_MIN,
         cast(max(Q.IMPDATE) as timestamp) MODTS_MAX,
         JSON_ARRAYAGG(JSON_OBJECT(Q.RID,
                                   Q.IMPDATE,
                                   Q.EDRPOU,
                                   Q.EMAIL,
                                   Q.IP
                                   returning clob)
                       order by RID
                       returning clob) JSON_DATA
    from Q
group by Q.GRP
order by Q.GRP;

Parsing & Formatting OK

Query 2

with Q
    as
        (select T.*, floor((row_number() over (order by 0) - 1) / :BLOCK_ROWS_QT) GRP
           from (select rownum RID, S.*
                   from (  select --+ parallel(8)
                                  trunc(I.IMPDATE, 'dd') IMPDATE,
                                  I.EDRPOU,
                                  case
                                      when instr(I.EMAIL, '#') > 0 then substr(I.EMAIL, 1, instr(I.EMAIL, '#') - 1)
                                      else I.EMAIL
                                  end EMAIL,
                                  I.SENDERIP IP
                             from IMPFILESJRNL I
                            where     trunc(current_date, 'dd') - interval '1' day <= I.IMPDATE
                                  and I.IMPDATE < trunc(current_date, 'dd')
                         group by trunc(I.IMPDATE, 'dd'),
                                  I.EDRPOU,
                                  I.EMAIL,
                                  I.SENDERIP
                         order by EDRPOU, EMAIL, SENDERIP) S) T)
  select min(Q.RID) RID_MIN,
         max(Q.RID) RID_MAX,
         cast(min(Q.IMPDATE) as timestamp) MODTS_MIN,
         cast(max(Q.IMPDATE) as timestamp) MODTS_MAX,
         JSON_ARRAYAGG(JSON_OBJECT(Q.*
                                   returning clob)
                       order by RID
                       returning clob) JSON_DATA
    from Q
group by Q.GRP
order by Q.GRP;

Parser displays an error at “Q.*“ position.
Formatter interrupts with errors

[Error] Syntax check (25: 76): ERROR: line 25, column 76, ending line 25, column 76: Found '*': Expecting: identifier  substitution_variable
[Error] Syntax check (25: 76): ERROR: line 25, column 76, ending line 25, column 76: Found '*': Not expecting this token or clause
[Error] Syntax check (25: 88): ERROR: line 25, column 88, ending line 25, column 91: Found 'clob': Expecting: IS  VALUE

But Query 2 works correctly.

2

thanks. I have reported it to the parser team.

1 Like