[Malachi4]

This syntax does not work, it returns all email_id's even nulls

PHP Code:

WHERE CHAR_LENGTH(Ltrim(rtrim(EMAIL_ID))) IS NOT NULL 


Samething with this one, it returns all email_id's:

PHP Code:

WHERE (datalength(Ltrim(rtrim(EMAIL_ID)))=0) 


Can anyone identify the problem?

THANK YOU!!

[Malachi4]

Looks like it does not work if I have MIN(EMAIL_SEQ_ID) As EMAIL_SEQ_ID in the select section.....

[pdreyer]

You probaly have inconsistant grouping.
Post your select statement

[Malachi4]

Here is my sql syntax:

SELECT PR_ID, MIN(EMAIL_SEQ_ID) As EMAIL_SEQ_ID, EMAIL_ID
FROM EMAIL
WHERE CHAR_LENGTH(Ltrim(rtrim(EMAIL_ID))) IS NOT NULL AND pr_id >= #StartPrid# AND pr_id <= #EndPrid#
ORDER BY pr_id;

Thanks for your help!!

[pdreyer]

That is indeed non standard sql as your grouping is inconsistent
This Sybase extension is to allow an easy join of an aggregate value to the rest of your data
In your select statement the where clause only apply to the MIN() function
An equivalent to your query using standard sql will be

select a.PR_ID, b.EMAIL_SEQ_ID, a.EMAIL_ID 
from EMAIL a 
,(select MIN(EMAIL_SEQ_ID) As EMAIL_SEQ_ID 
  from EMAIL 
  WHERE CHAR_LENGTH(Ltrim(rtrim(EMAIL_ID))) IS NOT NULL 
    and PR_ID between 1 and 2) b

I think you'll have to group by PR_ID and decide which EMAIL_ID you want min or max or do you want the one corosponding to the min(EMAIL_SEQ_ID)

Post some sample data and the expected output
AND your modified sql query if you still have a problem.