Rows to Column [message #446369] |
Mon, 08 March 2010 00:26 |
|
Dear All,
Can anyone explain me how i can convert rows into coulmns as tabular form.
I have two table : om_supplier
supp_code
supp_name
om_supplier_term
st_supp_code
terms
like if i want to make query form from supp_code to supp_code
the output is comming
[code]
select supp_code,terms from om_supplier,om_supplier_term
where supp_Code = st_supp_code
[code]
supp_code terms
1 90days
1 180days
1 120days
i want the output like
supp_code
1 90days-180days-120days
how i can achieve this in form.
|
|
|
|
Re: Rows to Column [message #446377 is a reply to message #446375] |
Mon, 08 March 2010 00:52 |
|
But muchel in your reference they are talking about numeric values by using decode but i have varchar columns and its not possible by that method as i dont know how many terms are there for one supplier.it may be 3 or 7 also.
|
|
|
|
Re: Rows to Column [message #446446 is a reply to message #446369] |
Mon, 08 March 2010 07:49 |
gregor
Messages: 86 Registered: March 2010 Location: Germany
|
Member |
|
|
Hi,
here an way for datastructures, not easy to fetch
in one Query.
you can built datastructures( PL/SQL-Tables) to show what
ever you want,
Writing a Database-Package with Functions for
SELECT , FETCH , LOCK, INSERT , UPDATE, DELETE
and
in your Form.fmb using TRANSACTIONAL-Triggers.
VERY VERY SHORT EXAMPLE:
--------------------------------------
on-Select : -- call your Database PAckage PS_SC.read
------- EXAMPLE
--- IN: QRY_id ; RETURNING PL/SQLTAB
g_erg_last := 0; -- on-Fetch-Counter reset
p_form.g_tab := PS_SC.READ (pi_QRY_id => 1,
po_anz_rows => l_count_rows,
po_an_column => l_count_column );
-----------------------------
on-Fetch:
WHILE p_form.g_erg_last < p_form.g_tab.COUNT
LOOP
p_form.g_erg_counter_last := p_form.g_erg_last + 1;
CREATE_QUERIED_RECORD;
-- UNIVERSAL is the name of the FORMS-Block
:UNIVERSAL.COl1 := p_form.g_tab(p_form.g_erg_last).colu1;
:UNIVERSAL.COl2 := p_form.g_tab(p_form.g_erg_last).colu2;
:UNIVERSAL.COl3 ...
...
END LOOP;
---------------------------------
on-insert : on-update
on-delete
write a Database-Package-Procedure, that inserts / updates/ deletes one Record.
---
on-lock :
cursor cu_lock is
select * from your_tab where id = :universal.id
for update nowait;
---------------------
( a little bit work, but fully flexible)
CM: Added [code] tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.
[Updated on: Mon, 08 March 2010 08:18] by Moderator Report message to a moderator
|
|
|
|
|
Re: Rows to Column [message #447553 is a reply to message #447544] |
Tue, 16 March 2010 00:33 |
|
Martin i just want one sample of how to convert rows to columns in a form.suppose i have one field displaying multiple values , i want to fix these values of rows as columns.
|
|
|
Re: Rows to Column [message #448728 is a reply to message #447553] |
Thu, 25 March 2010 01:08 |
|
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS Australia
|
Senior Member Account Moderator |
|
|
Read the data in the item. Use PL/SQL functions and logic to parse the data and populate a record in a multi-row non-displayed control block. Then go to your displayed block, populate the record with the data from the first row of the non-display block, do a 'next_record' on each block and repeat.
Alternatively, populate a table in the database, do a 'standard.commit' to save the data, then go to the 'new' block and do an 'execute_query'.
David
|
|
|