O$: SQL Statement

Steve Finger steve at srptech.com
Thu May 29 21:01:19 EDT 2008


Christine,

Is Null is definitely  supported by OmnisSql as is the join you need. 
Did you try reversing:

CC_CM_SEQ =* CM_SEQ_NO to
CM_SEQ_NO =* CC_CM_SEQ

Outer joins are very slow in OmnisSql but they do work. It's been a 
while and I don't have a test database to try it on but I'm pretty sure 
you can get the results you want

Steve Finger

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
>>
>> On May 29, 2008, at 2:22 PM, Christine Penner wrote:
>>
>>> I have an SQL question. I'm currently using Omnis SQL but will be
>>> using Oracle, MySQL and possibly others. I have 2 tables I am
>>> joining. Contacts and ContactCategories. Contacts can have many
>>> categories but not all contacts have a category. I want to build an
>>> SQL statement that will give me all contacts that have no category
>>> OR have "category 1". I have tried a few things and none of it
>>> works. Here is what I've tried so far: (I realize the =* wont work
>>> for all back ends. This is temporary)
>>>
>>> SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO
>>> AND ((CC_LM_SEQ=1) OR (CC_LM_SEQ=0))
>>>         ; This gives me all contacts
>>>         ; If I get rid of the * after the = I get only the one with
>>> Category 1
>>>
>>> SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO
>>> AND ((CC_LM_SEQ=899) OR NOT(CC_LM_SEQ))
>>>         ; Gives an error
>>>
>>> SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO
>>> AND ((CC_LM_SEQ=899) OR ((CC_LM_SEQ) IS NULL))
>>>         ; Gives an error
>>>
>>> I have also tried getting rid of the Category = 1 part (only show
>>> Contacts with no Category) and the results are the same. I either
>>> get everything or an error.
>>>
>>> Anyone know how to do that?
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



More information about the omnisdev-en mailing list