CODEWITHSUNDEEP
sqloracle10g
provençal le breton
provençal le breton

Reputation: 1438

Select statement into decode function

so I am writing a select query using a decode function, and set the result into a variable.

The thing is, when there is no lines, I always got no result error.

I tried decode with the value, and counting, but no results.

SELECT decode(count(id), 0, (select id from t_table where intitule = '?' and id_type =  93), id) into v_id
      from t_table where intitule = null
      group by id;


SELECT decode(id, null, (select id from t_table where intitule = '?' and id_type =  93), id) into v_id
      from t_table where intitule = null
      group by id;

But, as there is no line to decode, it doesn't work.

So, my query, must get an id, into v_id, where intitule = a value, else it bring back a default value that exist into t_table.

And as I alwys got 0 lines, I raise an no result found error.

So for example, I got into t_table those three lines

id | intitule | id_type

1  | ?        | 5
2  | electricien | 5
3  | mécanicien | 4

I will have a where I did'nt specify intitule, so intitule = null

So this bring back 0 lines.

But I know its the id_type, so if I do not found any lines (that is the case), I bring back the default value(?) for the id_type that I know (5).

So, in the end, I must have 1 into v_id.

But if I have intitule = 'electricien', so I will get 2 into v_id.

Is that better?

Can somebody help me?

Upvotes: 0

Views: 2963

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Sorry to say, there is more than one flaw in what you are doing.

First of all where intitule = null will never work. NULL doesn't equal anything, so = doesn't work with it. This must be where intitule is null instead.

But then, you want to select one row. So why do you group by ID? To group by ID means: get one record per ID. This is not at all what you want. You want one ID back.

It seems, you consider intitule to be unique? Then simply select the ID for the intitule wanted. If I understand it correctly, there is no record where intitule is null, right? You only want to select a particular record if no intitule is given to look for. So use OR in your where clause: Either intitule is given and must match OR it is not given and intitule must match '?' and id_type must be 93.

select id
into v_id
from t_table 
where intitule = vi_intitule
or (vi_intitule is null and intitule = '?' and id_type =  93);

Upvotes: 1

Khurram Ilyas
Khurram Ilyas

Reputation: 127

Try This:

SELECT 
   decode
   (
      select count(id) from t_table where id= e.id group by id, 
      0, 
      (select id from t_table where intitule = '?' and id_type =  93), 
      id
   ) 
   into v_id
FROM t_table e where intitule is null
GROUP BY id;

Upvotes: 0

Related Questions