PS Query & SQL

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Try using:

SELECT 
  A.qryname, 
  C.expressiontext 
FROM 
  psqrydefn A, 
  psqryfield B, 
  psqryexpr C 
WHERE 
  (
    A.oprid = B.oprid 
    AND A.qryname = B.qryname 
    AND B.oprid = C.oprid 
    AND B.qryname = C.qryname 
    AND B.recname = 'ACCOUNT_SF' 
    AND B.fieldname = 'ACCOUNT_BALANCE' 
    AND CASE WHEN Regexp_like(
      dbms_lob.Substr(C.expressiontext, 4000, 1), 
      '(^|[^0-9])200([^0-9]|$)', 
      'c'
    ) THEN 1 ELSE 0 END = 1
  )

The expression is:

CASE 
WHEN REGEXP_LIKE(DBMS_LOB.SUBSTR(C.EXPRESSIONTEXT, 4000, 1), '(^|[^0-9])200([^0-9]|$)', 'c') THEN 1 
ELSE 0 
END

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Try using:

SELECT 
  A.qryname, 
  C.expressiontext 
FROM 
  psqrydefn A, 
  psqryfield B, 
  psqryexpr C 
WHERE 
  (
    A.oprid = B.oprid 
    AND A.qryname = B.qryname 
    AND B.oprid = C.oprid 
    AND B.qryname = C.qryname 
    AND B.recname = 'ACCOUNT_SF' 
    AND B.fieldname = 'ACCOUNT_BALANCE' 
    AND CASE WHEN Regexp_like(
      dbms_lob.Substr(C.expressiontext, 4000, 1), 
      '(^|[^0-9])200([^0-9]|$)', 
      'c'
    ) THEN 1 ELSE 0 END = 1
  )

The expression is:

CASE 
WHEN REGEXP_LIKE(DBMS_LOB.SUBSTR(C.EXPRESSIONTEXT, 4000, 1), '(^|[^0-9])200([^0-9]|$)', 'c') THEN 1 
ELSE 0 
END

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Try using:

SELECT 
  A.qryname, 
  C.expressiontext 
FROM 
  psqrydefn A, 
  psqryfield B, 
  psqryexpr C 
WHERE 
  (
    A.oprid = B.oprid 
    AND A.qryname = B.qryname 
    AND B.oprid = C.oprid 
    AND B.qryname = C.qryname 
    AND B.recname = 'ACCOUNT_SF' 
    AND B.fieldname = 'ACCOUNT_BALANCE' 
    AND CASE WHEN Regexp_like(
      dbms_lob.Substr(C.expressiontext, 4000, 1), 
      '(^|[^0-9])200([^0-9]|$)', 
      'c'
    ) THEN 1 ELSE 0 END = 1
  )

The expression is:

CASE 
WHEN REGEXP_LIKE(DBMS_LOB.SUBSTR(C.EXPRESSIONTEXT, 4000, 1), '(^|[^0-9])200([^0-9]|$)', 'c') THEN 1 
ELSE 0 
END

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' ) 

Try using:

SELECT 
  A.qryname, 
  C.expressiontext 
FROM 
  psqrydefn A, 
  psqryfield B, 
  psqryexpr C 
WHERE 
  (
    A.oprid = B.oprid 
    AND A.qryname = B.qryname 
    AND B.oprid = C.oprid 
    AND B.qryname = C.qryname 
    AND B.recname = 'ACCOUNT_SF' 
    AND B.fieldname = 'ACCOUNT_BALANCE' 
    AND CASE WHEN Regexp_like(
      dbms_lob.Substr(C.expressiontext, 4000, 1), 
      '(^|[^0-9])200([^0-9]|$)', 
      'c'
    ) THEN 1 ELSE 0 END = 1
  )

The expression is:

CASE 
WHEN REGEXP_LIKE(DBMS_LOB.SUBSTR(C.EXPRESSIONTEXT, 4000, 1), '(^|[^0-9])200([^0-9]|$)', 'c') THEN 1 
ELSE 0 
END

Hi Dana,

When using long text fields in criteria I usually do not use a SUBSTR to make sure I am searching all of the text, see below.  Depending on how many queries you are looking through and how your expressions are setup you might want to include the query field Select Number and Field Number in case it is in the experssion text in the fun PS query notation of like :%2.15.

SELECT A.QRYNAME, TO_CHAR(SUBSTR(TRIM( C.EXPRESSIONTEXT),1,2048)), B.SELNUM, B.FLDNUM
  FROM PSQRYDEFN A, PSQRYFIELD B, PSQRYEXPR C
  WHERE ( A.OPRID = B.OPRID
     AND A.QRYNAME = B.QRYNAME
     AND A.OPRID = C.OPRID
     AND A.QRYNAME = C.QRYNAME
     AND B.RECNAME = 'ACCOUNT_SF'
     AND B.FIELDNAME = 'ACCOUNT_BALANCE'
     AND CASE WHEN  C.EXPRESSIONTEXT like '%200%' THEN 'Y' ELSE 'N' END = 'Y')

Okay, folks.  We are looking for queries that have criteria or having criteria looking for ACCOUNT_SF.ACCOUNT_BALANCE specifically looking for any logic that has 200 in it.  They need to change that to 500.

So, I've come close, but I'm missing something.  This query has this logic:

And it's not coming back in my query results.

This is my query.  Can you guys help me fix it?  Please and thank you.

SELECT DISTINCT A.qryname,
                dbms_lob.Substr(Trim(expressiontext), 3, 1)
FROM   psqrydefn A,
       psqryfield B,
       psqryexpr C
WHERE  ( A.oprid = B.oprid
         AND A.qryname = B.qryname
         AND B.oprid = C.oprid
         AND B.qryname = C.qryname
         AND B.recname = 'ACCOUNT_SF'
         AND dbms_lob.Substr(Trim(expressiontext), 3, 1) LIKE '%200%'
         AND B.recname = 'ACCOUNT_SF'
         AND B.fieldname = 'ACCOUNT_BALANCE' )