Home » SQL & PL/SQL » SQL & PL/SQL » PL SQL - In Operator Issue
icon5.gif  PL SQL - In Operator Issue [message #270632] Thu, 27 September 2007 12:44 Go to next message
trauwitz
Messages: 3
Registered: September 2007
Location: Mexico
Junior Member
Hi,

I hope somebody could help me on my code,
here is the question:

The following code is working fine:
--------------------------------------------------------------
SET TERMOUT OFF
SET SERVEROUTPUT ON SIZE 999999
SET ECHO ON

DECLARE

v_week NUMBER;
v_year NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('*** START ***');

SELECT MAX(fiscal_year),
MAX(fiscal_week)
INTO v_year,
v_week
FROM pwc_ias_inv_highest_parts
WHERE fiscal_year = (SELECT MAX(fiscal_year)
FROM pwc_ias_inv_highest_parts
WHERE plant_cd IN ('AAA','BBB','CCC','DDD'));


DBMS_OUTPUT.PUT_LINE('v_year= '||v_year);
DBMS_OUTPUT.PUT_LINE('v_week= '||v_week);
END;
/

-----------------------------------------------
-- SQLPlus LOG:
--
-- v_year= 2007
-- v_week= 37

-- PL/SQL procedure successfully completed.
-----------------------------------------------

BUT this another one NOT, Why????

-----------------------------------------------------------------

DECLARE

v_code VARCHAR2(200);
v_week NUMBER;
v_year NUMBER;

BEGIN

v_code:= ''''||'AAA'||''''||','||''''||'BBB'||''''||','||''''||'CCC'||'''';

DBMS_OUTPUT.PUT_LINE('*** START***');
DBMS_OUTPUT.PUT_LINE('v_code = ' || v_code);

SELECT MAX(fiscal_year),
MAX(fiscal_week)
INTO v_year,
v_week
FROM pwc_ias_inv_highest_parts
WHERE fiscal_year = (SELECT MAX(fiscal_year)
FROM pwc_ias_inv_highest_parts
WHERE plant_cd IN (v_code));


DBMS_OUTPUT.PUT_LINE('v_year= '||v_year);
DBMS_OUTPUT.PUT_LINE('v_week= '||v_week);
END;
/

-----------------------------------------------------------------

-- v_code = 'AAA','BBB','CCC'
-- v_year=
-- v_week=


-- PL/SQL procedure successfully completed.

-----------------------------------------------------------------

Note: In the fist one I use the IN Operator without a variable, but in the second one I use it with a variable as parameter.

Please , somebody help me Embarassed

Thanks!
Re: PL SQL - In Operator Issue [message #270637 is a reply to message #270632] Thu, 27 September 2007 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: PL SQL - In Operator Issue [message #270638 is a reply to message #270632] Thu, 27 September 2007 12:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use dynamic sql.
dbms_sql or execute immediate

Please have a look at OraFAQ Forum Guide.

By
Vamsi
Re: PL SQL - In Operator Issue [message #270639 is a reply to message #270637] Thu, 27 September 2007 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search "varying in list" on AskTom.

Regards
Michel
Re: PL SQL - In Operator Issue [message #270709 is a reply to message #270638] Thu, 27 September 2007 16:49 Go to previous messageGo to next message
trauwitz
Messages: 3
Registered: September 2007
Location: Mexico
Junior Member
Hi Vamsi,

I resolved my problem with Execute Immediate,

Thank you so much.
Re: PL SQL - In Operator Issue [message #270711 is a reply to message #270639] Thu, 27 September 2007 16:51 Go to previous message
trauwitz
Messages: 3
Registered: September 2007
Location: Mexico
Junior Member
Hi Michel,

Thanks a lot ! Ask Tom is great!!

Best regards,
Angelica.
Previous Topic: altering the table
Next Topic: Quary Required ., ( Column to row )
Goto Forum:
  


Current Time: Sat Dec 10 22:26:41 CST 2016

Total time taken to generate the page: 0.04086 seconds