• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 653
  • Last Modified:

simply SQL query against DB2 table

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!?!?!?!
0
morciani
Asked:
morciani
  • 2
  • 2
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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

-- DaveSlash
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Wow, the same answer in the same minute. That's impressive.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
morcianiAuthor 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!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
morcianiAuthor 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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now