Solved

Oracle row fetching in a table - Syntax Issue

Posted on 2015-01-12
12
171 Views
Last Modified: 2015-01-13
This is in Oracle 11g.My Minus query - Returns 101 row of records.
Each svc_pvr_pyr_nr   value has 16 row of records.
Minus query fetches only distinct svc_pvr_pyr_nr ( Which is good !!).

select svc_pvr_pyr_nr  from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '96' 


minus

select  svc_pvr_pyr_nr from tcomgrp 
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '0' 

Open in new window


on fetching svc_pvr_pyr_nr  , i need to get all columns for the corresponding svc_pvr_pyr_nr  for the
dat_cx_tm_prd_nr  = 96 and then insert into Temp Table.

select svc_pvr_pyr_nr  from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '96' 

Open in new window


Expected output should be 101 * 16 = 1616 row of records.
0
Comment
Question by:chokka
  • 7
  • 5
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40544616
insert into some_table
select * from some_other_table where svc_pvr_pyr_nr   in
(
select svc_pvr_pyr_nr  from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '96'
)
0
 

Author Comment

by:chokka
ID: 40544633
This some_table is already existing table or i have create new ????
0
 

Author Comment

by:chokka
ID: 40544639
I ran the query, It throws exception saying that - Temp Table has to be created.

insert into some_table
select * from some_other_table where svc_pvr_pyr_nr   in 
(
select svc_pvr_pyr_nr  from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '85' 


minus

select  svc_pvr_pyr_nr from tcomgrp 
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '0' 

) 

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40544645
>>This some_table is already existing table or i have create new ????

You stated "and then insert into Temp Table" but failed to provide the table's name.  I needed a table name for the insert statement, so I made one up.

If you don't already have the "temp table", of course you need to create it.
0
 

Author Comment

by:chokka
ID: 40544667
Yes, I need a Temp Table. My Actual issue to create the Temp Table - Insert into the Temp Table.

All has to take place in one sequence of query.  Along with the query which i provided.

Is it possible, we can do it in Oracle ???
0
 

Author Comment

by:chokka
ID: 40544670
Below query has to be inserted directly to a Temp Table in Oracle


select * from some_other_table where svc_pvr_pyr_nr   in
(
select svc_pvr_pyr_nr  from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '85'


minus

select  svc_pvr_pyr_nr from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '0'

)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40544676
From your previous question:
create table some_temp_table as (
select ...  -whatever select you have.
);

Also from your previous question:
I would not do this.  I would create a global temporary table and do the insert.
0
 

Author Comment

by:chokka
ID: 40544702
On running the query, Query fails. I need Select * from. where svc_pvr_pyr_nr  has to be fetched from Minus Query results.


I am actually iterating row of records in the same table : tcomgrp

select * from tcomgrp where svc_pvr_pyr_nr in

(

select svc_pvr_pyr_nr from tcomgrp

where sls_grp_cd ='IS'

and sales_vew_cd ='S1709'

and dat_cx_tm_prd_nr = '85' )

 

minus

(select svc_pvr_pyr_nr from tcomgrp

where sls_grp_cd ='IS'

and sales_vew_cd ='S1709'

and dat_cx_tm_prd_nr = '0'

)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40544708
Check your parenthesis.  Looks like you have a missing one or two.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40545554
Here is the paran issue:


select * from tcomgrp where svc_pvr_pyr_nr in
(
select svc_pvr_pyr_nr from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '85' 
minus
select svc_pvr_pyr_nr from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '0'
) 

Open in new window

0
 

Author Comment

by:chokka
ID: 40547309
Thank you slightwv !!

This Post is CLOSED. Please dont comment any further !!

I have a trouble on closing the post because of IE - Javascript Error. I will close it in another Browser.
0
 

Author Closing Comment

by:chokka
ID: 40548170
Thank you slightvw
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question