Solved

Oracle row fetching in a table - Syntax Issue

Posted on 2015-01-12
12
165 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 76

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
 
LVL 76

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

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 76

Expert Comment

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

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Read about achieving the basic levels of HRIS security in the workplace.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now