SQL & PL/SQL :: Column Reverse In Record?

Nov 7, 2011

I am trying to reverse data from couple record. I have display the output below of how I want it to be and is it possible to do it?

Quote:

This is how it is right now

ID|FROM_UOM_KEY|UOM_KEY|UOM|QUANTITY
1 |PK |PK |PK |1
2 |EA |PK |PK |3
3 |PK |PK |PK |1
4 |EA |PK |PK |3

I want it to be like this.

ID|FROM_UOM_KEY|UOM_KEY|UOM|QUANTITY
1 |EA |PK |PK |1
2 |PK |PK |PK |3
3 |EA |PK |PK |1
4 |PK |PK |PK |3

Here is the code

SELECT
from_uom_key,
vip.UOM_KEY,
--DECODE ( from_uom_key,from_uom_key,vip.UOM_KEY,from_uom_key),

[Code]....

View 21 Replies


ADVERTISEMENT

When To Use A Reverse Key Index

Jul 3, 2012

I've read some documentation about the reverse key indexes, but I haven't understood when really it can be useful use them. when it's smart to use them according to your experiences?

View 4 Replies View Related

PL/SQL :: Reverse Search In String

Aug 13, 2012

I have table having a single column a, which contain the values "HP Laptop", can search it in either way , i mean to say either user input the HP Laptop or Laptop HP? is it possible via SQL query ?

View 11 Replies View Related

Create Reverse Key Index On Table

Feb 25, 2013

difference between the ways we create Reverse Key Index on the table.The two ways are as below:

1) CREATE INDEX <Index_Name> ON <Table_Name>(<Column_Name) REVERSE ;
2) CREATE INDEX <Index_Name> ON<Table_Name>(REVERSE(<Column_Name>)) ;

Which one is the appropraite one.
Are both going to act in the same way.

I created index in both ways one by one on the table.But when I fire the select statement against the same table, The explain plan doesn't show any cost and it shows the full table scan in both ways.The select statement used is as below...I want to compare the column with string RBO(i.e('RBO%') at the start.

SELECT *
FROM <Table_Name>
WHERE REVERSE (<Column_Name>) LIKE '%OBR' ;

The select statement is giving me the correct result. Only the explain plan is showing Full Table Scan.can we use this reverse with IN operator also in the same way by reversing the values.

View 3 Replies View Related

Forms :: Why Arabic Comma Reverse Text

Mar 2, 2012

The Text is displayed correctly when there is no "Arabic comma" ، , otherwise the text will be reversed, How can I avoid the issue, why we have such problem only when we add the comma. The screenshot: s10.postimage.org/8qw1l8621/Arabic_comma.png

View 3 Replies View Related

SQL & PL/SQL :: Search String From The End To Beginning (reverse INSTR)?

Sep 22, 2010

Trying to get a number out of an error backtrack

06512: at "SCHEMA.PROCEDURE", line 4

I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.

The database languages might differ so I can't do this using

v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)

As it would not find any 'line' inside the string.

View 7 Replies View Related

PL/SQL :: Reverse Update - Create Synonym In Different Schema

Aug 8, 2012

I have table a in database 1 . Now i have created the MV for table a in database 2.

then i am going to create a synonym in different schema in the database 2.

Now i want to update the synonym in database 2 the same as to refresh in table a database 1.

How to achieve this??

View 2 Replies View Related

SQL & PL/SQL :: Order Of Bytes Returned By Dump() Function In Reverse Key Indexes?

May 28, 2013

Oracle Version: 11gR2: 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 X86_64

Currently, I'm reading the online book Oracle Concepts, Chapter 3: 3 Indexes and Index-Organized Tables, section: Reverse Key Indexes in order to understand this topic.

As I understand for each pair of (key, rowid) in the index structure, the rowid for each row in the table obviously remains the same but the bytes of the key are reversed before the key is stored. So for example on a 32 bit machine (just an example) a key = 10 AB CD EF will be stored as FE DC BA 01 , am I right?

