Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: PL/SQL Use...for existing sql ...urgent help needed

    Hi All

    I have the following .sql script ..which gives the output into
    a .csv file(~ delimeted),
    just for sample i have give some of the scritp only(not total)

    Now i want to change this script to use PL/SQL functions..to save time to get output.

    How best i can use PL/SQL
    what is best way i can reduce the code ...that is repetation of SQL statement...

    Try to help me get some idea and if possible some possible code to..

    Using any procedure/function or advanced PL/SQL
    i want to get the same output as of now but finally it has to store in .csv file as ~ is a delimeter.

    Thanks in advance......

    ***************************

    SELECT
    DOC_NUM,
    WRDT,
    STORE_CD,
    EMP1,
    decode(ordtpcd,'SAL',1*sale1/100,0) sal,
    decode(ordtpcd,'CRM',1*sale1/100,0) crm,
    FROM SOT
    WHERE WRDT between '&BWrDt' and '&EWtDT'
    and STCD in ('O','F')
    and OTPCD in ('SAL', 'CRM');


    SELECT
    DOC_NUM,
    WRDT,
    STORE_CD,
    EMP1,
    decode(ordtpcd,'SAL',1*sale2/100,0) sal,
    decode(ordtpcd,'CRM',1*sale2/100,0) crm,
    FROM SOT
    WHERE WRDT between '&BWrDt' and '&EWtDT'
    and STCD in ('O','F')
    and OTPCD in ('SAL', 'CRM')
    and EMP2 is not null;


    SELECT
    DOC_NUM,
    FInalDT,
    STORE_CD,
    EMP1,
    (-1)*decode(ordtpcd,'SAL',1*sale1/100,0) sal,
    (-1)*decode(ordtpcd,'CRM',1*sale1/100,0) crm,
    FROM SOT
    WHERE FInalDT between '&BWrDt' and '&EWtDT'
    and STCD = 'V'
    and OTPCD in ('SAL', 'CRM')
    and DOC_NUM > ' ';


    SELECT
    DOC_NUM,
    FInalDT,
    STORE_CD,
    EMP1,
    (-1)*decode(ordtpcd,'SAL',1*sale2/100,0) sal,
    (-1)*decode(ordtpcd,'CRM',1*sale2/100,0) crm,
    FROM SOT
    WHERE FInalDT between '&BWrDt' and '&EWtDT'
    and STCD = 'V'
    and OTPCD in ('SAL', 'CRM')
    and DOC_NUM > ' ';
    and EMP2 is not null;
    ***************************
    Suryadevara

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Code:
    DECLARE
    	lfFile  UTL_FILE.FILE_TYPE;
    	lspath varchar2(50) := 'c:\';
    	lsfile varchar2(50) := 'filename';
    	CURSOR c1 IS
    	SELECT 
    		DOC_NUM || '~' || WRDT || '~' || STORE_CD || '~' || EMP1
    		 || '~' || decode(ordtpcd,'SAL',1*sale1/100,0)
    		 || '~' || decode(ordtpcd,'CRM',1*sale1/100,0) THEROW
    	FROM SOT
    	WHERE WRDT between '&BWrDt' and '&EWtDT'
    		and STCD in ('O','F')
    		and OTPCD in ('SAL', 'CRM');
    BEGIN
    	lfFile  := UTL_FILE.FOPEN(lspath,lsfile||'.csv','w',32767);
    	FOR rec IN c1 LOOP
    		UTL_FILE.PUT_LINE(lfFile,rec.therow);
    	END LOOP;
    	UTL_FILE.FCLOSE_ALL;
    EXCEPTION
    	WHEN UTL_FILE.INVALID_OPERATION THEN
    		RAISE_APPLICATION_ERROR(-20051,'Invalid File open Operation');
    
    	WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    		RAISE_APPLICATION_ERROR(-20052,'Invalid File Name');
    
    	WHEN UTL_FILE.READ_ERROR THEN
    		RAISE_APPLICATION_ERROR(-20053,'Read Error');
    END;
    Last edited by robert xr4x4; 05-12-04 at 06:58.
    There are 10 types of people in the world, those that know Binary and those that don't.

  3. #3
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    Thanks for u r reply...i came to know from u r code that we have to use
    4 different cursers...for different ...select statement...
    is there any other way to mix those and get the same result...

    Thanks once again for ur help...help if there is any other way ...
    Suryadevara

  4. #4
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    you could union them together in one cursor

    SELECT
    DOC_NUM || '~' || WRDT || '~' || STORE_CD || '~' || EMP1
    || '~' || decode(ordtpcd,'SAL',1*sale1/100,0)
    || '~' || decode(ordtpcd,'CRM',1*sale1/100,0) THEROW
    FROM SOT
    WHERE WRDT between '&BWrDt' and '&EWtDT'
    and STCD in ('O','F')
    and OTPCD in ('SAL', 'CRM');

    UNION

    SELECT
    DOC_NUM || '~' || WRDT || '~' || STORE_CD || '~' || EMP1
    || '~' || decode(ordtpcd,'SAL',1*sale1/100,0)
    || '~' || decode(ordtpcd,'CRM',1*sale1/100,0) THEROW
    FROM SOT
    WHERE WRDT between '&BWrDt' and '&EWtDT'
    and STCD in ('O','F')
    and OTPCD in ('SAL', 'CRM')
    and EMP2 is not null;

    UNION

    SELECT
    DOC_NUM || '~' || WRDT || '~' || STORE_CD || '~' || EMP1
    || '~' || decode(ordtpcd,'SAL',1*sale1/100,0)
    || '~' || decode(ordtpcd,'CRM',1*sale1/100,0) THEROW
    FROM SOT
    WHERE FInalDT between '&BWrDt' and '&EWtDT'
    and STCD = 'V'
    and OTPCD in ('SAL', 'CRM')
    and DOC_NUM > ' ';

    UNION

    SELECT
    DOC_NUM || '~' || WRDT || '~' || STORE_CD || '~' || EMP1
    || '~' || decode(ordtpcd,'SAL',1*sale1/100,0)
    || '~' || decode(ordtpcd,'CRM',1*sale1/100,0) THEROW
    FROM SOT
    WHERE FInalDT between '&BWrDt' and '&EWtDT'
    and STCD = 'V'
    and OTPCD in ('SAL', 'CRM')
    and DOC_NUM > ' ';
    and EMP2 is not null;
    There are 10 types of people in the world, those that know Binary and those that don't.

  5. #5
    Join Date
    May 2004
    Location
    Bangalore
    Posts
    1
    Even i would like to have clarification on this same topic.
    Even i'm doing migration from SQL to PLSQL.
    I would like to know that if there are several temp_tables been created in SQL (ie the values from temp_table A will be called in temp_table B for further calculations and then the values form temp_table B will be used in temp_table c) Just an example, likewise i'm using around 10 temp_tables which should be migrated to PLSQL.
    Which would be the better process of doing it.
    Please suggest me some ways to make that query perform better atleast in PLSQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •