Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Every Row into an individual Columns in SQL Query

534103Jan 15 2010 — edited May 15 2010
Hi Experts,
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production          
PL/SQL Release 11.1.0.7.0 - Production                                          
CORE	11.1.0.7.0	Production                                                      
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production                         
NLSRTL Version 11.1.0.7.0 - Production          
-- DROP TABLE RENDER_VALUES;
CREATE TABLE RENDER_VALUES
(
    ID          INTEGER PRIMARY KEY,
    FIELD_NAME  VARCHAR2(1000),
    FIELD_VALUE VARCHAR2(1000)
);
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(1,'CropX','10.31234');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(2,'CropY','20.31234');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(3,'Height','100');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(4,'Width','200');
COMMIT;
SELECT FIELD_NAME,FIELD_VALUE FROM RENDER_VALUES;
FIELD_NAME	FIELD_VALUE
CropX		10.31234
CropY		20.31234
Height		100
Width		200
I need the output as:
CropX		CropY		Height	Width
10.31234	20.31234	100	200
Just for sample ..i have given Four Values in Field name Column...there can be (n) no.of values in that and to get each row
into an individual columns...how can i do something like this?

- Dharan V
This post has been answered by Frank Kulash on Jan 15 2010
Jump to Answer

Comments

fac586
V Rickert wrote:
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
Is there an APEX dimension to this? If so, tell us the full APEX and DB versions, and provide full details of what the APEX app is doing (a debug trace of page accept processing is the obvious place to start).

The most likely explanations are:

1. There is no duplicate in the table but there is a join problem in the query reporting on it, resulting in the appearance of a duplicate in the reults. Have you confirmed that the duplicate is really a physical row in the table?

2. There is an APEX page/application process containing an insert on the table that is unexpectedly running on page accept due to it having no condition or an incorrect condition. This will be visible in the Debug trace.
InoL
What would cause Oracle to insert duplicate rows into a table?
Nothing. It's is the programmer that created bad code (if duplicate rows are not supposed to be inserted). If there is no table trigger, than some other code must be responsible for the insert.

BTW. Is this related to your other post:
2533049
Here you did actually create a trigger B-)

Edited by: InoL on May 2, 2013 12:08 PM
V Rickert
I'm thinking it is the use of a unique INDEX on a table that is causing APEX to insert extra rows. The INDEX is not the primary key. Example: A row in an INVOICE table contains invoice information such as invoice number, vendor, date, purchase order number, Work Order number, etc. A sequence number is the primary key as all other data items can be updated by the user. To prevent the same invoice number from being duplicated, a unique composite key/index was created on Invoice, Vendor, and Work Order. If the user, however, needs to change the work order, then APEX creates another row instead of updating the current row. I think the index is the issue and I am investigating that theory.
InoL
If the user, however, needs to change the work order, then APEX creates another row instead of updating the current row.
What kind of page did you create in Apex? Is it a form with report based on the INVOICE table?
How did you specify the Primary Key Type? Managed by database (ROWID) or Primary key columns? In the 2nd case, check if you specified the correct PK column and PK generation method.

Just another question:
a unique composite key/index was created on Invoice, Vendor, and Work Order
Is Invoice the sequence generated invoice number (the PK)? In that case creating a unique index on these 3 columns is not very useful, since Invoice is already always unique. Not that this would explain your duplicate record, though.

Edited by: InoL on May 3, 2013 12:52 PM
V Rickert
The APEX report is a form with report based upon a sql query of the Invoice Table. It contains Header HTML page items and then a detail section where you 'Add a Row' for detail (that part works fine.) The primary key specified is not the APEX-defined ROWID, but a Sequence(Invoice.Seq_id) with associated trigger that I have specified. (So technically the row is not a duplicate as the Invoice.Seq_id is unique. It is the invoice_number that is repeated.) I think I need a unique index on Invoice_number to prevent the 'duplicates'? I do not have that specified.
InoL
Sorry, I'm a bit confused now.
where you 'Add a Row'
So, you are adding a row? Before you had a problem with updating a row.
I think I need a unique index on Invoice_number to prevent the 'duplicates'?
You could do that, but it will just give you an error message. However, it could point to the process that is trying to insert a record when you are only updating a record.

One more thing:
a form with report based upon a sql query of the Invoice Table
So, you are not using the INVOICE table directly as a source? What is the query you are using? It's not a view by any chance, with INSTEAD OF triggers?

Edited by: InoL on May 3, 2013 3:22 PM
V Rickert
I'm sorry. I am just not explaining myself very well! :(

Here is the scenario.
The invoice table contains a sequence number as the primary key. The sequence number is unique and never duplicated. SO technically, APEX/ORACLE is not erroneously entering rows into the table. My problem is: We cannot use 'invoice_number' as the primary key because it is a vendor-issued invoice number and you never know if Vendor A will use the same invoice number as Vendor B. I am trying to prevent an exact replica of Invoice Number/Vendor A showing up on two different rows: Example Seq_no = 1, invoice=ABC, Vendor=A; seq_no=2, invoice=ABC,Vendor=A. Technically, this is not a duplicate. But logically in our system, it is. Therefore, I am hoping that an index on Invoice/Vendor will prevent the invoice/vendor combination from being replicated at all. So, question: will a unique index on invoice_number/vendor prevent 'duplicates'?

Edited by: V Rickert on May 3, 2013 1:29 PM
InoL
will a unique index on invoice_number/vendor prevent 'duplicates'?
Yes it will.
But is is something completely different than your original post, where, from my understanding, you said that a row was inserted when you updated a record.
V Rickert
The effect to the user is - it is! The user does not see the sequnce_id, only the fact that the invoice number and vendor now occur twice! So for them, the users, duplicates exist. Sorry for the confusion!

Edited by: V Rickert on May 3, 2013 2:04 PM
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2010
Added on Jan 15 2010
19 comments
7,035 views