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
How do I uninstall and reinstall PS after my hard drive has been erased?  I tried the uninstall icon but it came up as error and now is gone.Run the Creative Suite Cleaner Tool, thne reinstall. just be aware that you may have lost an activation and m [More]
My iMac won't let me update to OS X v10.6 Snow Leopard. It's year of manufacture is 2007 and when I try to install Snow Leopard it now says that it cannot be upgraded on the iMac. Normally it wouldn't bother me but Chrome and Firefox no longer suppor [More]
Okay, so my iPhone 5's passcode is not working, so I just tried to do recovery mode but when I try to connect to iTunes it says that I need to download the newest version of iTunes. I can't do so unless I can get into my phone. Anything I can do? I d [More]
I am having trouble with endnote numbers in Pages (v2.0.1). I have a large document with several sections (chapters) each with its own set of endnotes. What I would like to have is each chapter range from 1–n, then restart at 1 for the next chapter/s [More]
Hi using jdev 11.1.1.3 Can anyone suggest me a solution as to how I can skip validation programatically. I have both entity level and deffered entitylevel txn validations on this entity. I use the VO to populate the data and I modify the date field, [More]
I've had this happen twice now over the last 10 days. After the first time, I removed the whole program & re-installed from the disc. Its now just happened again and editor will not load & I just get the Runtime Error! notification. As before, I c [More]
what do i do..my imac will not power up...it comes on, and goes back off....HELPWouldn't that be like, telling you that your car need a new engine and transmission over the phone. Take it to your local Apple Store or AASP and have them diagnose the p [More]
Hello ODI expert(s), I'm new to ODI & hence experimenting. I'm trying to load metadata from a SQL Server view to Oracle's Hyperion Planning. My question is w.r.t source. I've 6 columns in my source view. All 6 were mapped to the appropriate target fi [More]
Hi, I have a column which has some flag say 'Y' or 'N'. I have created an advanced table and followed the dev guide for aligning the value in the center of the cell.But i m getting null pointer exception when I m using getColumnFormats method of adva [More]
Hi i want to upload data from a flat file to a table. i dont know the exact file name. i want to make a search for filename. like i want to make a search on file (say test*) which will give me all the files with test. i want to upload data using thes [More]