By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,668 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

locate and select

P: n/a
how can I use a function inside a function, for example I want to
select col1, col2 from table1 where locate(dayofweek(current
date),col3) =dayofweek(current date)
col3 is a field that contains a string like '12 4567', or
'1 4567'. This field specifies what day in the week the job executes,
and if there is a match, I want to report col1 and col2.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
hi****@gmail.com wrote:
how can I use a function inside a function, for example I want to
select col1, col2 from table1 where locate(dayofweek(current
date),col3) =dayofweek(current date)
The things you have to keep in mind are the data types and the results
returned by the various functions. They don't match in your example.

DAYOFWEEK returns an INTEGER. LOCATE takes 2 (or 3) strings (VARCHAR, CHAR)
values as input parameters. So you have to convert the INTEGER to a
string:

LOCATE(RTRIM(CHAR(DAYOFWEEK(CURRENT DATE))), col3)

The RTRIM is necessary because the CHAR function pads the string to the
right with spaces, which you don't want LOCATE to search for, I guess.

Now, the result of LOCATE is the position of the first occurrence of the
search string found in "col3". This is again an INTEGER. I don't know if
you really want to compare that position with the day of the week. Given
your examples below, I think you do.
col3 is a field that contains a string like '12 4567', or
'1 4567'. This field specifies what day in the week the job executes,
and if there is a match, I want to report col1 and col2.


With these examples, it would be even easier to accomplish your goal: just
see if the position for the current day-of-week is empty (a space) or not.

SUBSTR(col3, DAYOFWEEK(CURRENT DATE), 1) = ' '

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena
Nov 12 '05 #2

P: n/a
Thanks Knut, it worked.

Knut Stolze wrote:
hi****@gmail.com wrote:
how can I use a function inside a function, for example I want to
select col1, col2 from table1 where locate(dayofweek(current
date),col3) =dayofweek(current date)


The things you have to keep in mind are the data types and the results
returned by the various functions. They don't match in your example.

DAYOFWEEK returns an INTEGER. LOCATE takes 2 (or 3) strings (VARCHAR, CHAR)
values as input parameters. So you have to convert the INTEGER to a
string:

LOCATE(RTRIM(CHAR(DAYOFWEEK(CURRENT DATE))), col3)

The RTRIM is necessary because the CHAR function pads the string to the
right with spaces, which you don't want LOCATE to search for, I guess.

Now, the result of LOCATE is the position of the first occurrence of the
search string found in "col3". This is again an INTEGER. I don't know if
you really want to compare that position with the day of the week. Given
your examples below, I think you do.
col3 is a field that contains a string like '12 4567', or
'1 4567'. This field specifies what day in the week the job executes,
and if there is a match, I want to report col1 and col2.


With these examples, it would be even easier to accomplish your goal: just
see if the position for the current day-of-week is empty (a space) or not.

SUBSTR(col3, DAYOFWEEK(CURRENT DATE), 1) = ' '

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.