Oracle row fetching in a table - Syntax Issue

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.
chokkaStudentAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
chokkaStudentAuthor Commented:
This some_table is already existing table or i have create new ????
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
chokkaStudentAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
chokkaStudentAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Check your parenthesis.  Looks like you have a missing one or two.
0
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
Thank you slightvw
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.