[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

simply SQL query against DB2 table

Posted on 2009-02-18
6
Medium Priority
?
650 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!?!?!?!
0
Comment
Question by:morciani
  • 2
  • 2
  • 2
6 Comments
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 2000 total points
ID: 23675295
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 23675304

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

-- DaveSlash
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 23675331

Wow, the same answer in the same minute. That's impressive.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:morciani
ID: 23695848
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
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 23695949
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
 

Author Closing Comment

by:morciani
ID: 31548496
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question