Home » SQL & PL/SQL » SQL & PL/SQL » i had a problem with Query
i had a problem with Query [message #304509] Wed, 05 March 2008 07:50 Go to next message
seenujanu
Messages: 53
Registered: August 2006
Location: chennai
Member

Hi Friends,

i had a problem of printing a values in EXCEl Sheet

i had two tables Item_Desc and Sales

ITEM_NAME	ITEM_DESC				
Moni	Monitor				
RAM	Random Access Memory				
USB	Universal Serial Bus				
OTHER	Other				
INC	Increasing				
DEC	Decreasing				

DESC_ID	SALE_ID	ITEM_ID	ITEM_NAME	REMARKS
1	101	201	Moni	No remarks
2	101	201	RAM	Yes
3	101	201	USB	Yeah
4	101	202	RAM	NO
5	101	202	INC	What

the output is 

i just want to print the values in EXCEL Sheet..

and [B]i am attachmenting with full detail...[/B]

SALE_ID	ITEM_ID	REMARKS	Monitor	Random Access Memory	Universal Serial Bus	Other	Increasing	Decreasing
101	201		No remarks	Yes	Yeah			
101	202			NO			What	

here is the Coding but ...

PROCEDURE Sales ( p_sale_id IN NUMBER DEFAULT NULL )
IS
CURSOR c_Sales ( v_sale_id VARCHAR2,v_item_id VARCHAR2 )
IS
  SELECT desc_id,sale_id,item_id,item_name,remarks
    FROM  Sales
   WHERE sale_id = v_sale_id
   AND   item_id = v_item_id
  ORDER By desc_id;

r_Sales c_Sales%rowtype;

CURSOR c_item_name
IS
  SELECT item_name,item_desc
  FROM   Item_Desc
  ORDER By item_name;

r_item_name  c_item_name%rowtype;

i         NUMBER := 0;
j         NUMBER := 0;
salitem   CHAR(1);
v_remVal LONG;
v_rem    LONG;

BEGIN

   FOR r_item_name IN c_item_name
   LOOP
      IF v_rem IS NULL THEN
         v_rem := r_item_name.item_desc ;
      ELSE
         v_rem := v_rem || fldSep || r_item_name.item_desc;
      END IF;
   END LOOP;

   htp.print( 'Sale ID'
         || salitem || 'Item ID'
         || salitem || 'Remarks'
         || salitem || v_rem
         );

   FOR r_item_name IN c_item_name
   LOOP
     FOR r_Sales IN c_Sales(101,201)
     LOOP
     
        IF r_item_name.item_name = r_Sales.item_name THEN
           IF v_remVal IS NULL THEN
              v_remVal := r_Sales.remarks ;
           ELSE
              v_remVal := v_remVal || salitem || r_Sales.remarks ;
           END IF ;
        END IF ;

     END LOOP;
   
     htp.print(      '101'
               || salitem || '201'
               || salitem || ''
               || salitem || v_remVal
              );
   
   END LOOP;

END;

Regards
Seenu

Re: i had a problem with Query [message #304649 is a reply to message #304509] Thu, 06 March 2008 00:40 Go to previous messageGo to next message
delconis
Messages: 1
Registered: March 2008
Junior Member
DECLARE
CURSOR C1 IS SELECT DISTINCT SALE_ID,ITEM_ID FROM SALES;
CURSOR C2(C_SALE_ID NUMBER,C_ITEM_ID NUMBER)
IS SELECT A.ITEM_NAME,A.REMARKS,B.ITEM_DESC FROM SALES A,ITEM_DESC B
WHERE A.ITEM_NAME=B.ITEM_NAME
AND A.SALE_ID =C_SALE_ID
AND A.ITEM_ID=C_ITEM_ID
ORDER BY B.ITEM_DESC;
CURSOR C3 IS SELECT ITEM_DESC FROM ITEM_DESC ORDER BY ITEM_DESC;
V_STR VARCHAR2(3000);
I NUMBER:=0;
J NUMBER:=0;
V_SALE_ID NUMBER;
V_ITEM_ID NUMBER;
BEGIN
--PRINT HEADER
V_STR:='SALE_ID,ITEM_ID,REMARKS';
FOR I IN C3 LOOP
V_STR:=V_STR||','||I.ITEM_DESC;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_STR);
--PRINT DETAIL
V_STR:=NULL;
FOR C_SALE IN C1 LOOP
V_STR:=C_SALE.SALE_ID||','||C_SALE.ITEM_ID||',';
FOR C_ITEM IN C2(C_SALE.SALE_ID,C_SALE.ITEM_ID) LOOP
IF (V_SALE_ID <>C_SALE.SALE_ID OR V_ITEM_ID<>C_SALE.ITEM_ID) OR (V_SALE_ID IS NULL AND V_ITEM_ID IS NULL) THEN
J:=0;
V_SALE_ID :=C_SALE.SALE_ID;
V_ITEM_ID :=C_SALE.ITEM_ID;
END IF;
I:=0;
FOR C_I IN C3 LOOP
I:=I+1;
IF C_ITEM.ITEM_DESC=C_I.ITEM_DESC THEN
V_STR:=V_STR||','||C_ITEM.REMARKS;
J:=I;
EXIT;
ELSE
IF I>J THEN
V_STR:=V_STR||','||' ';
END IF;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_STR);
END LOOP;
END;

The sql has space at the begin of every line in my PC,I don't know why it delete space when I post it.
Re: i had a problem with Query [message #304675 is a reply to message #304649] Thu, 06 March 2008 01:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You should spend some time to read the forum's guidelines.

Regards

Raj

[Updated on: Thu, 06 March 2008 01:41]

Report message to a moderator

Re: i had a problem with Query [message #305287 is a reply to message #304509] Mon, 10 March 2008 05:41 Go to previous message
seenujanu
Messages: 53
Registered: August 2006
Location: chennai
Member
Hi delconis,

Thanks for advice, that's what I needed i got it.

Regards

Seenu
Previous Topic: Variable formatting
Next Topic: Difference between where and having clause
Goto Forum:
  


Current Time: Thu Nov 07 16:27:28 CST 2024