O$: SQL Statement

[MARTINK2 at nationwide.com]

Christine,

OminsSQL does support the SQL function LENGTH.  The trick is that the 
column you are using must be a character column.  If so, try ...Where 
LENGTH(CC_LM_SEQ)=0 Or CC_LM_SEQ=1.  Unfortunately, if not, OmnisSQL 
doesn't support the TO_CHAR SQL function either that would convert a 
numeric column value to a character value, thereby allowing you the use of 
the LENGTH function.

Kevin J. Martin
ID-Ten-Tee Software




Debra Peters <dshaberland at earthlink.net>
 
Sent by: omnisdev-en-bounces at lists.omnis-dev.com




05/29/2008 12:56 PM
Please respond to OmnisDev List - English 
<omnisdev-en at lists.omnis-dev.com>

From
Debra Peters <dshaberland at earthlink.net>
To
OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>, OmnisDev List - 
English <omnisdev-en at lists.omnis-dev.com>
cc

Subject
Re: O$: SQL Statement







Maybe NOT EXISTS CC_LM_SEQ>1? Or is EXISTS not supported by Omnis SQL, 
either?
Deb


At 02:36 PM 5/29/2008, Christine Penner wrote:

>Left Joins are not supported by Omnis. That's the reason for 
>the  =*. Part of the problem is that I need this to work for Omnis 
>SQL which doesn't support many typical SQL features. The (is null) 
>is not supported by OmnisSQL and that's the real issue. I need 
>another way of saying is null.
>
>Is there anyone who uses OmnisSQL who could help.
>
>Christine
>
>At 12:26 PM 29/05/2008, you wrote:
>>Hi Christine,
>>
>>What you want to use is an outer join. I can only speak for MySQL
>>here, but try this:
>>
>>SELECT * FROM CONTACTS left join CONTACT_CAT on CC_CM_SEQ = CM_SEQ_NO
>>where CC_LM_SEQ is null or CC_LM_SEQ = 1
>>
>>The left join (left because the "reference" table CONTACTS is on the
>>left side of the word "join" as written here) preserves all rows in
>>the reference table by default and brings along any matching rows
>>from the other table. The join...on syntax operates WITHIN the from
>>clause, building a virtual table on which the where clause will
>>operate. The where clause then chooses those rows where the category
>>is 1 or is null.
>>
>>I do not know whether this is supported by OmnisSQL... I can
>>guarantee it works with MySQL, though.
>>
>>HTH!
>>
>>Regards,
>>
>>David Swain

_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com