Solved

Oracle row fetching in a table - Syntax Issue

Posted on 2015-01-12
12
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to take different types of Oracle backups using RMAN.
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

617 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