According to the documentation, this becomes interesting in RAC environments in order to remove a hot spot from the index (when multiple instances repeatedly modify the same block) with the disadvantage that in some cases there cannot be Index Range Scan any more as data in the index is not sorted by column key when it is stored.

I was just curious to see how bytes of each key are reversed and after a bit googling I found an article where Tom Kyte shows with an example by using dump function (which as I understand gives the internal representation of a given expression) the difference in the sequence of bytes. Here is the link

[URL]

So based on his instructions I tried to do my own test, yet I don't get the same result, that is, the bytes are not reversed for me once we rebuild the index by REVERSE key word.

I'm going to write down here the test that I did, where is/are my error(s)

Test Case:
(I use a copy of the employees table in hr sample schema)
SQL> CREATE TABLE emp_test AS SELECT * FROM hr.employees;
Table created.
SQL> CREATE INDEX emp_test_idx ON emp_test(first_name);

[code]...

Now, a test SQL Query using the index we've just defined (just for giving an example)

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

FIRST_NAME DUMP_RESULT
-------------- ----------------------------------
Kelly Typ=1 Len=5: 4b,65,6c,6c,79

[code]...

So, according to the above Execution plan, Oracle does an Index Range Scan using the index that I defined on my table that is, emp_test_idx. According to the output of dump, the key (first_name) in that index is stored (in terms of bytes) as 4b,65,6c,6c,79

SQL> SELECT chr(to_number('4b', 'xx')) ||
2 chr(to_number('65', 'xx')) ||
3 chr(to_number('6c', 'xx')) ||
4 chr(to_number('6c', 'xx')) ||
5 chr(to_number('79', 'xx')) first_name
6 FROM DUAL;

FIRST_NAME
--------------------
Kelly

Which as we can see corresponds to the first name 'Kelly', the first name we specified in the above SQL query.

Now let's rebuild the index

SQL> ALTER INDEX emp_test_idx REBUILD REVERSE;
Index altered.
SQL>

Once the index keys have been reversed, I run the very same query in order to see the difference

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

[code]...

So the second time after the index has been reversed, I still get the very same sequence of bytes, that is, 4b,65,6c,6c,79, whereas I expected to get 79,6c,6c,65,4b (that is, the reversed order of the initial bytes sequence)

View 8 Replies View Related

SQL & PL/SQL :: Record To Column Format

Feb 1, 2011

I have to display a record in column format. I have tried doing it using COUNT & DECODE as in below script.

