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
After Upgrading to Mountain Lion, I can't access the app store, itunes store, Safari - anything that requires the internet.  I tried resetting my modem and rebooting. I also tried deleting items from the keychain that look like they are related to th [More]
Hi,     There was ios 4.1 in my iphone3gs.i have tried to update it through itunes but there is a problem.After installation through itunes,there is problem that it is not activating my iphone and shows a message that your iphone could not be activat [More]
I created a temporary web site using GoLive. I imported a short movie in 2 different versions (1 medium for DSL/Cable and 1 small for Dial-up). When I tested the site either in Explorer or Safari on different Mac computers at home, they both loaded a [More]
i just got my first ipad & a lot of my fav sites dont work without Adobe Flash - is there a version for the ipad 3 that i can download? taNo, flash is not supported on the iPad - and as Adobe have announced that they are stopping development on all m [More]
< MODERATOR:  Message locked.  Please post this message in the [Controlling forum|SAP ERP Financials - Controlling;. > hi     while accessing t code ksb1 report iam geting this error message Message no. KB420 Diagnosis No line items were found that [More]
Hi, I have a JMS receiver channel which is connected to message queue of TIBCO which is working fine. But if I use the same channel after a day it goes into error and after restart of the channel it again works fine. Error: Message process failed. Ca [More]
Hi, I have a new requirement in application, Where user will be uploading and download file upto 150MB which is the best method to do need a suggestion which will not affect the database performance. Need a best way to do in apex to upload and downlo [More]
i have a iphone5. Want to add all music from medai player on pc. can i do it?Import the music to iTunes and sync it using iTunes.Read other 3 answers [More]
WHENVER WE WANT TO ACCESS FILES FROM OHTER APPLICATIONS,WHICH ONE IS BETTER TO USE,<c:redirect> OR RequestDispatcher<c:redirect> is the JSTL equivalent of the sendRedirect() method of javax.servlet.http.HttpServletResponse. Please see here: ht [More]
I have a web page I am building of some of my old pictures from my pro days. I really like some of the gallery output from Lightroom but if you look here and browse a little My Assignments you can see that a large part of the pages are anecdotes abou [More]