?
Solved

SUBSTR in WHERE clause SQl/DB2

Posted on 2011-09-13
5
Medium Priority
?
10,620 Views
Last Modified: 2012-05-12
Hi,
I am using SUBSTR in a WHERE clause in an SQL DB2.statement.
SYNTAX:

WHERE
substr(ldate,1,4) = substr(mdate,1,4)

I get the following error.
"SELECT - 0 row(s), 0.000 secs]  [Error Code: -440, SQL State: 42884]  DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SUBSTR;FUNCTION, DRIVER=3.57.82"

Is anyone familar with this?

Thanks in advance!



0
Comment
Question by:Mark1M
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 36534036
What is the data type of ldate & mdate?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36534188
just guessing, probably because substr doesn't work on dates.  Try casting/converting to a string before applying the substr function.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36534208
WHERE
substr(cast(ldate as varchar(26)),1,4) = substr(cast(mdate as varchar(26)),1,4)
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1000 total points
ID: 36535961
Hi Mark,

What's the data type of ldate and mdate?  If they are date and/or timestamp objects, you can use any of the built-in DB2 date functions to extract the portion of the data that you want.  

Since you're extracting 4 characters, I'll assume that you're comparing years.  In DB2, the equivalent SQL would be:

  WHERE year (ldate) = year (mdate)

And of course, if they are really strings, dqmq has posted a definitive answer.  :)


Good Luck,
Kent
0
 

Author Closing Comment

by:Mark1M
ID: 36536556
Kent & Everyone,
Thank you!
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

801 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