SELECT COUNT (DECODE (a1.application_status, 21, a1.ppid, NULL)) cnt_app21,
COUNT (DECODE (a1.application_status, 22, a1.ppid, NULL)) cnt_app22
FROM (SELECT a.application_status application_status, a.ppid ppid
FROM vp_recon_upload_dtls a, vp_ppid_upload_info b

[Code]...

This gives output as:-

cnt_app21 cnt_app21
3 1

The second query is little different from above as it has 2 more tables joined and more conditions than first query.

SELECT SUM (DECODE (a2.application_status, 21, a2.rate_amount, NULL)) sum_app21,
SUM (DECODE (a2.application_status, 22, a2.rate_amount, NULL)) sum_app22
FROM (SELECT a.application_status application_status,
d.rate_amount rate_amount
FROM vp_recon_upload_dtls a,

[Code]....

Second query output is :-

SUM_APP21 SUM_APP21
222 111

My requirement is to display all the four values in one row as:-

cnt_app21 cnt_app21 SUM_APP21 SUM_APP21
3 1 222 111

One easy way is to create two views and select values from them.

But for some reason I have to write in a query as there would be more dynamic conditions added to it.

View 2 Replies View Related

SQL & PL/SQL :: Getting Back Whole Record Then Picking 1 Column?

Jul 22, 2010

I want a function that'll execute a query and return the whole table record then i need to somehow pick a column in that record and return the data in that column

So like

function something(p_param varchar2)
return table_record
something('blah).employee_number

where employee_number is a column in that table

View 3 Replies View Related

PL/SQL :: Only Display A Column Value One Time For A Record / Not A Static Value

Jan 7, 2013

I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.

select Date, Person, Language, Country
from TableATableA
Date            Person  Language   Country
01/25/2013       James   English
12/20/2012       James   English
US

[code]....

View 3 Replies View Related

Forms :: FRM-30058 - Invalid Name For A Record Group Column

Feb 2, 2010

earlier the query was running on same record group but i hav to delete some code so i did but after made such changes itreturns this error.but still query is correct i complied on toad.

View 10 Replies View Related

PL/SQL :: How To Insert Null Record (some Column) In Table Using Loop

Jul 5, 2012

How to insert null record (for some columns) in table using loop.

sample data of x_tab

order_id order_name

231 xxx
123
345
111 vvvv

View 5 Replies View Related

PL/SQL :: Average / Standard Deviation Of Multi-Column Record

Jun 28, 2012

I want to read a record that has 6 columns

RECORD     |Col1     |Col2     |Col3      |Col4     |Average      |Standard Deviation     
0001____|Null_|5___|8___|10__|8.75___|2.986079

With SQL I want to calculate Average and Standard Deviation.

View 4 Replies View Related

Forms :: Populating Values To Record Group From Multi Record Datablock

Jul 26, 2012

My procedure proc_ex is in when_validate_item trigger

I have one Multi Record data block in my form with values in its items

I need to Populate multi record block values to one Record Group using

add_group_row,
add_group_column,
set_group_char_cell to populate values to record group

Let us suppose my multi record data block looks like

item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80

how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies

View 3 Replies View Related

Server Utilities :: How To Associate Header Record To Its Corresponding Detail Record

May 16, 2012

I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the

LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,

[code]....

A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).

THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002

What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?

View 11 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 01.27.48.000000000 PM1 nammi  26-AUG-13 01.28.10.000000000 PM2 kk        22-AUG-13 01.28.26.000000000 PM2 thej      26-AUG-13 01.28.42.000000000 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej  26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

Forms :: Only New Record Have Saved Last Record No Longer Exist

Jun 29, 2013

I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.

View 4 Replies View Related

Forms :: Choose Lov In First Record That Won't Be Display In Second Record Lov

Sep 10, 2012

I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...

View 4 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Application Express :: Hyperlink Column With Link Parameters As Region Column Values

Apr 10, 2013

On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.

I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.

So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:

a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?

The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.

---

"Application Express 4.2.1.00.08"

View 2 Replies View Related

Reports & Discoverer :: REP-1241 Circular Column Dependency Originating With Column?

Jul 13, 2010

I receive the error,

REP-1241 Circular column dependency originating with column 'cenvat_opening'

I have the following return statement for a column total_credit;

function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;

Now i am using this total_creditFormula for the error formula column 'cenvat_opening'

function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN

[code]...

here the formula column 'total_credit' has the following return statements;

RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)

so how can i use this formula column? is there any other option to return the value in the same column.

View 6 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

Reports & Discoverer :: How To Make Name Of Column Appear As Many Times As Column On Page

Nov 21, 2010

This is what i want to do:

I have a basic report looking like this:

<Column name>
value 1
value 2
value 3
.
.
.
value n

Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.

After these changes my report looks like this:
<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m

What i want is my report to look like this:

<Column name>-------<Column name>------------<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m

View 4 Replies View Related

Server Utilities :: Mapped 100th Column From CSV File After 5th Column

Sep 10, 2012

I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.

OPTIONS (ERRORS=100000000)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
[code]....

View 10 Replies View Related

Application Express :: Column Link Where Column Number Changes With Query

Feb 11, 2013

We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report

<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012

[Code]....

The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.

Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)

ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.

So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved