We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

simply SQL query against DB2 table

morciani
morciani asked
on
Medium Priority
660 Views
Last Modified: 2012-05-06
Hi All,

This is a simple SQL query that I am trying to run against a DB2 data table:

SELECT COUNT(*) AS SETUPS, YEAR (LAST_MOD_DT_TM) AS YEAR
FROM SCHEMA1.SETUP WHERE DDA_ID>''
AND DDA_ID LIKE ('%x%')
GROUP BY YEAR (LAST_MOD_DT_TM)  

This query returns no results:
                           
      SETUPS         YEAR  
 -----------  -----------  

There are definitely records in the table that match!

I can change a single character in the query (x to *), as follows:

SELECT COUNT(*) AS SETUPS, YEAR (LAST_MOD_DT_TM) AS YEAR
FROM SCHEMA1.SETUP WHERE DDA_ID>''
AND DDA_ID LIKE ('%*%')
GROUP BY YEAR (LAST_MOD_DT_TM)

The query returns the expected results:

                             
        SETUPS         YEAR  
   -----------  -----------  
            31         2007  
            65         2008  
                             
I'm just looking for records in the table that contain an 'x' in the DDA_ID field.  What gives!?!?!?!
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Hi Morciani,

DB2 doesn't do a case-insenstive comparison for values in a LIKE clause.  Rows containing 'x' (lower case) will be selected, but rows containing 'X' (upper case) won't be selected unless the row also contains a lower case 'x'.

Perhaps that's the issue.


Kent

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Dave FordSoftware Developer / Database Administrator
CERTIFIED EXPERT

Commented:

Since these kinds of things are case-sensitive, are you absolutely sure that a lowercase "x" exists in the data?

-- DaveSlash
Dave FordSoftware Developer / Database Administrator
CERTIFIED EXPERT

Commented:

Wow, the same answer in the same minute. That's impressive.

Author

Commented:
HI all- sorry about the delay in this comment.

I've tried the query using 'x' and 'X'..... still no records returned.  There are definitely records in the database that contain 'x' (lowercase).  My data ONLY includes records with lowercase x's.... no upper case X's exist.  
The data contains the following unique characters: 1,2,3,4,5,6,7,8,9,0,*,-,x.  I can run the query and get expected results with every character, except 'x'.  Crazy!
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Hi morciani,

I can't duplicate what you're seeing.  I've added a row with a lower case 'x' to an existing table and when I query the table, the row shows up just fine.

  insert into members (name) values ('foxes');
  select * from members where name like '%x%';

Just a pointer, but the filter on your query sems to have an unnecessary clause.  Unless there is binary data in the string, the test for DDA_ID > '' will always return true.

Is this column indexed?  If so, you might drop and recreate the index.


Kent

Author

Commented:
I don't have admin access to the database, so I will have to give up on this one.  Oh well- thank you for the help!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.