locate and select

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hikums@gmail.com - 29 Jun 2005 20:12 GMT

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.

Reply to this Message

Knut Stolze - 30 Jun 2005 10:03 GMT

> 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) = ' '

Signature

Knut Stolze
Information Integration Development
IBM Germany / University of Jena

Reply to this Message

hikums@gmail.com - 30 Jun 2005 22:29 GMT

Thanks Knut, it worked.

> > how can I use a function inside a function, for example I want to
> > select col1, col2 from table1 where locate(dayofweek(current
[quoted text clipped - 30 lines]
> Information Integration Development
> IBM Germany / University of Jena

Reply to this Message