I would like to know if it's possible to write a sql query that returns a set of columns based on a condition.
Like for example:
If (id=='A')
{
Select id,name
From Table A
}
Else If(Condition=B)
{
Select Column1, Column3
From Table A
}
If yes please help me write it
You can do switch-like statements with CASE expressions in plain SQL. See the following example:
SELECT some_other_field,
id,
CASE ID
WHEN A THEN columnA
WHEN B THEN columnB
ELSE 'Unknown'
END genericvalue
FROM customers;
There are some limitations of course. For example the type of the return values in the THEN clause need to match, so you may need to convert for example all to char, or to int, etc.
Syntax (IF-THEN-ELSE)
The syntax is for IF-THEN-ELSE in Oracle/PLSQL is:
IF condition THEN
{...statements to execute when condition is TRUE...}
ELSE
{...statements to execute when condition is FALSE...}
END IF;
SQL itself isn't turing-complete and it doesn't have syntax for loops and conditions: you can perform a query with it, no matter how complex it is, but you can't decide which query to execute depending on a condition or perform a query a number of times, which is what you are trying to do here.
In order to provide such functionality each database developer typically provides an additional language that includes variable declaration, loops, conditionals, etc. For Oracle this language is PL/SQL.
What you need to do in SQL Developer to solve your issue and see how PL/SQL works is create an empty script, then write something like this:
--Enabling output to the console
SET SERVEROUTPUT ON;
DECLARE
--Variable declaration block; can initialize variables here too
test_var varchar2(10);
test_result varchar2(10);
BEGIN
--Initializing variables, the first one we will check in the IF statement, the second one is just for transparency
test_var := 'test';
test_result := '';
--IF block: check some condition, perform a select based on the value, save result into a variable
IF test_var = 'test' THEN
SELECT '1' INTO test_result FROM dual;
ELSE
SELECT '2' INTO test_result FROM dual;
END IF;
--Output the result to console
DBMS_OUTPUT.PUT_LINE(test_result);
END;
Then run it with 'Run script'/F5. You will get '1' as output as you would expect. Change test_var to something else and run it again, you will get '2'.
If you have questions of this kind it might be useful to read about what exactly SQL and PL/SQL are. PL/SQL is quite efficient and versatile and can be used for anything from automating SQL scripts to implementing complex optimisation algorithms.
Of course, PL/SQL has similar constructs for FOR and WHILE loops, CASE checks, etc.
I guess this is what you are looking at.
It is not possible to do such a selection in SQL even by using CASE and DECODE.
But the best we can do is we can create a function which returns a ref_cursor and use the function in the SQL stetement to fit your requirement.
Below is an example for it:
CREATE OR REPLACE
FUNCTION test1(
id1 VARCHAR)
RETURN sys_refcursor
AS
v_ref sys_refcursor;
BEGIN
IF(id1='A') THEN
OPEN v_ref FOR SELECT id,name FROM TABLE A;
ElsIf(id1='B') THEN
OPEN v_ref FOR SELECT Column1, Column3 FROM TABLE A;
END IF;
RETURN v_ref;
END;
select test1(A) from dual;
Above will display only the columns id and name.
idthat you compare come from?