I'd like to create a table dynamicly. List of column in this table
should be taken from select: "select distinct fiel from table "
How to do it ?
tnx in advance for help
K.
--
Używam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org 8 13799
"Krzys!" <kr*****@polbox.com> wrote in message
news:bg***********@foka1.acn.pl... I'd like to create a table dynamicly. List of column in this table should be taken from select: "select distinct fiel from table "
How to do it ?
tnx in advance for help K.
-- Używam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org
It is rare that in Oracle you would ever need to do this. Usually people
who feel they need to do this are from a SQLServer or Sybase background. If
you feel that you must do this look up global temprorary tables in the
documentation.
Jim
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in message news:<E3aWa.19502$cF.8390@rwcrnsc53>... "Krzys!" <kr*****@polbox.com> wrote in message news:bg***********@foka1.acn.pl... I'd like to create a table dynamicly. List of column in this table should be taken from select: "select distinct fiel from table "
How to do it ?
tnx in advance for help K.
-- Używam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org It is rare that in Oracle you would ever need to do this. Usually people who feel they need to do this are from a SQLServer or Sybase background. If you feel that you must do this look up global temprorary tables in the documentation. Jim
To add to above and just so that you know that it CAN be done in
Oracle, Visit http//tahiti.oracle.com and lookup dynamic sql. Use
dbms_sql search keyword.
Regards
/Rauf Sarwar
"Krzys!" <kr*****@polbox.com> wrote in message news:<bg***********@foka.acn.pl>... True :-) I used to working with TSQL and I'm a begginer in Oracle. Anyway I try to figure out with temporary tables but if someone else have other suggestion how to solve my problem please help !!!!
K.
You got that suggestion didn't you. 'Look up global temporary tables
in the documentation'.
You won't learn Oracle without reading the documentation, so I suggest
you start reading it now.
(Or don't come back in this forum)
Senior Oracle DBA
"Krzys!" <kr*****@polbox.com> schrieb im Newsbeitrag
news:bg***********@foka.acn.pl... True :-) I used to working with TSQL and I'm a begginer in Oracle. Anyway I try to figure out with temporary tables but if someone else have other suggestion how to solve my problem please help !!!!
K.
Hi Krzys,
we would try to help you if you let us know what
your problem is. Please elaborate. Oh: and
include your dbms version (at least!).
Greetings,
Guido
"Krzys!" <kr*****@polbox.com> wrote in message
news:bg***********@foka.acn.pl... True :-) I used to working with TSQL and I'm a begginer in Oracle. Anyway I try to figure out with temporary tables but if someone else have other suggestion how to solve my problem please help !!!!
K. Jim Kennedy wrote: "Krzys!" <kr*****@polbox.com> wrote in message news:bg***********@foka1.acn.pl...
I'd like to create a table dynamicly. List of column in this table should be taken from select: "select distinct fiel from table "
How to do it ?
tnx in advance for help K.
-- Używam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org
It is rare that in Oracle you would ever need to do this. Usually
people who feel they need to do this are from a SQLServer or Sybase background.
If you feel that you must do this look up global temprorary tables in the documentation. Jim
Ah, resorting to bad habits. In those environments you have to use
temporary tables. In Oracle just try selecting the data, don't bother with
temporary tables. It will only slow you down. In all my years in using
Oracle - dba and application developer and it is over 10 years of
experience - I have only come accros one instance where global temporary
tables where the answer. At the time they did not exist, so we just used a
regular table and created it once for everyone.
Oracle!=SQLServer. About 2 different beasts as you can have. In Oracle
readers don't block writers and writers don't block readers. No really they
don't. You have a lot to learn that things are done much easier in Oracle.
Jim
Hi Krzys,
the difficult part is that you have to know
how many different act_base_id's (and which!)
exist. If you can sort that out, it's easy.
You can run a query directly from excel or
spool the output of the query to ...csv format.
Read upon SPOOL command in the SQL/PLUS docs.
To seperate the columns you have to use SET COLSEP ,<. This will get you the comma
separated file you need.
About the query:
my solution is simplified (I didn't want to
create your 3 tables).
SELECT sales_id,
SUM(CASE WHEN act_base_id=100 THEN amt ELSE 0 END) amt100,
SUM(CASE WHEN act_base_id=120 THEN amt ELSE 0 END) amt120,
SUM(CASE WHEN act_base_id=130 THEN amt ELSE 0 END) amt130,
SUM(CASE WHEN act_base_id=150 THEN amt ELSE 0 END) amt150,
SUM(CASE WHEN act_base_id=170 THEN amt ELSE 0 END) amt170,
SUM(CASE WHEN act_base_id=200 THEN amt ELSE 0 END) amt200
FROM (insert your query here)
GROUP BY sales_id;
Note that I used a column alias on count(*).
I'm pretty sure that someone can find a better
solution.
Greetings & hth,
Guido
"Krzys!" <kr*****@polbox.com> schrieb im Newsbeitrag
news:bg***********@foka.acn.pl... WOW .. great !
version of ORACLE 8.1.6
Let me describe whole situation. I need to create a raport I could export and analyze in Excel. Here is a query I have to "publish" in excel
query: select cf.sales_id, af.ACT_BASE_ID, count(*) from calls_fact cf, act_fact af, act_base_name abn where cf.ACT_FACT_ID=af.ACT_FACT_ID and
af.ACT_BASE_ID=abn.ACT_BASE_ID and abn.lang_id='POL' group by cf.sales_id, af.ACT_BASE_ID;
result: sales_id act_base_id count(*) 1 100 20 1 120 30 1 170 5 2 130 10 2 170 90 3 100 5 4 150 20 4 200 4 5 120 40 ETC. ETC.
so every sales (sales_id) has a few different activities
(act_base_id) and he make this activities a lot of times every day. I hope it's
easy and clear. But I need export this result to excel to a bit
different format. I could say format which my manager is able to understand ;) Each line should contain one sales like that:
sales_id 100 120 130 150 170 200 1 20 30 5 2 10 90 3 5 4 20 4 5 40
is it clear ? I'm sure yes
So I need to create table which contains as many column as many name
of activities there are on my table of activities. If I manage to create it I start fill this table cell by cell using
two or three cursors I've already created
So ... what do you think ? Now is it clear ? Ir maybe You have other ideas how to convert "oracle result" to
"excel sheet"
K.
Guido Konsolke wrote: "Krzys!" <kr*****@polbox.com> schrieb im Newsbeitrag news:bg***********@foka.acn.pl...
True :-) I used to working with TSQL and I'm a begginer in
Oracle.Anyway I try to figure out with temporary tables but if someone
elsehave other suggestion how to solve my problem please help !!!!
K.
Hi Krzys,
we would try to help you if you let us know what your problem is. Please elaborate. Oh: and include your dbms version (at least!).
Greetings, Guido
Damn .. it's so easy !!!! No cursors, no store procedures , just one
simple query with case :) I owe You one beer ;)
Thanks a lot !!
K.
Guido Konsolke wrote: Hi Krzys,
the difficult part is that you have to know how many different act_base_id's (and which!) exist. If you can sort that out, it's easy. You can run a query directly from excel or spool the output of the query to ...csv format. Read upon SPOOL command in the SQL/PLUS docs. To seperate the columns you have to use
SET COLSEP ,<. This will get you the comma
separated file you need.
About the query: my solution is simplified (I didn't want to create your 3 tables).
SELECT sales_id, SUM(CASE WHEN act_base_id=100 THEN amt ELSE 0 END) amt100, SUM(CASE WHEN act_base_id=120 THEN amt ELSE 0 END) amt120, SUM(CASE WHEN act_base_id=130 THEN amt ELSE 0 END) amt130, SUM(CASE WHEN act_base_id=150 THEN amt ELSE 0 END) amt150, SUM(CASE WHEN act_base_id=170 THEN amt ELSE 0 END) amt170, SUM(CASE WHEN act_base_id=200 THEN amt ELSE 0 END) amt200 FROM (insert your query here) GROUP BY sales_id;
Note that I used a column alias on count(*).
I'm pretty sure that someone can find a better solution.
Greetings & hth, Guido
"Krzys!" <kr*****@polbox.com> schrieb im Newsbeitrag news:bg***********@foka.acn.pl...
WOW .. great !
version of ORACLE 8.1.6
Let me describe whole situation. I need to create a raport I could export and analyze in Excel. Here is a query I have to "publish" in excel
query: select cf.sales_id, af.ACT_BASE_ID, count(*) from calls_fact cf, act_fact af, act_base_name abn where cf.ACT_FACT_ID=af.ACT_FACT_ID and
af.ACT_BASE_ID=abn.ACT_BASE_ID
and abn.lang_id='POL' group by cf.sales_id, af.ACT_BASE_ID;
result: sales_id act_base_id count(*) 1 100 20 1 120 30 1 170 5 2 130 10 2 170 90 3 100 5 4 150 20 4 200 4 5 120 40 ETC. ETC.
so every sales (sales_id) has a few different activities
(act_base_id)
and he make this activities a lot of times every day. I hope it's
easy
and clear. But I need export this result to excel to a bit
different
format. I could say format which my manager is able to understand ;) Each line should contain one sales like that:
sales_id 100 120 130 150 170 200 1 20 30 5 2 10 90 3 5 4 20 4 5 40
is it clear ? I'm sure yes
So I need to create table which contains as many column as many name
of
activities there are on my table of activities. If I manage to create it I start fill this table cell by cell using
two
or three cursors I've already created
So ... what do you think ? Now is it clear ? Ir maybe You have other ideas how to convert "oracle result" to
"excel
sheet"
K.
Guido Konsolke wrote:
"Krzys!" <kr*****@polbox.com> schrieb im Newsbeitrag news:bg***********@foka.acn.pl...
True :-) I used to working with TSQL and I'm a begginer in Oracle. Anyway I try to figure out with temporary tables but if someone else have other suggestion how to solve my problem please help !!!!
K.
Hi Krzys,
we would try to help you if you let us know what your problem is. Please elaborate. Oh: and include your dbms version (at least!).
Greetings, Guido
"Krzys!" <kr*****@polbox.com> wrote in message news:<bg***********@foka.acn.pl>... Damn .. it's so easy !!!! No cursors, no store procedures , just one simple query with case :) I owe You one beer ;)
Thanks a lot !! K.
And you will end up with a mess, of course. But you seem to love
'experimenting until it works' as opposed to 'doing it properly'
And above all you don't want to RTFM. Go figure!
Sybrand Bakker
Senior Oracle DBA' This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Klom Dark |
last post by:
I've got a weird problem going on - I've got a table of dynamically created buttons. Each button has the X/Y value of the buttons position in the table assigned to it's CommandArgument property and...
|
by: Dave Williamson |
last post by:
When a ASPX page is created with dynamic controls based on what the
user is doing the programmer must recreate the dynamic controls again
on PostBack in the Page_Load so that it's events are wired...
|
by: Leo J. Hart IV |
last post by:
OK, here's another question for the experts:
I am building a multi-step (3 steps actually) form using a panel for
each step and hiding/displaying the appropriate panel/panels depending
on which...
|
by: russ |
last post by:
Hi all,
Here's a problem I'm having with a dynamic table. Following the
guidelines here
(http://www.codeproject.com/aspnet/dynamiccontrols.asp), which make
perfect sense. The problem is that...
|
by: Pascal Costanza |
last post by:
Dynamic Languages Day @ Vrije Universiteit Brussel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Monday, February 13, 2006, VUB Campus Etterbeek
The VUB (Programming Technology Lab,...
|
by: william_dean |
last post by:
Hello,
I've done some searching around the post, and I have found quite a bit of information related to the setAttribute and it's related uses. My problem lies in the usage of colspanning in dynamic...
|
by: salad |
last post by:
Operating in A97.
I didn't receive much of a response conserning Pivot tables in Access.
Pivot tables are nice, but a CrossTab will work for me too.
Using a Pivot table, one is actually...
|
by: arunank |
last post by:
Hi,
The following code for dynamic table creation is not working.
Can anyone please help me.
The dynamically created rows and columns are not getting populated.
CODE:
=========
<html>
|
by: cdmsenthil |
last post by:
I have an Infragistics UltrawebGrid . Each Row in the grid is attached to a context menu using Infragistics CSOM
Upon click on the menu, I am creating an Iframe dynamically which points to...
|
by: tokcy |
last post by:
hi everyone,
i am creating dynamic row in a table using javascript its working fine and now i want to create more than 1 table using javascript which is dynamic its also working fine but when i am...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |