Question

Locked

SQL Where clause with case construct

By john.a.wills ·
I have a very large query, with 10 tables and a lot of complex conditions. It is failing. I have sliced pieces off it until I am left with this:
select distinct perbarg_pidm "pidm"
from PERBARG
where CASE perbarg_barg_code = 'A'
THEN
perbarg_bure_code = '&&Campus'
END
, which yields ERROR at line 3:
ORA-00920: invalid relational operator with a star under the P of perbarg;
or this:
select perbarg_pidm "pidm"
from PERBARG
where CASE (perbarg_barg_code = 'A')
THEN
perbarg_bure_code = '&&Campus'
END
, which yields ERROR at line 3:
ORA-00907: missing right parenthesis
with a star under the first equals sign.
I could obviously replace the present queries with something else and get it to work, but I do need this structure for the query from which I am deriving these. Where am I going wrong?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

A lot of information missing, but ...

by Sven2157 In reply to SQL Where clause with cas ...

What are you comparing? The DISTINCT clause will only find DISTINCT values in the table field specified. Example: SELECT DISTINCT City FROM Customers;

The "pidm" is a bit confusing, as well. Are you wanting an AS clause there? SELECT DISTINCT City AS 'Town' FROM Customers;. Do you want that table to be referred to as 'pidm'?

The CASE clause is used for finding a value WHEN it equals a value THEN returning it as 'Something' ELSE return it as 'This' END. Think of it like a SWITCH statement.

So without knowing what you're actual data is, or what it is you need to be output, it is hard to say why or where your query is error-ring, nor is it easy to say how to fix it.

From what I can gather, you are looking at something like this:

SELECT DISTINCT PERBARG.perbarg_pidm pidm
CASE
WHEN PERBARG.perbarg_barg_code = 'A' THEN PERBARG.perbarg_bure_code = '&&Campus'
END

Again, I am not sure exactly what it is that is your desired result, nor the table structure. Are there more than one table being called here? Anyway you can try something like that.

Hope that helps! ;-)

Collapse -

Reponse To Answer

by Slayer_ In reply to A lot of information miss ...

So a case is for multiple where clauses using a when keyword?

I've never used case before, is this t-sql or another?

Collapse -

Reponse To Answer

by Sven2157 In reply to A lot of information miss ...

As pointed out above, and below, no. It is more like a SWITCH statement. If you need to use multiple WHERE clauses, then just append the next condition to the statement. Something like: SELECT * FROM my_table WHERE product_id >= 500 OR product_id <= 250 AND product_id != NULL;

Collapse -

WHEN missing

by john.a.wills In reply to SQL Where clause with cas ...

In both kinds of CASE construct in SQL one needs a WHEN clause before the condition tested for. My mistake was in omitting this. Oracle's mistake was in yielding misleading server messages.

No, slayer, this is not a special kind of SQL but the standard kind, here implemented by Oracle. And there is a further mistkae in my code: in Oracle, at least, the construct cannot yield a boolean value such as I wanted, so I Have had to use a deeper level of nesting and supply values tobe compared after the END, e.g. ...END = 'T'

Collapse -

Reponse To Answer

by Slayer_ In reply to WHEN missing

Oracle uses PL/SQL. So it is slightly different from T-SQL

Collapse -

Reponse To Answer

by Sven2157 In reply to WHEN missing

So ... ??? Did my suggestion work?

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums