0

I am using Oracle SQL Developer and I need do a search in a table to find if at lest one record matches. After the search is done, a value of true or false is returned from the stored procedure. I was asked to not only return that result of true or false but also add two values from newly added columns. I already have the part of the query that returns true or false (This was previously created). Now I need to create a select that returns these two new values along with the the previously generated value 'T' or 'F' as the result. This value is created using Decode in the following manner: DECODE(COUNT(ColumnId), 0, 'F', 'T') answer. How can I return this true or false value (that doesn't exist in the table) alongside the two values from the table as the SELECT values?

This is for a web service, the database uses Oracle SQL Developer as interface and the client expects the String value along with the two columns value if there is a match. I am going to return the string along with two dummy values is there isn't a match. I have tried selecting the two columns and then selecting that answer varibale with an AS name and it did not work. Selecting the varibale itself did not work either. 

create or replace procedure
PROCEDURE procedurename
(
Value IN VARCHAR,
BoolValue OUT VARCHAR,
) AS
BEGIN
SELECT DECODE(COUNT(ColumnId), 0, 'F', 'T') answer
INTO Variable
FROM tablename
WHERE (*Conditions*);
END
procedurename

IF answer= 'T' THEN 
            SELECT Val1, Val2, Val3, answer
            FROM tablename
            WHERE col1= 'val1'AND col2 = 'val2';

I expect to have a result of (for example):

Val1, Val2, Val3, T

I get:

"answer" : invalid identifier

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
William Lewis
  • 11
  • 1
  • 2
    There is no `IF` in SQL, and if that query is in a PL/SQL block then it needs to select into something (i.e. three variables), as well as declaring the `answer` variable. If you're trying to run the query as plain SQL then you might be a bit stuck, as you can't call a procedure from SQL. You can call a function, so you could change the procedure to a function, or add a wrapper function, or - from 12c - define a temporary function in a CTE. It isn't quite clear how you need to call this though. Is the procedure query against the same table, and with the same conditions? Is col1/col2 unique? – Alex Poole May 16 '19 at 18:35
  • I hardcoded it as SELECT 'T' as answer, val1, val2, val3. And yes, it is using PL/SQL and it is a store procedure being modified. – William Lewis May 16 '19 at 19:29

0 Answers0