0

I need an evaluation of purchase contracts (Einkaufsverträge) and the corresponding sales contracts, where the Einkaufsverträge will be extended.

Therefore, I want to create a query that returns a row from the nag_einkaufsverträge table if the expression in the vertragsverlaengerung column (vertrag extension column) evaluates to true for the data item.

I used this query:

SELECT * 
FROM nag_einkaufsvertraege
WHERE EVALUATE (nag_einkaufsvertraege.vertragsverlaengerung, <data item>) = 1;

but then it shows error ORA-00936 – missing expression.

My table looks more or less like this:

enter image description here

I'm using SQL Tools 1.8 b38

Could someone please help to write the right query? I'm lost. Thank you.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
ella widya
  • 15
  • 5
  • you can find syntax format description for evaluate function here https://docs.oracle.com/middleware/1221/biee/BIVUG/GUID-7035040C-BB40-4392-920A-9A435593F659.htm#BILUG683 – Ankit Agrawal Sep 23 '20 at 13:04
  • And also here: https://docs.oracle.com/cd/B28359_01/appdev.111/b31088/exprn_sql.htm#CIHGBFDH. Note that `` is the expression that you have to replace with an appropriate one. – padaleiana Sep 23 '20 at 13:10
  • Forgive my weakness with Oracle, but should the WHERE clause not simply be "WHERE nag.vertragsverlaengerung = 1" ? Otherwise, what does represent in the WHERE clause? – v0rl0n Sep 23 '20 at 13:10
  • I already tried: 'SELECT EVALUATE('instr(%1, %0)', 'lieferant_name', 'vertragsverlaengerung') FROM nag_einkaufsvertraege' but didn't work out. It gives an error ORA-00904 Invalid Identifier. I still don't understand the syntax. – ella widya Sep 23 '20 at 13:10
  • @padaleiana: what is the appropiate query to replace based on my table value? I used this: "SELECT * FROM nag_einkaufsvertraege WHERE evaluate (vertragsverlaengerung, ) = 1;" But still gives error ORA-00936 – missing expression. – ella widya Sep 23 '20 at 13:17
  • @v0rl0n: this query only used when I want spesific information to be displayed: "SELECT * FROM nag_einkaufsvertraege WHERE nag_einkaufsvertraege.vertragsverlaengerung = 1;" But that's not what I need. It doesn't give the evaluation I want. – ella widya Sep 23 '20 at 13:21
  • 3
    Then please go into more detail in the evaluation you want. `EVALUATE` is used to compare complex data types to each other but based upon your screenshot, `vertragsverlaengerung` is just a number so you can simply do `WHERE nag_einkaufsvertraege.vertragsverlaengerung = 1`. If there is additional logic that you are looking for to limit your results, please describe what you are looking to happen, or even better provide example output that you are hoping to get. – EJ Egyed Sep 23 '20 at 13:33
  • EVALUATE is not a standard Oracle SQL functon To use it you need to get a DBA to install the Rules Management extension. [Find out more](https://docs.oracle.com/cd/B28359_01/appdev.111/b31088/exprn_app_e.htm#CHDBJCHH) – APC Sep 23 '20 at 14:04

0 Answers0