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.
http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
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
Show quoted text
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