0

We are working on Oracle Database SQL Query.

DataBase Structure:
(etd_log_id, region,sla_status,escaltion_level,added_ts)

escalation_level field has:
1,2,3,4,5,6,7,8,9,10 
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9
1,2,3,4,5,6,7,8,
1,2,3,4,5,6,7,
1,2,3,4,5,6,
1,2,3,4,5,
1,2,3,4,
1,2,3
1,2
1

Requirement: We need to send alert based on level as a input, Lets say we get 6 as a input, we should get all these records :

1,2,3,4,5,6,
1,2,3,4,5,
1,2,3,4,
1,2,3
1,2
1

Java passes level as input in the where clause

`WHERE CAST(REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') AS NUMBER) <= 10<--Input from Java`

Approach: We developed a SQL query initially wherein we had this

CAST(REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') AS NUMBER) <= 6--<input from java>

But it threw an error as

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Upon RCA we came to know that REGEXP is treating the value into ASCII values. So we changed the condition by removing CAST as NUMBER from the CLAUSE.( WHERE (REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') <= ‘10’)Now 10 is treated as a character and comparison is made on the basis of ascii values.

The code failed in testing . I did RCA for the same and came to know that 1 and 10 have same ASCII value as ‘49’.

So here is the interesting part. 1 and 10 have ASCII value as 49. 2,3,4,5,6,7,8,9 have ASCII values as 50,51,52,53,…

When it reaches level 10, it shows output for escalation level 1 and 10 ( as we have WHERE (REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') < =‘10’) because it satisfies the condition <=49(ascii value) for level 1 as well as 10. Also, when it reaches 10, and then we provide any level between 2 and 9 we get level 10 also in the output.(obviously because now string has 10 in it with lower ASCII value to satisfy the condition)

What I want?

  1. Is there any way we can convert regexp and the level to same format? ( I tried with TO_NUMBER and CAST as NUMBER)
  2. Is there any other approach you would suggest to solve this issue?

Your help will be highly appreciated!

QUERY

SELECT CAST( REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') AS VARCHAR2(200)) "LEVEL",    
                              ETD_ALERT_LOG_ID ,REGION, REQUIRED_PERCENTAGE AS SLA_SET,  
----------------- THIS PART FETCHES START TIME FOR EMAIL ALERT---------------------------------------------------                             
                              CAST(TO_CHAR(    
                                  (FROM_TZ((SELECT ADDED_TS FROM (SELECT ADDED_TS, DENSE_RANK() OVER (PARTITION BY REGION ORDER BY ADDED_TS DESC) RNK    
                                                         FROM CHRT_SMTFOMGR.ETD_ALERT_LOG WHERE UPPER(REGION) = UPPER('Southern Ohio')    
                                                          AND ESCALATION_LEVEL ='1' AND SLA_STATUS = 'Missed SLA'     
                                                          AND ADDED_TS > (SELECT ADDED_TS FROM (SELECT ADDED_TS, DENSE_RANK() OVER (PARTITION BY REGION ORDER BY ADDED_TS DESC) RNK    
                                                          FROM CHRT_SMTFOMGR.ETD_ALERT_LOG WHERE UPPER(REGION) = UPPER(EAL.REGION)    
                                                           AND (SLA_STATUS = 'In SLA' OR IS_SEND_ALERT = 'N') )    
                                                        WHERE RNK = 1 AND ROWNUM = 1)    
                                                  AND TRUNC(FROM_TZ(ADDED_TS, (SELECT TZ_OFFSET(TO_CHAR(SYSTIMESTAMP,'TZR')) FROM DUAL ))    
                                                  AT TIME ZONE (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                                  WHERE UPPER(EAC.REGION) = UPPER(EAL.REGION) AND ROWNUM=1))=    
                                      TRUNC(SYSTIMESTAMP AT TIME ZONE (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG    
                                                                              WHERE UPPER(REGION) = UPPER(EAL.REGION) AND ROWNUM=1)))    
                                                  WHERE RNK = 1 AND ROWNUM = 1), (SELECT TZ_OFFSET(TO_CHAR(SYSTIMESTAMP,'TZR')) FROM DUAL ))    
                                                  AT TIME ZONE (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                                  WHERE UPPER(EAC.REGION)= UPPER(EAL.REGION) AND ROWNUM=1))    
                                  - NUMTODSINTERVAL((SELECT VALUE FROM CHRT_SMTFOMGR.APP_PROPERTY    
                                                                          WHERE UPPER(NAME) = 'ETD_ALERT_ESCALATION_INTERVAL'),'MINUTE')    
                                  ,'HH:MI AM') AS VARCHAR2(10)) ||' '|| (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                                  WHERE UPPER(EAC.REGION)= UPPER(EAL.REGION) AND ROWNUM=1) AS ALERT_START_TIME,  
----------------------END OF START TIME **WORKS FINE**-------------------------
------------------FETCHES END TIME FOR EMAIL TEMPLATE-------------------------------------                                            
                                  CAST(TO_CHAR(    
                                  (FROM_TZ(ADDED_TS, (SELECT TZ_OFFSET(TO_CHAR(SYSTIMESTAMP,'TZR')) FROM DUAL ))    
                                                  AT TIME ZONE (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                                  WHERE UPPER(EAC.REGION)= UPPER(EAL.REGION) AND ROWNUM=1))    
                                  ,'HH:MI AM') AS VARCHAR2(10)) ||' '|| (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                                  WHERE UPPER(EAC.REGION)= UPPER(EAL.REGION) AND ROWNUM=1)                        
                              AS ALERT_END_TIME,  
-------------------------END TIME **WORKS FINE----------------------------------------------------                            
                              TOTAL_ETD, (TOTAL_ETD - TOTAL_MET_SLA) AS UCM_SLA_MISSED_ETD, CURRENT_PERCENTAGE AS ACTUAL_SLA    
                    FROM CHRT_SMTFOMGR.ETD_ALERT_LOG EAL 
---------------------WHERE CLAUSE MENTIONED IN THE MAIL--------------------------------------------                 
                        WHERE CAST(REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') AS NUMBER) <= 10
                                                                                    
----------------------------------------------------------------------------------------------------
--------------PART TO FILTER REGION AND CHECK IF THE ALERT IS ON FOR SENDING EMAIL----
                        AND UPPER(EAL.REGION) = UPPER('Southern Ohio') AND IS_SEND_ALERT = 'Y'  

-----------------PART TO CONVERT THE TIME ZONE INTO REGION BASED TIMEZONE SO THAT ALERT IS SENT ONLY DURING CONFIGURED START AND END TIME-------------------------------------------                        
                        AND TRUNC(FROM_TZ(ADDED_TS, (SELECT TZ_OFFSET(TO_CHAR(SYSTIMESTAMP,'TZR')) FROM DUAL ))    
                                            AT TIME ZONE (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                            WHERE UPPER(EAC.REGION) = UPPER(EAL.REGION) AND ROWNUM=1))=    
                              TRUNC(SYSTIMESTAMP AT TIME ZONE (SELECT TIMEZONE FROM CHRT_SMTFOMGR.ETD_ALERT_CONFIG EAC    
                                                                              WHERE UPPER(EAC.REGION) = UPPER(EAL.REGION) AND ROWNUM=1)) 
---PART TO CHECK IF THE TICKETS MEET SLA OR THE ALERT WAS DISABLED IN BETWEEN THE RECORDS THAT ARE TO BE FETCHED SHOULD BE AFTER THAT 'In SLA' RECORD-----------------------------------------------------------------------                                                                          
                        AND ADDED_TS > (SELECT ADDED_TS FROM (SELECT ADDED_TS, DENSE_RANK() OVER (PARTITION BY REGION ORDER BY ADDED_TS DESC) RNK    
                                                                      FROM CHRT_SMTFOMGR.ETD_ALERT_LOG WHERE UPPER(REGION) = UPPER(EAL.REGION)    
                                                                      AND (SLA_STATUS = 'In SLA' OR IS_SEND_ALERT = 'N') )    
                                                  WHERE RNK = 1 AND ROWNUM = 1)    
                          ORDER BY ADDED_TS DESC;
astentx
  • 6,393
  • 2
  • 16
  • 25
Akash
  • 1
  • Why store _all_ of the values `1,2,3,4,5,6,7,8,9,10` in your string and use regexp to strip off the last one? Since they appear to always be all-inclusive (you never have `1,2,4,5`, for example), why not simply store the highest one as a number in the first place (e.g. just `10`)? Then your entire conversion problem goes away... – pmdba Sep 17 '21 at 04:02
  • 1
    10 does not have an ascii value of 49, it is the '1' in '10' that has that. And using regexp_substr as you provided on the strings you show does not raise that error. It sometimes returns NULL (when you have ',' at the end of the string) or a number. So, are you sure that you don't have strings in this column that do not end with a number, or that the error does not comes from elsewhere in the query? – gsalem Sep 17 '21 at 06:27
  • To compare integers as integers in text format, they should be right aligned: `lpad(num, 20, ' ')`. But as was said earlier, you do not need all the values between 1 and 10 as they can be calculated on-the-fly by having 10 only – astentx Sep 17 '21 at 07:05
  • `regexp_substr` is not treating anything as an ASCII value, whatever that means. It just returns character strings. Your regex will return null where the string ends in `,` or a space if it ends in `, `. Perhaps this explains the error you are seeing . – William Robertson Sep 17 '21 at 08:14

1 Answers1

0

You do not need (slow) regular expressions and can do it with simple string functions:

SELECT *
FROM   table_name
WHERE  TO_NUMBER(
         RTRIM(
           SUBSTR(escalation_level, INSTR(escalation_level, ',', -2) + 1),
           ','
         )
       ) <= 6;

Which, for the sample data:

CREATE TABLE table_name ( escalation_level ) AS
SELECT '1,2,3,4,5,6,7,8,9,10 ' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6,7,8,9,10' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6,7,8,9,10' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6,7,8,9' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6,7,8,' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6,7,' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6,' FROM DUAL UNION ALL
SELECT '1,2,3,4,5,' FROM DUAL UNION ALL
SELECT '1,2,3,4,' FROM DUAL UNION ALL
SELECT '1,2,3' FROM DUAL UNION ALL
SELECT '1,2' FROM DUAL UNION ALL
SELECT '1' FROM DUAL

Outputs:

ESCALATION_LEVEL
1,2,3,4,5,6,
1,2,3,4,5,
1,2,3,4,
1,2,3
1,2
1

db<>fiddle here

Additionally:

(SELECT TZ_OFFSET(TO_CHAR(SYSTIMESTAMP,'TZR')) FROM DUAL)

Does not need the sub-query and you can just use:

TZ_OFFSET(TO_CHAR(SYSTIMESTAMP,'TZR'))

and

SELECT ADDED_TS FROM (
  SELECT ADDED_TS,
         DENSE_RANK() OVER (PARTITION BY REGION
                            ORDER BY ADDED_TS DESC) RNK
  FROM   CHRT_SMTFOMGR.ETD_ALERT_LOG
  WHERE UPPER(REGION) = UPPER(EAL.REGION)    
  AND   (SLA_STATUS = 'In SLA' OR IS_SEND_ALERT = 'N')
)    
WHERE RNK = 1 AND ROWNUM = 1

When there are two REGION values that have identical characters but differing cases then the inner query will return multiple rows where rnk = 1 and it may be random which is matched by ROWNUM = 1 and it may not be the one with the latest date.

db<>fiddle here

You can write it much simpler using ROW_NUMBER and without the PARTITION BY clause (since that is already handled in the WHERE filter and I am assuming case does not matter):

SELECT ADDED_TS FROM (
  SELECT ADDED_TS,
         ROW_NUMBER() OVER (ORDER BY ADDED_TS DESC) RN
  FROM   CHRT_SMTFOMGR.ETD_ALERT_LOG
  WHERE  UPPER(REGION) = UPPER(EAL.REGION)
  AND    (SLA_STATUS = 'In SLA' OR IS_SEND_ALERT = 'N')
)    
WHERE RN = 1

However, you can probably write it without all the nested sub-queries if you use conditional aggregation in a windowed analytic function but your query is huge, badly-formatted and difficult to understand and I'll leave that to you to solve if you want to.

MT0
  • 143,790
  • 11
  • 59
  • 117