How to use variables in where? Construction view:

The first select returns a single value (works correctly). Problem when calling a variable in the second select.

declare myVar date; begin select myField into :myVar from myTable; select * into col from secondTable where secField > &myVar and rownum < 2; end;

This design does not work. Interested in just such a design - I know workaround solutions.

  • And the question is what? - Viktorov
  • Deleted the answer, apparently not answering your question. Formulate the question more accurately. It is not clear what you ask. - Viktorov
  • Task: it is necessary to add the result of the query (select) to the oracle variable. After that, this variable is used in the where clause of the next query. I gave an example of how I did it. Any alternatives will suit me. Sorry for the incorrectness, but I do not know how to properly set it. - meow meow
  • And yet, what you did not accept the answer that was given and then deleted? Variables in Oracle participate in queries without any colons or ampersands in front of them. just as it is. Of course, no variable should be named the same way as any column in the table with which you work, so that the interpreter does not confuse them - Mike
  • Not satisfied. I tested it - it does not work, if you do not trust you can check it yourself. - meow meow

1 answer 1

You have a block PL \ SQL code. Here is a working version of what you have written. Without clarifying the problem it is difficult to understand what you ask. It is assumed that in the secondTable table secondTable is a column with the name col1 . In the block, the result of the query must be saved somewhere, you cannot write a select without saying where to place its result. I also added the condition rownum < 2 , since more than the 1st value cannot be returned to the variable (this will lead to an error)

 declare myVar date; col varchar2(100); begin select myField into myVar from myTable; select t.col1 into col from secondTable t where secField > myVar and rownum < 2; end;
  • The construction doesn't work - meow meow
  • Explain what it means "not working"? Does not perform the task (if so, what should it do)? Returns an error? - Viktorov
  • thank! after edits it works) - meow meow
  • @meowmeow then click the check mark to accept the answer as correct. - Viktorov