SQL Query with CASE statement

[Fante]

Dear EA experts,
I am working with a SQL query with a CASE statement

SELECT tabPort.ea_guid AS CLASSGUID , tabPort.Object_Type AS CLASSTYPE, tabObject.Name AS BlockpropertyName,
tabPort.Name AS PortName, (CASE WHEN tabPort.PDATA1 IS NOT NULL THEN tabPortType.Name END) AS PortType,
tabPort.Object_ID, tabPort.Stereotype AS PortStereotype

FROM t_object tabObject, t_object tabPort, t_object tabPortType

WHERE
tabObject.ea_guid = '<Search Term>' and
tabPort.ParentID = tabObject.Object_ID and
tabPort.PDATA1 = tabPortType.ea_guid


When I run the querry I get the error message "Syntax Error (missing operator) in query expression (CASE WHEN tabPort.PDATA1 IS NOT NULL THEN tabPortType.Name END).

I need the CASE statement, because some tabPort.PDATA1 objects are empty.

I would be happy if anyone could help me!

Fante

[OpenIT Solutions]

You may have to construct two queries, one with pdata1 where its not null and another with name when it is, then use a union to join the result set.

[Fante]

Thank you very much for your reply. I already used this workaround with multiple queries and Union but this is not a perfect solution for me, because even if PDATA1 is NULL I have to provide a value for the next table. See example below. One other problem is that I have much more complex queries and I need several sub queries if the CASE not works.
I am not an expert on SQL queries and wondering why the CASE structure is not working. Could it be that the CASE statement is not supported?
Could it be that Oracle and Microsoft JET data bases does not support the CASE statement?

SELECT tabPort.ea_guid AS CLASSGUID , tabPort.Object_Type AS CLASSTYPE, tabObject.Name AS BlockpropertyName,
tabPort.Name AS PortName, tabPortType.Name AS PortType, tabPort.Object_ID

FROM t_object tabObject, t_object tabPort, t_object tabPortType

WHERE
tabObject.ea_guid = '<Search Term>' and
tabPort.ParentID = tabObject.Object_ID and
tabPortType.ea_guid = tabPort.PDATA1

UNION

SELECT tabPort.ea_guid AS CLASSGUID , tabPort.Object_Type AS CLASSTYPE, tabObject.Name AS BlockpropertyName,
tabPort.Name AS PortName, tabPortType.Name AS PortType, tabPort.Object_ID

FROM t_object tabObject, t_object tabPort, t_object tabPortType

WHERE
tabObject.ea_guid = '<Search Term>' and
tabPort.ParentID = tabObject.Object_ID and
tabPort.PDATA1 = NULL and
tabPortType.ea_guid = tabPort.ea_guid /* fake */

[qwerty]

I think you'd be better supported in a SQL forum for the RDBMS you're using. Maybe you try over at StackOverflow?

q.

[g.makulik]

I think you'd be better supported in a SQL forum for the RDBMS you're using. Maybe you try over at StackOverflow?

q.

I'm really missing the possibility to upvote answers or questions in this forum like 'm used to in SO. +1 for this one ...

Günther

[qwerty]

Thanks :-) Although I know that I would have receive quite a number of -1 for some of my more sarcastic answers it would be nice to have a forum like SO. But that will only be a dream. Nobody would pay the effort for moving over to a more powerful forum.

q.