Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    15

    Unanswered: CHAR_LENGTH question

    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!!

  2. #2
    Join Date
    Nov 2006
    Posts
    15
    Looks like it does not work if I have MIN(EMAIL_SEQ_ID) As EMAIL_SEQ_ID in the select section.....

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You probaly have inconsistant grouping.
    Post your select statement

  4. #4
    Join Date
    Nov 2006
    Posts
    15
    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!!

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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
    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •