Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle row fetching in a table - Syntax Issue

Posted on 2015-01-12
12
Medium Priority
?
175 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 78

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 78

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 78

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 78

Expert Comment

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

886 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