i had a problem with Query [message #304509] |
Wed, 05 March 2008 07:50 |
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 |
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.
|
|
|
|
|