OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Complex query (Oracle 10.1.0.2)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Complex query [message #557185]

Sun, 10 June 2012 09:20

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

I have two tables one item master and there is one with cost and value , what i need is like a inner query or sub query where i want to get the output as formatted in reports 6i using data link ,like below with grouping .


SQL> CREATE TABLE OS_CST_GRP_VAL ( CG_GROUP VARCHAR2(7),CG_ITEM VARCHAR2(12),CG_COST NUMBER,CG_VAL NUMBER);

Table created.

SQL> CREATE TABLE ITEM_MAST (ITEM_CODE VARCHAR2(12),ITEM_ANLY_05 VARCHAR2(8),ITEM_UOM VARCHAR2(3));

Table created.

SQL> ALTER TABLE ITEM_MAST MODIFY ITEM_ANLY_05 VARCHAR2(10);

Table altered.

SQL>  INSERT INTO ITEM_MAST VALUES ('11VST053301','11VST0533','KGS');

1 row created.

SQL>  INSERT INTO ITEM_MAST VALUES('11VST053302','11VST0533','KGS');

1 row created.

SQL> INSERT INTO ITEM_MAST VALUES('11VST053303','11VST0533','KGS');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ITEM_MAST;

ITEM_CODE    ITEM_ANLY_ ITE                                                                         
------------ ---------- ---                                                                         
11VST053301  11VST0533  KGS                                                                         
11VST053302  11VST0533  KGS                                                                         
11VST053303  11VST0533  KGS                                                                         

SQL> INSERT INTO OS_CST_GRP_VAL VALUES ('JED_OC','11VST053301',2,4);

1 row created.

SQL> INSERT INTO OS_CST_GRP_VAL VALUES ('JED_CS','11VST053302',10,20);

1 row created.

SQL> INSERT INTO OS_CST_GRP_VAL VALUES ('JED_CS','11VST053303',20,30);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DESC OS_CST_GRP_VAL;
 Name                            Null?    Type
 ------------------------------- -------- ----
 CG_GROUP                                 VARCHAR2(7)
 CG_ITEM                                  VARCHAR2(12)
 CG_COST                                  NUMBER
 CG_VAL                                   NUMBER

SQL> SELECT CG_GROUP,CG_ITEM,CG_COST,CG_VAL,ITEM_ANLY_05 FROM
  2  OS_CST_GRP_VAL,ITEM_MAST
  3  WHERE CG_ITEM = ITEM_CODE
  4  AND CG_GROUP='JED_OC';

CG_GROU CG_ITEM        CG_COST    CG_VAL ITEM_ANLY_                                                 
------- ------------ --------- --------- ----------                                                 
JED_OC  11VST053301          2         4 11VST0533                                                  

SQL> ED
Wrote file afiedt.buf

  1  SELECT X.CG_GROUP,X.CG_ITEM,X.CG_COST,X.CG_VAL,Y.ITEM_ANLY_05 , 
(SELECT A.CG_ITEM FROM OS_CST_GRP_VAL A,ITEM_MAST B
  2  WHERE A.CG_ITEM = B.ITEM_cODE
  3  AND B.ITEM_ANLY_05 = Y.ITEM_ANLY_05)
  4   FROM
  5  OS_CST_GRP_VAL X ,ITEM_MAST Y
  6  WHERE X.CG_ITEM = Y.ITEM_CODE
  7* AND X.CG_GROUP='JED_OC'
SQL> /
SELECT X.CG_GROUP,X.CG_ITEM,X.CG_COST,X.CG_VAL,Y.ITEM_ANLY_05 , 
(SELECT A.CG_ITEM FROM OS_CST_GRP_VAL A,ITEM_MAST B
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row 

--I Want to have the output with only COST_GROUP JED_OC and the other cost groups to be 
repeated along with that as details record 
--wht i need is as below like a grouping what we do in a report using data link

X.CG_GROUP ,X.CG_COST,X.CG_ITEM , Y.ITEM_ANLY_05 , A.CG_GROUP,A.CG_VAL,A.CG_ITEM
JED_OC       2         11VST053301     11VST0533    JED_CS     20      11VST053302
                                                    JED_CS     30      11VST053303

[Updated on: Sun, 10 June 2012 09:40] by Moderator

Report message to a moderator

Re: Complex query [message #557194 is a reply to message #557185]

Sun, 10 June 2012 16:48

Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> BREAK ON cg_group1 ON cg_cost ON cg_item1 ON item_anly_05
SCOTT@orcl_11gR2> SELECT t1.cg_group cg_group1, t1.cg_cost, t1.cg_item cg_item1, t1.item_anly_05,
  2  	    t2.cg_group cg_group2, t2.cg_val,  t2.cg_item cg_item2
  3  FROM   (SELECT X.CG_GROUP, X.CG_COST, X.CG_ITEM, Y.ITEM_ANLY_05, x.cg_val
  4  	     FROM   OS_CST_GRP_VAL X ,ITEM_MAST Y
  5  	     WHERE  X.CG_ITEM = Y.ITEM_CODE
  6  	     AND    X.CG_GROUP = 'JED_OC') t1,
  7  	    (SELECT a.cg_group, a.cg_val, A.CG_ITEM, b.item_anly_05
  8  	     FROM   OS_CST_GRP_VAL A, ITEM_MAST B
  9  	     WHERE  A.CG_ITEM = B.ITEM_cODE) t2
 10  WHERE  t1.item_anly_05 = t2.item_anly_05
 11  AND    t1.cg_val != t2.cg_val
 12  ORDER  BY t1.cg_group, t1.cg_cost, t1.cg_item, t1.item_anly_05, t2.cg_val
 13  /

CG_GROU    CG_COST CG_ITEM1     ITEM_ANLY_05 CG_GROU     CG_VAL CG_ITEM2
------- ---------- ------------ ------------ ------- ---------- ------------
JED_OC           2 11VST053301  11VST0533    JED_CS          20 11VST053302
                                             JED_CS          30 11VST053303

2 rows selected.

Report message to a moderator

Re: Complex query [message #557207 is a reply to message #557194]

Mon, 11 June 2012 00:37

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Thanks very much barbara, this is what exactly i want as output , but i need to take this output into excel directly from sql plus, is there any easy method.

Report message to a moderator

Re: Complex query [message #557214 is a reply to message #557207]

Mon, 11 June 2012 01:13

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Use UTL_FILE..

Report message to a moderator

Re: Complex query [message #557216 is a reply to message #557214]

Mon, 11 June 2012 01:16

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

kindly give me the steps to use utl_file murali

Report message to a moderator

Re: Complex query [message #557218 is a reply to message #557216]

Mon, 11 June 2012 01:21

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

In DB ,is there any oracle external directory...Use below command and output past it

select * from all_directories

Report message to a moderator

Re: Complex query [message #557220 is a reply to message #557218]

Mon, 11 June 2012 01:40

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

dear murali, please find below the required information.

SQL> /

OWNER                          DIRECTORY_NAME                                                       
------------------------------ ------------------------------                                       
DIRECTORY_PATH                                                                                      
----------------------------------------------------------------------------------------------------
SYS                            SUBDIR                                                               
D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep                                     
                                                                                                    
SYS                            XMLDIR                                                               
D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\                                              
                                                                                                    
SYS                            MEDIA_DIR                                                            
D:\oracle\product\10.2.0\db_1\demo\schema\product_media\                                            
                                                                                                    
SYS                            LOG_FILE_DIR                                                         
D:\oracle\product\10.2.0\db_1\demo\schema\log\                                                      
                                                                                                    
SYS                            WORK_DIR                                                             
C:\ADE\aime_sel928\oracle/work                                                                      
                                                                                                    
SYS                            DATA_FILE_DIR                                                        
D:\oracle\product\10.2.0\db_1\demo\schema\sales_history\                                            
                                                                                                    
SYS                            DATA_PUMP_DIR                                                        

OWNER                          DIRECTORY_NAME                                                       
------------------------------ ------------------------------                                       
DIRECTORY_PATH                                                                                      
----------------------------------------------------------------------------------------------------
D:\oracle\product\10.2.0/admin/ora10g/dpdump/                                                       
                                                                                                    
SYS                            ADMIN_DIR                                                            
C:\ADE\aime_sel928\oracle/md/admin                                                                  
                                                                                                    

8 rows selected.

Report message to a moderator

Re: Complex query [message #557231 is a reply to message #557220]

Mon, 11 June 2012 03:33

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Sorry for late replay...

declare
  TYPE type_ename   IS TABLE OF VARCHAR2(1000);
  TYPE type_empno    IS TABLE OF NUMBER;
  t_ename          type_ename;
  t_empno          type_empno;
 ft_logFile                UTL_FILE.FILE_TYPE; 
 v_table_query varchar2(1000);
 
 TYPE cur_typ              IS REF CURSOR;
  c_table_data              cur_typ;
begin

  ft_logFile  := utl_file.fopen('XMLDIR','test.txt','W',32767);
  v_table_query :='select ename,empno from emp';
  
  OPEN c_table_data FOR v_table_query;
  LOOP
      FETCH c_table_data BULK COLLECT INTO t_ename,t_empno LIMIT 1000;
      EXIT WHEN  t_ename.COUNT <= 0 AND c_table_data%NOTFOUND;
     FOR idx in t_ename.FIRST..t_ename.LAST LOOP         
        utl_file.putf(ft_logFile,'%s\n',t_ename(idx)||','||t_empno(idx) );       
      END LOOP;
  END LOOP;
end;
/

Report message to a moderator

Re: Complex query [message #557233 is a reply to message #557231]

Mon, 11 June 2012 03:43

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

thanks murali, i will check this code, actually what i want is we are using break on command in sql plus to format the query to remove duplicates , can we do that in toad 9, if so kindly do let me know otherwise i have to create a report and take it to excel using rpt2xls tool etc its a long process as i need the same formatting sql plus output in toad too.

Report message to a moderator

Re: Complex query [message #557239 is a reply to message #557233]

Mon, 11 June 2012 04:04

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

not clear?
Quote:

what i want is we are using break on command in sql plus to format the query to remove duplicates

Report message to a moderator

Re: Complex query [message #557240 is a reply to message #557239]

Mon, 11 June 2012 04:07

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

i have one query written with the help of barbara , and i am running that in sql plus , in sql plus the break on command is working and now i want to use this break on in toad also so that i can upload the data format to excel directly.

SCOTT@orcl_11gR2> BREAK ON cg_group1 ON cg_cost ON cg_item1 ON item_anly_05
SCOTT@orcl_11gR2> SELECT t1.cg_group cg_group1, t1.cg_cost, t1.cg_item cg_item1, t1.item_anly_05,
  2  	    t2.cg_group cg_group2, t2.cg_val,  t2.cg_item cg_item2
  3  FROM   (SELECT X.CG_GROUP, X.CG_COST, X.CG_ITEM, Y.ITEM_ANLY_05, x.cg_val
  4  	     FROM   OS_CST_GRP_VAL X ,ITEM_MAST Y
  5  	     WHERE  X.CG_ITEM = Y.ITEM_CODE
  6  	     AND    X.CG_GROUP = 'JED_OC') t1,
  7  	    (SELECT a.cg_group, a.cg_val, A.CG_ITEM, b.item_anly_05
  8  	     FROM   OS_CST_GRP_VAL A, ITEM_MAST B
  9  	     WHERE  A.CG_ITEM = B.ITEM_cODE) t2
 10  WHERE  t1.item_anly_05 = t2.item_anly_05
 11  AND    t1.cg_val != t2.cg_val
 12  ORDER  BY t1.cg_group, t1.cg_cost, t1.cg_item, t1.item_anly_05, t2.cg_val
 13  /

CG_GROU    CG_COST CG_ITEM1     ITEM_ANLY_05 CG_GROU     CG_VAL CG_ITEM2
------- ---------- ------------ ------------ ------- ---------- ------------
JED_OC           2 11VST053301  11VST0533    JED_CS          20 11VST053302
                                             JED_CS          30 11VST053303

2 rows selected.

Report message to a moderator

Re: Complex query [message #557246 is a reply to message #557240]

Mon, 11 June 2012 04:23

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

You can try spool on..option..

Report message to a moderator

Re: Complex query [message #557253 is a reply to message #557246]

Mon, 11 June 2012 05:14

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

you mean to say open the spool file directly in excel.

Report message to a moderator

Re: Complex query [message #557254 is a reply to message #557253]

Mon, 11 June 2012 05:17

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

if somebody gave any suggestions,just try let me know.. Did you tried spool? working?

Report message to a moderator

Re: Complex query [message #557262 is a reply to message #557254]

Mon, 11 June 2012 06:20

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

murali it did'nt work with spool , i tried with opening lst as delimited but no the format is changed, i hope only barbara can help us.

Report message to a moderator

Re: Complex query [message #557263 is a reply to message #557262]

Mon, 11 June 2012 06:22

cookiemonster
Messages: 9147
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why not jst set colsep to , in sqlplus and spool the output to a csv file.

Report message to a moderator

Re: Complex query [message #557264 is a reply to message #557263]

Mon, 11 June 2012 06:42

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

it did'nt work when i changed it to csv the output is attached.kindly brief me the steps cookiemonster.
./fa/10237/0/

[mod-edit: image inserted into message body by bb]

Attachment: output.png
(Size: 232.12KB, Downloaded 294 times)

[Updated on: Mon, 11 June 2012 07:59] by Moderator

Report message to a moderator

Re: Complex query [message #557266 is a reply to message #557264]

Mon, 11 June 2012 06:50

cookiemonster
Messages: 9147
Registered: September 2008
Location: Rainy Manchester

Senior Member

You'll want the following sets for starters:

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF

Report message to a moderator

Re: Complex query [message #557274 is a reply to message #557233]

Mon, 11 June 2012 08:11

Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA

Senior Member

arif_md2009 wrote on Mon, 11 June 2012 01:43

... what i want is we are using break on command in sql plus to format the query to remove duplicates , can we do that in toad 9 ... as i need the same formatting sql plus output in toad too.

SCOTT@orcl_11gR2> SELECT DECODE (rn, 1, cg_group1, NULL) cg_group1,
  2  	    DECODE (rn, 1, cg_cost, NULL) cg_cost,
  3  	    DECODE (rn, 1, cg_item1, NULL) cg_item1,
  4  	    DECODE (rn, 1, item_anly_05, NULL) item_anly_05,
  5  	    cg_group2, cg_val, cg_item2
  6  FROM   (SELECT t1.cg_group cg_group1, t1.cg_cost, t1.cg_item cg_item1, t1.item_anly_05,
  7  		    t2.cg_group cg_group2, t2.cg_val,  t2.cg_item cg_item2,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY t1.cg_group, t1.cg_cost, t1.cg_item, t1.item_anly_05
 10  		       ORDER BY t2.cg_val) rn
 11  	     FROM   (SELECT X.CG_GROUP, X.CG_COST, X.CG_ITEM, Y.ITEM_ANLY_05, x.cg_val
 12  		     FROM   OS_CST_GRP_VAL X ,ITEM_MAST Y
 13  		     WHERE  X.CG_ITEM = Y.ITEM_CODE
 14  		     AND    X.CG_GROUP = 'JED_OC') t1,
 15  		    (SELECT a.cg_group, a.cg_val, A.CG_ITEM, b.item_anly_05
 16  		     FROM   OS_CST_GRP_VAL A, ITEM_MAST B
 17  		     WHERE  A.CG_ITEM = B.ITEM_cODE) t2
 18  	     WHERE  t1.item_anly_05 = t2.item_anly_05
 19  	     AND    t1.cg_val != t2.cg_val)
 20  ORDER  BY 1, 2, 3, 4, 5, 6
 21  /

CG_GROU    CG_COST CG_ITEM1     ITEM_ANLY_05 CG_GROU     CG_VAL CG_ITEM2
------- ---------- ------------ ------------ ------- ---------- ------------
JED_OC           2 11VST053301  11VST0533    JED_CS          20 11VST053302
                                             JED_CS          30 11VST053303

2 rows selected.

[Updated on: Mon, 11 June 2012 08:13]

Report message to a moderator

Re: Complex query [message #557277 is a reply to message #557274]

Mon, 11 June 2012 08:23

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

thanks very much barbara , this is what exactly i want , you are amazing !!!

Report message to a moderator

Re: Complex query [message #557281 is a reply to message #557274]

Mon, 11 June 2012 09:08

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Hi barbara sorry for troubling you , there is one small problem as i have posted one test case , but there may be multiple records in the table here i have added more inserts and when i ran the query its comming as below i want it like a grouping, In simple i want to run the query without BREAK ON option and get the output similar to BREAK ON.


INSERT INTO ITEM_MAST VALUES ('11VST053401','11VST0534','KGS');

COMMIT;

INSERT INTO ITEM_MAST VALUES ('11VST053402','11VST0534','KGS');

COMMIT;

INSERT INTO ITEM_MAST VALUES ('11VST053403','11VST0534','KGS');

COMMIT;


insert into os_cst_grp_val values ('JED_OC','11VST053401',3,5);

COMMIT;

insert into os_cst_grp_val values ('JED_CS','11VST053402',10,10);

COMMIT;

insert into os_cst_grp_val values ('JED_CS','11VST053403',11,11);

COMMIT;

SQL> SELECT * FROM ITEM_MAST;

ITEM_CODE    ITEM_ANLY_ ITE
------------ ---------- ---
11VST053301  11VST0533  KGS
11VST053302  11VST0533  KGS
11VST053303  11VST0533  KGS
11VST053401  11VST0534  KGS
11VST053402  11VST0534  KGS
11VST053403  11VST0534  KGS

SQL> SELECT * FROM OS_CST_GRP_VAL;

CG_GROU CG_ITEM        CG_COST    CG_VAL
------- ------------ --------- ---------
JED_OC  11VST053301          2         4
JED_CS  11VST053302         10        20
JED_CS  11VST053303         20        30
JED_OC  11VST053401          3         5
JED_CS  11VST053402         10        10
JED_CS  11VST053403         11        11

-- When i ran the query

SQL> SPOOL C:\QRY_MULTI
SQL> SELECT DECODE (rn, 1, cg_group1, NULL) cg_group1,
  2              DECODE (rn, 1, cg_cost, NULL) cg_cost,
  3              DECODE (rn, 1, cg_item1, NULL) cg_item1,
  4              DECODE (rn, 1, item_anly_05, NULL) item_anly_05,
  5              cg_group2, cg_val, cg_item2
  6      FROM   (SELECT t1.cg_group cg_group1, t1.cg_cost, t1.cg_item cg_item1, t1.item_anly_05,
  7                  t2.cg_group cg_group2, t2.cg_val,  t2.cg_item cg_item2,
  8                  ROW_NUMBER () OVER
  9                    (PARTITION BY t1.cg_group, t1.cg_cost, t1.cg_item, t1.item_anly_05
 10                    ORDER BY t2.cg_val) rn
 11              FROM   (SELECT X.CG_GROUP, X.CG_COST, X.CG_ITEM, Y.ITEM_ANLY_05, x.cg_val
 12                  FROM   OS_CST_GRP_VAL X ,ITEM_MAST Y
 13                  WHERE  X.CG_ITEM = Y.ITEM_CODE
 14                  AND    X.CG_GROUP = 'JED_OC') t1,
 15                 (SELECT a.cg_group, a.cg_val, A.CG_ITEM, b.item_anly_05
 16                  FROM   OS_CST_GRP_VAL A, ITEM_MAST B
 17                  WHERE  A.CG_ITEM = B.ITEM_cODE) t2
 18              WHERE  t1.item_anly_05 = t2.item_anly_05
 19              AND    t1.cg_val != t2.cg_val)
 20     ORDER  BY 1, 2, 3, 4, 5
 21  /

CG_GROU   CG_COST CG_ITEM1     ITEM_ANLY_ CG_GROU    CG_VAL CG_ITEM2
------- --------- ------------ ---------- ------- --------- ------------
JED_OC          2 11VST053301  11VST0533  JED_CS         20 11VST053302
JED_OC          3 11VST053401  11VST0534  JED_CS         10 11VST053402
                                          JED_CS         11 11VST053403
                                          JED_CS         30 11VST053303


--actaull i want it this way below

CG_GROU   CG_COST CG_ITEM1     ITEM_ANLY_ CG_GROU    CG_VAL CG_ITEM2
------- --------- ------------ ---------- ------- --------- ------------
JED_OC          2 11VST053301  11VST0533  JED_CS         20 11VST053302
                                          JED_CS         10 11VST053402
JED_OC          3 11VST053401  11VST0534  JED_CS         11 11VST053403
                                          JED_CS         30 11VST053303

Report message to a moderator

Re: Complex query [message #557293 is a reply to message #557281]

Mon, 11 June 2012 10:34

Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA

Senior Member

Add an alias for the sub-query and change the order by clause, as shown below.

SCOTT@orcl_11gR2> SELECT DECODE (rn, 1, cg_group1, NULL) cg_group1,
  2  	    DECODE (rn, 1, cg_cost, NULL) cg_cost,
  3  	    DECODE (rn, 1, cg_item1, NULL) cg_item1,
  4  	    DECODE (rn, 1, item_anly_05, NULL) item_anly_05,
  5  	    cg_group2, cg_val, cg_item2
  6  FROM   (SELECT t1.cg_group cg_group1, t1.cg_cost, t1.cg_item cg_item1, t1.item_anly_05,
  7  		    t2.cg_group cg_group2, t2.cg_val,  t2.cg_item cg_item2,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY t1.cg_group, t1.cg_cost, t1.cg_item, t1.item_anly_05
 10  		       ORDER BY t2.cg_val) rn
 11  	     FROM   (SELECT X.CG_GROUP, X.CG_COST, X.CG_ITEM, Y.ITEM_ANLY_05, x.cg_val
 12  		     FROM   OS_CST_GRP_VAL X ,ITEM_MAST Y
 13  		     WHERE  X.CG_ITEM = Y.ITEM_CODE
 14  		     AND    X.CG_GROUP = 'JED_OC') t1,
 15  		    (SELECT a.cg_group, a.cg_val, A.CG_ITEM, b.item_anly_05
 16  		     FROM   OS_CST_GRP_VAL A, ITEM_MAST B
 17  		     WHERE  A.CG_ITEM = B.ITEM_cODE) t2
 18  	     WHERE  t1.item_anly_05 = t2.item_anly_05
 19  	     AND    t1.cg_val != t2.cg_val) t3
 20  ORDER  BY t3.cg_group1, t3.cg_cost, t3.cg_item1, t3.item_anly_05, t3.cg_group2, t3.cg_val
 21  /

CG_GROU    CG_COST CG_ITEM1     ITEM_ANLY_05 CG_GROU     CG_VAL CG_ITEM2
------- ---------- ------------ ------------ ------- ---------- ------------
JED_OC           2 11VST053301  11VST0533    JED_CS          20 11VST053302
                                             JED_CS          30 11VST053303
JED_OC           3 11VST053401  11VST0534    JED_CS          10 11VST053402
                                             JED_CS          11 11VST053403

4 rows selected.

Report message to a moderator

Re: Complex query [message #557299 is a reply to message #557293]

Mon, 11 June 2012 13:39

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

thanks very much barbara,i will change the code and re run the query.

Report message to a moderator

Re: Complex query [message #559855 is a reply to message #557231]

Fri, 06 July 2012 15:23

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

wshere can we define the path to store this text file.like 'c:\'

Report message to a moderator

Re: Complex query [message #559857 is a reply to message #559855]

Fri, 06 July 2012 18:30

Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA

Senior Member

arif_md2009 wrote on Fri, 06 July 2012 13:23

wshere can we define the path to store this text file.like 'c:\'

It depends on what you are running it from and on what operating system. From SQL*Plus on Windows:

spool drive_letter:\directory_path\file_name.txt
select ... ;
spool off

Report message to a moderator

Previous Topic:	numbers to words
Next Topic:	create insert script

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 16:06:59 CDT 2013

Total time taken to generate the page: 1.31109 seconds