PL/SQL Variables Problem

[SeAL]

Hi peeps,

Does someone know how to select a variable of a select into?

here's my sample :

select count(*) into Nb_Facture_Echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and (TO_DATE(sysdate, 'dd-mm-yy') - TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy') < 0);

IF Nb_Facture_Echues <> 0 THEN
select SUM(PNS.MONTANT) into Facture_echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and (TO_DATE(sysdate, 'dd-mm-yy') - TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy') < 0);
ELSE
Facture_echues := 0;
END IF;

select Facture_echues from dual;

Why this doesn't work?
Does anybody got an issue for this probleme?

Thanks

 

[jee]

I think your problem is the select of your variable (from dual) also needs an into.

I just executed the following PL/SQL block

DECLARE
   x   VARCHAR2 (5);
   y   VARCHAR2 (5);
BEGIN
   x := 'ABCDE';
   SELECT x INTO y FROM dual;
   dbms_output.put_line (y);
END;

and the output was ABCDE as expected.

 

[rcurva]

If your purpose is to print the outpput into your SQLplus screen, use the dbms_output package instead, but SET SERVEROUTPUT ON first in the sqlplus window.

For example, this will be your sript:

SET SERVER OUTPUT ON
DECLARE
-- declare ur variables here
BEGIN
select count(*) into Nb_Facture_Echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and (TO_DATE(sysdate, 'dd-mm-yy') - TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy') < 0);

IF Nb_Facture_Echues <> 0 THEN
select SUM(PNS.MONTANT) into Facture_echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and (TO_DATE(sysdate, 'dd-mm-yy') - TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy') < 0);
ELSE
Facture_echues := 0;
END IF;

dbms_output.put_line(TO_char(Facture_echues));

END;
/

SET SERVEROUTPUT OFF
Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[rcurva]

Correction, no space between SERVER OUTPUT, it should be:

SET SERVEROUTPUT ON

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[sem]

Why do you convert sysdate to date? It returns DATE without conversion!

(TO_DATE(sysdate, 'dd-mm-yy') - TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy') < 0);

should be

sysdate < TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy')

of course if your PNS.DATE_ECHEANCE field contains varchar2. If it's of DATE datatype, you need no
conversions at all:

sysdate < PNS.DATE_ECHEANCE

And after all: what language do you use? If your language is PL/SQL you have to execute an [anonymous] block starting with BEGIN and ending with END. You should also declare your variables. To declare session-wide bind variable in sql*plus you may use VAR[IABLE] statement, but to use this variable you should prefix it by colon :

To print its value use PRINT statement (use variable name without colon in it) outside your pl/sql block.

You may also use dbms_output function, replacing your last statement by dbms_output.put_line(Facture_echues), but this package is normally used for debug purposes only.

So your statement may be:

declare
Nb_Facture_Echues number;
Facture_echues number;

begin

select count(*) into Nb_Facture_Echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and sysdate < TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy');

IF Nb_Facture_Echues <> 0 THEN
select SUM(PNS.MONTANT) into Facture_echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and sysdate < TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy');
ELSE
Facture_echues := 0;
END IF;

dbms_output.put_line(Facture_echues);
end;
/

 

[SeAL]

Well I think that i forget to tell you something!

This is a stored procedure where i need to select so much thing that i must have variables.

My problem is to select all those variables at the end of my stored procedure.

 

[rcurva]

Then create a function instead:

CREATE OR REPLACE
FUNCTION sample RETURN NUMBER IS
Nb_Facture_Echues number;
Facture_echues number;

begin

select count(*) into Nb_Facture_Echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and sysdate < TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy');

IF Nb_Facture_Echues <> 0 THEN
select SUM(PNS.MONTANT) into Facture_echues
from BT_PNS_POS PNS
where PNS.CODE_CLIENT = var_codeClient
and sysdate < TO_DATE(PNS.DATE_ECHEANCE, 'dd-mm-yy');
ELSE
Facture_echues := 0;
END IF;

RETURN Facture_echues;

end;
/
Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War