At 09:48 AM 21-06-02 +0800, you wrote:

>Hello all,
>
>I have query SQL at Store Procedure like this :
>
> FOR SELECT DISTINCT MO.OBJECT_ID, MO.LOCALE_ID, MO.OBJECT_NAME,
> MO.OBJECT_VALUES, MO.OBJECT_TYPE_ID, MOT.OBJECT_TYPE_NAME
> FROM M_OBJECTS MO, M_OBJECT_TYPE MOT, M_CONTENTS MC
> WHERE (MO.OBJECT_TYPE_ID = MOT.OBJECT_TYPE_ID AND
> CAST(MC.CONTENT_ID AS VARCHAR(255)) = MO.OBJECT_VALUES)
> AND MO.LOCALE_ID = 1
> INTO :OBJID, :OBJLCLID, :OBJNM, OBJVL, :OBJTPID, :OBJTPNM
> DO
> SUSPEND;
>
>
>the result is data from M_OBJECTS where MO.LOCALE_ID = 1 and
>MO.OBJECT_VALUES = CAST(MC.CONTENT_ID AS VARCHAR(255))
>like i want, but when i change
> ...WHERE (MO.OBJECT_TYPE_ID = MOT.OBJET_TYPE_ID AND
>CAST(MC.CONTENT_ID AS VARCHAR(255)) != MO.OBJECT_VALUES)
> AND MO.LOCALE = 1...
>
>the result is all data in M_OBJECTS include the result above
>(MO.OBJECT_VALUES = CAST(MC.CONTENT_ID AS VARCHAR(255)) which
>i want their not include in the result.
>
>why it happen ??
>
>note : MC.CONTENT_ID is INTEGER.

Ah, attribute this to your confusion as a result of using implicit join
syntax and attempting to treating a JOIN criterion as a WHERE
criterion. Let's clean up your query, apply explicit join syntax and see why:

This works:
FOR SELECT DISTINCT MO.OBJECT_ID, MO.LOCALE_ID, MO.OBJECT_NAME,
MO.OBJECT_VALUES, MO.OBJECT_TYPE_ID, MOT.OBJECT_TYPE_NAME
FROM M_OBJECTS MO
join M_OBJECT_TYPE MOT
on
MO.OBJECT_TYPE_ID = MOT.OBJECT_TYPE_ID
join M_CONTENTS MC
on
MO.OBJECT_VALUES = CAST(MC.CONTENT_ID AS VARCHAR(255)
/* the predicate above is a JOIN criterion, not a WHERE criterion 8?
where
MO.LOCALE_ID = 1
INTO :OBJID, :OBJLCLID, :OBJNM, OBJVL, :OBJTPID, :OBJTPNM
DO
SUSPEND;

Now, let's try to negate that join predicate:
FOR SELECT DISTINCT MO.OBJECT_ID, MO.LOCALE_ID, MO.OBJECT_NAME,
MO.OBJECT_VALUES, MO.OBJECT_TYPE_ID, MOT.OBJECT_TYPE_NAME
FROM M_OBJECTS MO
join M_OBJECT_TYPE MOT
on
MO.OBJECT_TYPE_ID = MOT.OBJECT_TYPE_ID
join M_CONTENTS MC
on
( NOT (MO.OBJECT_VALUES = CAST(MC.CONTENT_ID AS VARCHAR(255) ))
/* can you see the logic problem here? */
where
MO.LOCALE_ID = 1

You can't perform an inner join on "nothing". You might be able to get the
set you want by using an exclusive outer join on M_CONTENTS and using a
WHERE predicate to filter out the rows where the predicate MO.OBJECT_VALUES
= CAST(MC.CONTENT_ID AS VARCHAR(255) ) is true. I say "might", since you
are violating another SQL rule by joining to a right-hand table that
doesn't contribute any columns to the output...and your DISTINCT constraint
may not interact well with the NULLS generated by the outer join. I'll
leave you to experiment with this.<g>

There's often a good reason why standards change from version to version -
and this kind of confusion is one very good reason for the introduction of
explicit joins with SQL-92.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________