Home » SQL & PL/SQL » SQL & PL/SQL » Storing value in a variable
Storing value in a variable [message #190615] Thu, 31 August 2006 07:04 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF

DEFINE csvfile = &1
DEFINE rptid = &2

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500

SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL &csvfile

--
-- Column Headings
--
--
-- Write out Report Indentifier
--
SELECT '&rptid'||','
  FROM DUAL;
-- Column Headings
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,'    FROM DUAL;
--
-- Detail Information
--

SPOOL OFF
SET TERMOUT ON

PROMPT Data extraction complete.
PROMPT

The above code is exporting a CSV file. I want a formula or a variabe where I can store a value and display with the SELECT statement which can further be exported to the CSV file.
The value which I want to store is v_date:

SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                                2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                                3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                                4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                                5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                                6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                            TO_CHAR(SYSDATE-7,'dd-mon-yy')) INTO v_date FROM DUAL;


I have to display the value v_date and some other combinations by manipulating the value of v_date.
Please let me know how can I use the value of v_date which can be stored in a variable and further used. I can't use any function or procedure but have to do it in the above sql file only.

In the below select statement I have to insert the v_date with different combinations, so I cannot use the SELECT DECODE statement in this select statement.

SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,' FROM DUAL;

Please advice.

Thanks,
Mona
Re: Storing value in a variable [message #190622 is a reply to message #190615] Thu, 31 August 2006 07:44 Go to previous messageGo to next message
bhuvan_orcl
Messages: 1
Registered: August 2006
Location: Chennai
Junior Member

Hi

Before Opening the Spool File,you can use select decode and assign it into v_date and then you can concatenate the same after the select statement in the spool.

Regards

Bhuvan
Re: Storing value in a variable [message #190998 is a reply to message #190622] Mon, 04 September 2006 01:14 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hey Bhuvan,
I cannot use the DECODE statement along with the SELECT statement. As I have to display many combinations of it and the select statement with become too heave and repeatitive. I have to use this around 12 times and with firther conditions. So I have to save the value in a variable.
If I use simply the SELECT statement, I need to declare the variable v_date that will hold the value which can further be used. That is where I am facing the problem. If I use the PL/SQL tags in betwen, this too is causing the error.
Please advice as where and how can I save the value of v_date which can be used in later SELECT statements to display and store in the CSV file.


SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF

DEFINE csvfile = &1

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500

v_date DATE   := TRUNC(SYSDATE,'dd-mon-yy');

INSERT INTO DUAL VALUES (SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                                2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                                3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                                4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                                5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                                6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                            TO_CHAR(SYSDATE-7,'dd-mon-yy')) FROM DUAL);

SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL &csvfile
-- Column Headings
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,'    FROM DUAL;
SPOOL OFF
SET TERMOUT ON

PROMPT Data extraction complete.
PROMPT
END;


In the above select statement I need to concatenate the value v_date and I have to display various combinations of the v_date. So I need to get its value stored in a variable. Please advice as how can I do that as using it before spooling off is not working.

Thanks,
Mona

[Updated on: Mon, 04 September 2006 01:49]

Report message to a moderator

Re: Storing value in a variable [message #191008 is a reply to message #190615] Mon, 04 September 2006 01:59 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
Using Bind Variables
Have you read this?

BTW, your code NLS-dependent.
Re: Storing value in a variable [message #191031 is a reply to message #191008] Mon, 04 September 2006 04:00 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi, I used Bind variable. But I am not able to get the result of the select statement where the bind variable should also return the result.
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500

SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL try.csv

variable v_date varchar2;

SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                                2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                                3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                                4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                                5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                                6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                            TO_CHAR(SYSDATE-7,'dd-mon-yy')) INTO :v_date FROM DUAL;
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,':v_date    FROM DUAL;

SPOOL OFF


This query should work:
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,':v_date FROM DUAL;

Please advice,
Mona


[Updated on: Mon, 04 September 2006 07:04]

Report message to a moderator

Previous Topic: Difference
Next Topic: Create a function
Goto Forum:
  


Current Time: Mon Dec 05 11:04:08 CST 2016

Total time taken to generate the page: 0.12189 seconds