Sitemap

Amicuk Programming Answers

How to select Subpartition name in a Select query?

2015-10-11   Views:0

Advertisement

Hi, I have a table that is partitioned on date range and subpartitioned based on and ID list. Lets assume the table name is something like: MY_TABLE The partition name would look like: P_20110126160527 The subpartition list is as follows: GB, IN, AU,

Hi,
I have a table that is partitioned on date range and subpartitioned based on and ID list. Lets assume the table name is something like: MY_TABLE
The partition name would look like: P_20110126160527
The subpartition list is as follows: GB, IN, AU, US etc. The sub partition name for GB would look like
P_20110126160527_GB
I need to run a select query to fetch data from MY_TABLE along with Sub partition name. The result set needs to look like:
Name|Location|SubPartition
Sam|UK|P_20110126160527_GB
Tom|UK|P_20110126160527_GB
Dave|AU|P_20110126160527_AU
The data available in ALL_TAB_SUBPARTITIONS and USER_TAB_SUBPARTITIONS can't be used just because the only join condition available is the TABLE Name but we would also have to join on SUBPARTITION KEY. I am not sure how to achieve this.
Does anyone here have a clue?

The replay answer
Advertisement
In a pinch, you could do something like this.
select col1, col2, col3, 'PARTITION_1' from your_table where key_col in <values for partition_1>
union all
select col1, col2, col3, 'PARTITION_2' from your_table where key_col in <values for partition_2>
union all
select col1, col2, col3, 'PARTITION_3' from your_table where key_col in <values for partition_3>
union all
...Or better yet:
select col1, col2, col3, case when key_col = 'x' then 'PARTITION_1'
                              when key_col = 'y' then 'PARTITION_2'
                              when key_col = 'z' then 'PARTITION_3'
                         end
from ...Of course, none of these would be "dynamic".

Go to See the other 6 answers

How to select Subpartition name in a Select query?

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a table that is partitioned on date range and subpartitioned based on and ID list. Lets assume the table name is something like: MY_TABLE The partition name would look like: P_20110126160527 The subpartition list is as follows: GB, IN, AU,[More]

Select Query Problem

Category:DefaultRelease time:2015-10-11Views:130

Hi Experts, I am having a select query in which I am using a variable in the where condition but it is giving error. Please suggest how to use variable in the select query. The query I am using is a s below. select * from zexc_rec into table it_ZEXC_[More]

Oracle 11g :SELECT query blocked..??

Category:DefaultRelease time:2015-10-11Views:130

Hi Experts, could you please explain why the below SQL query is blocked? SELECT 1 FROM DUAL is blocking the SQL statement on [email protected] ( SID=469 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1 SELECT 1 FROM DUAL is blocking th[More]

Oracle SQL Select query takes long time than expected.

Category:DefaultRelease time:2015-10-11Views:130

Hi, I am facing a problem in SQL select query statement. There is a long time taken in select query from the Database. The query is as follows. select /*+rule */ f1.id,f1.fdn,p1.attr_name,p1.attr_value from fdnmappingtable f1,parametertable p1 where[More]

Select query differences between oracle 9 and oracle 8.

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a problem using the select query between oracle 7 and oracle 9i I don't have the same result : ex: With oracle7 SQL> select 'champ1','champ2' from DUAL; 'CHAMP 'CHAMP champ1 champ2 With Oracle 9 SQL> select 'champ1','champ2' from DUAL; 'C[More]

Select Query resulting in Scientific Notation

Category:DefaultRelease time:2015-10-11Views:130

Hello all, I am running a Select query through a batch file that extracts data from an Oracle database. Several of the fields that I am extracting from contain numbers that are up to 38 digits long. When I extract the data, it converts the numbers in[More]

Select-options in SELECT query - syntax error

Category:DefaultRelease time:2015-10-11Views:130

Hi all,   I get the error below when I try to use the select options in a SELECT query . Please help me. "The IN operator with "SO_AWART" is followed neither by an internal table nor by a value list." The code i have used(Logical datab[More]

Query in Select query operation

Category:DefaultRelease time:2015-10-11Views:130

Please excuse me for posting this again. I'm stuck up with this for 3 days. Lenghty post pls do read and help me. How is the select query going to be changed in the SAP so that the original database behind it understands it. I want to know the operat[More]

SELECt query in UNiX environment

Category:DefaultRelease time:2015-10-11Views:130

Iam using hp unix with oracle 11g when i try to spool 26 lack records it is giving me error " There is not enough memory available now. O/S Message: Broken pipe" is there is any other way SQL commands, we can limit the SELECT query to spool spec[More]

Select query in materialized view with two dblinks

Category:DefaultRelease time:2015-10-11Views:130

Hi All, We have oracle 10g On windows. We are trying to create materialized view. Scenario is we have base table on other database and we are creating mview on different database. Basa database have two schema's and i am selecting records from that t[More]

Select query in MB51 failing only for posting date(BUDAT)02/02/2010

Category:DefaultRelease time:2015-10-11Views:130

Hello Experts, I am having below select query. It is running perfectly fine for all the dates except 02/02/2010.On all other dates it is getting executed with in second. For 02/02/2010 it keeps on running for more than 10 minutes and then fails with[More]

Hot
Hi Experts My client has a  scenario where for certain items they create a credit memo request document and finally a credit memo document from the VKOA system is picking the revenue bearing GL and posting all the line items to that G/L account Manua [More]
Hi, Client is asking a Balance sheet. In that Balance Sheet, he wants plant wise break up for Inventory Finished Goods GL Account. For Example: Inventory finished goods  - Plant 1100 = 120000 Inventory finished goods  - Plant 1900 = 110000 How can we [More]
Hi, I want to display a bar chart(not column) that allows a pareto sorting of the  bar values on the Y axis. Any suggestions?Hi Joe, In Reporting Services, a Pareto chart can be defined as a column chart with the columns sorted in descending order, b [More]
Hi, I am working on a SQL, please help ms with the question below .... thanks (1)Increase by 10% salary of these captain pilots who have traveled more than 800,000 miles. Routes | | Flights | |Pilots | | | | | | #routeID | | #flightNO | |#pilotID | d [More]
hi, pl tell me a link through table and field - how to get a link between sale ord - item no. - mtrl - prodn ord no. - prodn posted qty. regardsI have an understanding that you are in need of the tables for mat.no,prod.ord.and sales order to make a r [More]
My post is not, strictly speaking, about using Snow Leopard. But that's what I'm using and this forum comes closest to my question. The recent gas line explosion in San Bruno, CA (where houses were destroyed in a matter of seconds) made me think agai [More]
Hello everybody, since we are using SP12 we have problems with the HTTP sender when using EOIO. If I use the two parameters ....qos=EOIO&queueid=myQueue... I got a HTTP Reponse Code 500: Internal Server Error in the sender system (for the test I use [More]
Hi, We have WD form we are calling from GP. We are implementing a cancel button and stuck with the functionality. Already created a sdn post on WD Java forum no results. We thought since the call to the form originates from a GP interface may be you [More]
I am having major problems with my Motion program. I suspect it is the fact I have the NVIDIA GeForce 7300 GT graphic card. I want to know what cards will prevent my crashing issues. Will the ATI Radeon XT1900 help fix my problems? or do I need to ge [More]
Is it possible to use the excitation voltage from one channel, as an external voltage on three other channels? So that all stain gauges is using the same Vex Using following equipment: SCXI 1520, with SCXI 1314 terminal block. Larsit´s because we hav [More]