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 13822
"Krzys!" <kr*****@polbox .com> wrote in message
news:bg******** ***@foka1.acn.p l... 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_spamm ers@no_spam.com cast.net> wrote in message news:<E3aWa.195 02$cF.8390@rwcr nsc53>... "Krzys!" <kr*****@polbox .com> wrote in message news:bg******** ***@foka1.acn.p l... 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.c om and lookup dynamic sql. Use
dbms_sql search keyword.
Regards
/Rauf Sarwar
"Krzys!" <kr*****@polbox .com> wrote in message news:<bg******* ****@foka.acn.p l>... 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.p l...
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!=SQLServ er. 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='PO L' 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='PO L' 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.p l>... 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 the name of a common command (btn_Command) assigned to it's Command property. The creation of the table is done by a function called drawGeo. drawGeo is called during the initial Page_Load (!IsPostBack), but should be called by btn_Command on...
|
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 and are
called like a static control.
Here is the problem that I need to solve. The processing overhead
that occurs to determine what dynamic controls need to be added
involves business logic and a query or queries of data in a sql server...
|
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 step you're on. This all works fine, but I ran into some
trouble when I started creating controls dynamically in my code-behind
file.
Each panel contains a table which is filled with various radio buttons,
text fields and the such which are...
|
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 the table contains a SELECT box
populated on the initial load. Every time I postback I'm inserting a
column into the table, the dropdown always remains in the last column.
First time I postback the dropdown is populated okay. The second time...
|
by: Pascal Costanza |
last post by:
Dynamic Languages Day @ Vrije Universiteit Brussel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Monday, February 13, 2006, VUB Campus Etterbeek
The VUB (Programming Technology Lab, System and Software Engineering
Lab), ULB (deComp) and the Belgian Association for Dynamic Languages
(BADL) are very pleased to invite you to a whole day of presentations
about the programming languages Self, Smalltalk and Common Lisp by
experts in...
| |
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 table creation.
Code Snippet
<SCRIPT LANGUAGE="JavaScript">
function morecase(id){
var tbody = document.getElementById(id).getElementsByTagName("TBODY");
var row1=document.createElement('TR');
var...
|
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 launching Excel for data viewing.
I'd prefer the user stay in Access. Creating dynamic crosstab queries
is pretty simple. The problem is that the column count may shrink or
grow depending on the filter.
|
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 another page in the same domain which also contains infragistics datagid populated with default data retrieved from Data Base. After creating the frame I am attaching it to the HTML DOM and show it as modal popup with OK and Cancel Button inside an...
|
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 taking the value of each table individually thats i am not able to get any value. if i get the value of table then its not taking properly.
let me explain the whole scenario...
i want to add product specification . it may vary in number of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |