1

I have a table which stores SQL queries in string column like below. (I have given 3 sample rows below) 

select sql_query_part_1_tx  from Table1;

sql_query_part_1_tx
--------------------------------------------------------
SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1  AND HIRHCC04<>'US' AND HIRHCE01 IN ('TDCD','TDOA','IRATD') AND HIREG039 IN ((660),(661),(604)) AND HIREG234 <> 345 ;
SELECT SUM(BAL_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND HIRHCC04<>'US' AND HIRHCN17='FDIC' AND HIFD1516<>'Y' AND HIFD1527='FDIC';
SELECT SUM(OUTSTANDING_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND HIRHCC04<>'US' AND HIRHCN17='FDIC' AND HIFD1516='Y';

So the column sql_query_part_1_tx stores SQL queries in string format. my requirement is to read these queries and execute them dynamically and stores the results in another table.

Here the challenge in the above stored queries is wherever there is <> (not equal to) condition, it is not fetching rows with NULL values (which is expected). But I need rows with NULL values also.

For ex : 

SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1  AND HIRHCC04<>'US' AND HIRHCE01 IN ('TDCD','TDOA','IRATD') AND HIREG039 IN ((660),(661),(604)) AND HIREG234 <> 345 ;

In the above query string, there is condition HIRHCC04<>'US'. So it will not fetch records with HIRHCC04 IS NULL.

I want to change the above query string like below, apply NVL function wherever I find <> in the condition . 

SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1  AND NVL(HIRHCC04,'###')<>'US' AND HIRHCE01 IN ('TDCD','TDOA','IRATD') AND HIREG039 IN ((660),(661),(604)) AND NVL(HIREG234,999999) <> 345 ;

So that I can get rows with NULL values for 'HIRHCC04' and 'HIREG234' also.

So I want to update my Table which stores these query strings with NVL or COALESCE option. I hope I am clear.

NOTE : Please note that we need to consider datatype and "NOT IN" condition.

Improve this question
1
  • This is a bit tricky. You have both strings and numbers that need to be replaced. Commented Mar 4, 2019 at 22:34

2 Answers 2

Reset to default
1

The following query should do the trick:

SELECT REGEXP_REPLACE(sql_query_part_1_tx, '(\w+)\s*<>', 'COALESCE(\1, 9999) <>') 
FROM mytable;

Regexp explanation:

  • '(\w+)\s*<>' captures words (ie consecutive alphanumeric and underscore characters) at the left of the <> sign, with an optional sequence of spaces in between
  • 'COALESCE(\1, 9999) <>' : replaces the matched string, with the captured word (column name) referred to as '\ 1'

This demo on DB fiddle with your sample data returns:

| REGEXP_REPLACE(SQL_QUERY_PART_1_TX,'(\W+)\S*<>','COALESCE(\1,9999)<>')                                                                                                                              |
| :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1  AND COALESCE(HIRHCC04, 9999)<>'US' AND HIRHCE01 IN ('TDCD','TDOA','IRATD') AND HIREG039 IN ((660),(661),(604)) AND COALESCE(HIREG234, 9999)<> 345 ; |
| SELECT SUM(BAL_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND COALESCE(HIRHCC04, 9999)<>'US' AND HIRHCN17='FDIC' AND COALESCE(HIFD1516, 9999)<>'Y' AND HIFD1527='FDIC';                      |
| SELECT SUM(OUTSTANDING_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND COALESCE(HIRHCC04, 9999)<>'US' AND HIRHCN17='FDIC' AND HIFD1516='Y';                                                   |

Incidently, let me suggest an optimization to your replacement logic.

This:

COALESCE(HIRHCC04, 9999)<>'US'

Is usually better spelled:

(HIRHCC04 IS NULL OR HIRHCC04 <> 'US')

The second expression is more explicit and more efficient, as it will happily use an existing index on the column being filtered (while the first expression will not). It also avoids potential conversion issues when the column being checked is not a number.

These expressions could be generated with the following regex:

SELECT REGEXP_REPLACE(
    sql_query_part_1_tx, 
    '(\w+)\s*<>\s*''(\w+)''', 
    '(\1 IS NULL OR \1 <> ''\2'')'
) 
FROM mytable;

Demo on DB Fiddle:

| REGEXP_REPLACE(SQL_QUERY_PART_1_TX,'(\W+)\S*<>\S*''(\W+)''','(\1ISNULLOR\1<>''\2'')')                                                                                                          |
| :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1  AND (HIRHCC04 IS NULL OR HIRHCC04 <> 'US') AND HIRHCE01 IN ('TDCD','TDOA','IRATD') AND HIREG039 IN ((660),(661),(604)) AND HIREG234 <> 345 ;   |
| SELECT SUM(BAL_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND (HIRHCC04 IS NULL OR HIRHCC04 <> 'US') AND HIRHCN17='FDIC' AND (HIFD1516 IS NULL OR HIFD1516 <> 'Y') AND HIFD1527='FDIC'; |
| SELECT SUM(OUTSTANDING_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND (HIRHCC04 IS NULL OR HIRHCC04 <> 'US') AND HIRHCN17='FDIC' AND HIFD1516='Y';                                      |

UPDATE

Here is an updated query that will also process NOT IN clauses, and allow both numbers and strings on the left side of the <> operator (and in NOT IN lists):

SELECT REGEXP_REPLACE(
    REGEXP_REPLACE(sql_query_part_1_tx, '(\w+)\s*<>\s*(''*\w+''*)', '(\1 IS NULL OR \1 <> \2)'), 
    '(\w+)\s*NOT IN\s*(\([^)]+\))', 
    '(\1 IS NULL OR \1 NOT IN \2)'
) FROM mytable;

Demo on DB Fiddle;

| REGEXP_REPLACE(REGEXP_REPLACE(SQL_QUERY_PART_1_TX,'(\W+)\S*<>\S*(''*\W+''*)','(\1ISNULLOR\1<>\2)'),'(\W+)\S*NOTIN\S*(\([^)]+\))','(\1ISNULLOR\1NOTIN\2)')                                                                                    |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1  AND (HIRHCC04 IS NULL OR HIRHCC04 <> 'US') AND (HIRHCE01 IS NULL OR HIRHCE01 NOT IN ('TDCD','TDOA','IRATD')) AND HIREG039 IN ((660),(661),(604)) AND (HIREG234 IS NULL OR HIREG234 <> 345) ; |
| SELECT SUM(BAL_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND (HIRHCC04 IS NULL OR HIRHCC04 <> 'US') AND HIRHCN17='FDIC' AND (HIFD1516 IS NULL OR HIFD1516 <> 'Y') AND HIFD1527='FDIC';                                               |
| SELECT SUM(OUTSTANDING_AM) FROM SCH.DEFD1520 WHERE 1=1  AND HIRHCV04='Y' AND (HIRHCC04 IS NULL OR HIRHCC04 <> 'US') AND HIRHCN17='FDIC' AND HIFD1516='Y';                                                                                    |
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

THANK YOU. can we extend the same for <> occuring more than once ? Unfortunately, I don't know how many times it might occur in the string. Secondly, can we extend this to NOT IN clause as well ? For EX : lets take the string below..... select 'SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1 AND HIRHCC04<>''US'' AND HIRHCE01 NOT IN (''TDCD'',''TDOA'',''IRATD'') AND HIREG039 NOT IN ((660),(661),(604)) AND HIREG234 <> 345 ;' as q from dual; Is it possible to split the expression you have used and explain ?
@Nag: I should have mentionned... REGEXP_REPLACE() treats all occurences by default, no issue there.
@Nag: for NOT IN, we would have to use another regexp. Try: REGEXP_REPLACE( sql_query_part_1_tx, '(\w+)\s*NOT IN\s*(\([^)]+\))', '(\1 IS NULL OR \1 NOT IN \2)'. This works in this db fiddle (I added a NOT IN condition to the first record).
COALESCE(HIRHCC04, 9999)<>'US' --> This is giving data type mismatch. So we need to check the right side of <>, whether it is string or number. Anyway, the second option is very good to split the condition with OR. If we can extend that for multiple occurances of <> and "NOT IN" clause, my problem is solved. I tried hard but breaking one or the other way. Please help.
@Nag : just run each UPDATE query one by one, and you will get the desired output, isn't that ok?
|
0

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION:

Here the challenge in the above stored queries is wherever there is <> (not equal to) condition, it is not fetching rows with NULL values (which is expected). But I need rows with NULL values also.

This is really nasty. I can't say that I really condone this. Your queries are not written consistently (in one place there are spaces around <> and in the other no spaces). I suspect that there might be a better solution to your overall problem than storing such raw SQL.

That said, I can appreciate that you have a problem. This is tricky, because you have both strings and numbers, which makes things tricky. You could have dates. The following will not work on dates. But, regexp_replace() can do something:

select regexp_replace(regexp_replace(q, ' ([^ <]+) ?<> ?''', ' NVL(\1, ''XXX'') <> '''), ' ([^ <)]+) ?<> ?[^'']', ' NVL(\1, -1) <> ')
from (select 'SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1 AND HIRHCC04<>''US'' AND HIRHCE01 IN (''TDCD'',''TDOA'',''IRATD'') AND HIREG039 IN ((660),(661),(604)) AND HIREG234 <> 345 ;' as q
     from dual) x

Note: This pretty much assumes that the reference before the <> is a single column reference, not a more complicated expression.

Improve this answer

1 Comment

Thank you. This covered <>. Can we extend this for 'NOT IN' as well (for both NUMBERS and STRINGS) ? example below select regexp_replace(regexp_replace(q, ' ([^ <]+) ?<> ?''', ' NVL(\1, ''XXX'') <> '''), ' ([^ <)]+) ?<> ?[^'']', ' NVL(\1, -99) <> ') from (select 'SELECT SUM(BAL_AM) FROM SCH.DERHCE03 WHERE 1=1 AND HIRHCC04<>''US'' AND HIRHCE01 NOT IN (''TDCD'',''TDOA'',''IRATD'') AND HIREG039 NOT IN ((660),(661),(604)) AND HIREG234 <> 345 ;' as q from dual) x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.