0

We're learning about databases and for our case the database is Oracle, in class we we're asked to create a constraint based on the information of another field on the table, and the constraint should be build with a case inside, despite of a large google search nothing worked for me (we're working on apex Oracle online platform), so here is what we were asked to do:

Based on the acumuladoCompra field and Clasification field do the following:

If the classification is A the acumuladocompra must be less than $20,000; if the classification is E the acumuladocompra should be between $20,001 and $40,000; if the classification is I, the acumuladocompra must be greater than $40,001 but less than $60,000; if the classification is O the acumuladocompra must be less than $80,000 but more than $60,001; otherwise it should be higher to $100,000

So, how could this be build?

Edit: I've tried this code:

alter table "CLIENTES_152" add constraint "RESTRICCIONCLASIFICA" check ( CASE when CLASIFICACION_152 = A THEN ACUMULADOCOMPRA_152 <20000; 
when CLASIFICACION_152 = E THEN ACUMULADOCOMPRA_152 BETWEEN 20001 AND 40000;
when CLASIFICACION_152 = I THEN ACUMULADOCOMPRA_152 >40001 AND <60000;
when CLASIFICACION_152 = O THEN ACUMULADOCOMPRA_152 >60001 AND <80000;
when CLASIFICACION_152 = U THEN ACUMULADOCOMPRA_152 >100000; END)

But got

ORA-00905: missing keyword

What's wrong?

Leonardo Forero
  • 21
  • 2
  • 3
    It sounds a lot like you're asking us to do your homework Leo. I googled 'oracle case constraint' and the second hit was the official oracle documentation. Try reading that and having a go at the problem yourself then come back if you have a specific question. Read [ask] first, and good luck with your course. http://www.oracle.com/technetwork/issue-archive/o63asktom-082127.html – Software Engineer Mar 28 '16 at 03:10
  • Hi, thank you for your answer, but unfortunately I've reached that information before and it wasn't of help building the case inside a constraint. Just to be clear my homework is done given that we had two options, use CASE or use AND OR clauses but AND OR was a lot easier to implement, but as I was frustrated trying the CASE then I was wondering how it could be done, cause the found info haven't been of help. – Leonardo Forero Mar 28 '16 at 15:49
  • @EngineerDollery I've tried this code: alter table "CLIENTES_152" add constraint "RESTRICCIONCLASIFICA" check ( CASE when CLASIFICACION_152 = A THEN ACUMULADOCOMPRA_152 <20000; when CLASIFICACION_152 = E THEN ACUMULADOCOMPRA_152 BETWEEN 20001 AND 40000; when CLASIFICACION_152 = I THEN ACUMULADOCOMPRA_152 >40001 AND <60000; when CLASIFICACION_152 = O THEN ACUMULADOCOMPRA_152 >60001 AND <80000; when CLASIFICACION_152 = U THEN ACUMULADOCOMPRA_152 >100000; END) But got ORA-00905: missing keyword What's wrong? – Leonardo Forero Mar 28 '16 at 21:22
  • You should add that to your question – Software Engineer Mar 29 '16 at 10:35
  • look at example http://stackoverflow.com/questions/8072463/oracle-sql-can-case-be-used-in-a-check-constraint-to-determine-data-attributes?rq=1 – Pars Mar 30 '16 at 07:20
  • @Pars In fact I did this with that example but it haven't worked. – Leonardo Forero Mar 30 '16 at 11:37
  • try this " alter table CLIENTES_152 add constraint "RESTRICCIONCLASIFICA" check ( CASE when CLASIFICACION_152 = A THEN ACUMULADOCOMPRA_152 <20000 when CLASIFICACION_152 = E THEN ACUMULADOCOMPRA_152 BETWEEN 20001 AND 40000 when CLASIFICACION_152 = I THEN ACUMULADOCOMPRA_152 >40001 AND <60000 when CLASIFICACION_152 = O THEN ACUMULADOCOMPRA_152 >60001 AND <80000 when CLASIFICACION_152 = U THEN ACUMULADOCOMPRA_152 >100000 END); ", remember sql consist of only one ';' per sql statement – Pars Mar 30 '16 at 13:15
  • I think it's "end case" instead of end – Typo Mar 31 '16 at 02:48
  • @Pars Nope, Tried that but still same error. ORA-00905: missing keyword – Leonardo Forero Mar 31 '16 at 18:24
  • @Typo No, tried that but still the same error. – Leonardo Forero Mar 31 '16 at 18:25
  • (facepalm) CLASIFICACION_152 = 'A' CLASIFICACION_152 = 'E' and so on...asuming CLASIFICACION_152 is a varchar type – Typo Mar 31 '16 at 21:30
  • @Typo I've tried that but still same error, and yes the field is varchar. – Leonardo Forero Apr 01 '16 at 12:59

1 Answers1

0

To those who tried to help me thanks.

now, the final solution to this constraint was:

alter table "CLIENTES_152" add constraint "RESTRICCIONCLASIFICA" 
CHECK ( 
(CASE when CLASIFICACION_152 = 'A' AND ACUMULADOCOMPRA_152 <20000 THEN 1
when CLASIFICACION_152 = 'E' AND ACUMULADOCOMPRA_152 BETWEEN 20001 AND 40000 THEN 1
when CLASIFICACION_152 = 'I' AND ACUMULADOCOMPRA_152 BETWEEN 40001 AND 60000 THEN 1
when CLASIFICACION_152 = 'O' AND ACUMULADOCOMPRA_152 BETWEEN 60001 AND 80000 THEN 1
when CLASIFICACION_152 = 'U' AND ACUMULADOCOMPRA_152 >100000 THEN 1
ELSE 0 
END)=1
);

As you can see what I did was to change the way I was writing the case, so this is the way it can be written and finally it has worked very well.

Thanks for your tips and suggestions.

Note: I haven't come to the solution by myself, it was someone who's told me the right way the query had to be build!

Leonardo Forero
  • 21
  • 2