Thread: Select Cast Error

I have a field that is varchar(15) type and an example of data I'm working with is (PROJ-0001-06)

 

I can make these two select statements work but not together.

select cast((max(substring(test.test from 6 for 4))) AS INTEGER) + 1  FROM test;
select max(substring(test.test from 11 for 2)) FROM test;

 

I want this to work like this.

select cast((max(substring(test.test from 6 for 4))) as integer) + 1  FROM test where max(substring(test.test from 11 for 2));

 

List below is a better idea of what my table looks like and the result I need.

PROJ-0004-05

PROJ-0001-06

PROJ-0002-06

PROJ-0003-06

 

When I run my select statement I want to return the number 4.  The idea is that I need the next highest number in the middle but take in consideration that the highest trailing numbers take president.

 

There error I get is this  ERROR:  argument of WHERE must be type boolean, not type text

---

Be one of the first to try Windows Live Mail. Windows Live Mail.

> select cast((max(substring(test.test from 6 for 4))) as integer) + 1
> FROM test where max(substring(test.test from 11 for 2));

This where clause is just returning a number but not comparing it to
anything.  Normally it would be something like WHERE test.test  =
max(substring(test.test from 11 for 2)).  That is the reason for the
error message you are seeing.

I don't quite get how you are expecting to return 4 from 0004 instead of
3 from 0003 based on the statement above.

Mike
>
>
> List below is a better idea of what my table looks like and the result
> I need.
>
> PROJ-0004-05
>
> PROJ-0001-06
>
> PROJ-0002-06
>
> PROJ-0003-06
>
>
>
> When I run my select statement I want to return the number 4.  The
> idea is that I need the next highest number in the middle but take in
> consideration that the highest trailing numbers take president.
>
>
>
> There error I get is this  ERROR:  argument of WHERE must be type
> boolean, not type text
>
>
>
> ______________________________________________________________________
> Be one of the first to try Windows Live Mail. Windows Live Mail.

On fim, 2006-09-21 at 21:57 -0400, Brad Budge wrote:
> I have a field that is varchar(15) type and an example of data I'm
> working with is (PROJ-0001-06)

> select cast((max(substring(test.test from 6 for 4))) as integer) + 1
> FROM test where max(substring(test.test from 11 for 2));
>

as someone already pointed out, this where clause
is just equivalent to WHERE '06'
and does not mean anything


> List below is a better idea of what my table looks like and the result
> I need.
> PROJ-0004-05
> PROJ-0001-06
> PROJ-0002-06
> PROJ-0003-06

> When I run my select statement I want to return the number 4.  The
> idea is that I need the next highest number in the middle but take in
> consideration that the highest trailing numbers take president.

if all the values are really formatted like this, you could ORDER BY to
get the value you want:

SELECT
   1 +
   CAST(substring(test.test from 6 for 4) AS INTEGER)
FROM test
ORDER BY
   substring(test.test from 11 for 2) DESC,
   substring(test.test from 6 for 4) DESC
LIMIT 1;

gnari