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 all, can any one pls tell me  by using which method or class  i can set the pf status......?   regards cnuFast and Easy way As you are using OO ALV, remove any function not needed from the ALV via parameter IT_TOOLBAR_EXCLUDING of method set_table [More]
I am not a garageband user in the past so please bear with me. I want to record from the built-in mic and I did and there was a obvious record patten on the basic track. BUT No Sound, I thought maybe the file was wrong I then recorded an itunes and t [More]
Ok.  I am working with the fluid grid system in DW CS6. in the broiler plate.css file i see the below code  which seems to control the styling for the listed html 5 elements. HTML5 display definitions    ============================================== [More]
I have Windows XP 32bit system. I am having a problem with the latest Flash Player. The problem occurs when entering the PC to play yahoo Canasta. All information required  is entered and relevant windows are opened with correct data. When I click to [More]
Hi All, I need to come out with the IO tracking report for IO in DBM order (status is open for IO header or IO split). Appreciate if anyone could assist me to provide the table name for IO status. ThanksHi Please check the tables... VBUK - for Header [More]
All, I am trying to find a BAPI or function module that can attach a PRT (DMS Document PRT to be specific) to an existing task list. Can anyone please provide the correct FM or BAPI?  What Package would FM's for PRT's be stored under?Hi, To search BA [More]
Hello Oracle, The following are the documents I had tried to download. http://download.oracle.com/docs/pdf/B13848_02.pdf http://download.oracle.com/docs/pdf/B13670_03.pdf http://download.oracle.com/docs/pdf/B10929_01.pdf http://download.oracle.com/do [More]
I'm using Ubuntu 14. Ever since the last few days (say, a week) i've been getting messages saying that Flash is outdated, and firefox blocks the plugin, so I have to unblock ("allow") every time I get to a new page. It's no big deal, but it's an [More]
Hello I Would like to create new app for iphone however I don't have Macbook laptop Is it possible to make app on windows ? Thank you  for helping .Xcode is Mac only. Matt Message was edited by: Matthew MorganRead other 3 answers [More]
Hi guys! I have this weird problem: I have a program that does some updates to standard tables. The program runs ok, but if I comment the "update" lines, the execution ends with this dump: TSV_TNEW_PAGE_ALLOC_FAILED With the UPDATE doesn't cance [More]