Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access.
Given are two tables to join:
T_FIRM:
FIRM_ABBR VARCHAR2(3 BYTE),
FIRM_LONG VARCHAR2(70 BYTE),
CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
"FI1", "Company Number One"
"FI2", "Company Number Two"
....
"FOO", "another Company"
T_DATA:
BEZEICHNUNG VARCHAR2(255 BYTE),
DATUM DATE,
VOLUMEN_EUR FLOAT(126),
AK VARCHAR2(10 BYTE),
AW VARCHAR2(10 BYTE),
AR VARCHAR2(10 BYTE),
MF VARCHAR2(10 BYTE),
AP VARCHAR2(10 BYTE),
PK VARCHAR2(10 BYTE),
PI VARCHAR2(10 BYTE),
EU VARCHAR2(10 BYTE),
ZG VARCHAR2(10 BYTE),
FT VARCHAR2(10 BYTE),
ET VARCHAR2(10 BYTE),
ST NUMBER,
DE VARCHAR2(10 BYTE),
EM VARCHAR2(10 BYTE),
DU VARCHAR2(10 BYTE),
RK VARCHAR2(10 BYTE),
BR VARCHAR2(10 BYTE),
KA VARCHAR2(10 BYTE),
AM VARCHAR2(10 BYTE),
PM VARCHAR2(10 BYTE),
PT VARCHAR2(10 BYTE),
TT VARCHAR2(10 BYTE),
FIRM VARCHAR2(10 BYTE),
Columns AK to TT are Foreign Key references to a lot of separate
tables.
AK can be one of 16 values, i.e. 'A1','G4','I1', 'C2'. These 16 rows
should become columns. ZG can be one of 'G','P','S'.
I would like to store this Pivot table as a View:
TRANSFORM Sum([VOLUMEN_EUR]/1000000) AS Vol
SELECT T_FIRM.FIRM_LON G, T_DATA.Datum, Sum([VOLUMEN_EUR]/100000000) AS
Summe1
FROM T_DATA INNER JOIN T_FIRM ON T_DATA.FIRM = T_FIRM.FIRM_ABB R
WHERE (((T_DATA.Datum )=#2/27/2004#))
GROUP BY T_FIRM.FIRM_LON G, T_DATA.Datum
PIVOT T_DATA.ZG;
I've read about such a construct, but this only creates
columns from rows, but without summing up anything:
select DA.FIRM, DA.DATUM, DA.AK,
decode (DA.AK, 'A1', DA.AK) A1,
decode (DA.AK, 'A2', DA.AK) A2,
decode (DA.AK, 'G1', DA.AK) G1,
decode (DA.AK, 'G2', DA.AK) G2,
decode (DA.AK, 'G3', DA.AK) G3,
decode (DA.AK, 'G4', DA.AK) G4,
decode (DA.AK, 'G5', DA.AK) G5,
decode (DA.AK, 'G6', DA.AK) G6,
decode (DA.AK, 'G7', DA.AK) G7,
decode (DA.AK, 'G8', DA.AK) G8,
decode (DA.AK, 'I1', DA.AK) I1,
decode (DA.AK, 'R1', DA.AK) R1,
decode (DA.AK, 'R2', DA.AK) R2,
decode (DA.AK, 'X', DA.AK) X
FROM T_DATA DA
group by DA.FIRM, DA.DATUM, DA.AK
Any help would be greatly appreciated.
mic 4 2888
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Use the CASE statement to create the summations:
select DA.FIRM, DA.DATUM, DA.AK,
SUM(CASE DA.AK WHEN 'A1' THEN DA.AK ELSE NULL END) A1,
SUM(CASE DA.AK WHEN 'A2' THEN DA.AK ELSE NULL END) A2,
SUM(CASE DA.AK WHEN 'G1' THEN DA.AK ELSE NULL END) G1,
SUM(CASE DA.AK WHEN 'G2' THEN DA.AK ELSE NULL END) G2,
SUM(CASE DA.AK WHEN 'G3' THEN DA.AK ELSE NULL END) G3,
SUM(CASE DA.AK WHEN 'G4' THEN DA.AK ELSE NULL END) G4,
SUM(CASE DA.AK WHEN 'G5' THEN DA.AK ELSE NULL END) G5,
SUM(CASE DA.AK WHEN 'G6' THEN DA.AK ELSE NULL END) G6,
SUM(CASE DA.AK WHEN 'G7' THEN DA.AK ELSE NULL END) G7,
SUM(CASE DA.AK WHEN 'G8' THEN DA.AK ELSE NULL END) G8,
SUM(CASE DA.AK WHEN 'I1' THEN DA.AK ELSE NULL END) I1,
SUM(CASE DA.AK WHEN 'R1' THEN DA.AK ELSE NULL END) R1,
SUM(CASE DA.AK WHEN 'R1' THEN DA.AK ELSE NULL END) R2,
SUM(CASE DA.AK WHEN 'X' THEN DA.AK ELSE NULL END) X
FROM T_DATA DA
group by DA.FIRM, DA.DATUM, DA.AK
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQIhNMYechKq OuFEgEQJsAACfe7 UmJHjaAr8/Lqrddfd7zjLVs+o AoL0V
LMWK2hbIHZBU4rZ +Q7q1No1i
=Tzm4
-----END PGP SIGNATURE-----
Michael John wrote: Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access. Given are two tables to join:
T_FIRM: FIRM_ABBR VARCHAR2(3 BYTE), FIRM_LONG VARCHAR2(70 BYTE), CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
"FI1", "Company Number One" "FI2", "Company Number Two" ... "FOO", "another Company"
T_DATA: BEZEICHNUNG VARCHAR2(255 BYTE), DATUM DATE, VOLUMEN_EUR FLOAT(126), AK VARCHAR2(10 BYTE), AW VARCHAR2(10 BYTE), AR VARCHAR2(10 BYTE), MF VARCHAR2(10 BYTE), AP VARCHAR2(10 BYTE), PK VARCHAR2(10 BYTE), PI VARCHAR2(10 BYTE), EU VARCHAR2(10 BYTE), ZG VARCHAR2(10 BYTE), FT VARCHAR2(10 BYTE), ET VARCHAR2(10 BYTE), ST NUMBER, DE VARCHAR2(10 BYTE), EM VARCHAR2(10 BYTE), DU VARCHAR2(10 BYTE), RK VARCHAR2(10 BYTE), BR VARCHAR2(10 BYTE), KA VARCHAR2(10 BYTE), AM VARCHAR2(10 BYTE), PM VARCHAR2(10 BYTE), PT VARCHAR2(10 BYTE), TT VARCHAR2(10 BYTE), FIRM VARCHAR2(10 BYTE),
Columns AK to TT are Foreign Key references to a lot of separate tables. AK can be one of 16 values, i.e. 'A1','G4','I1', 'C2'. These 16 rows should become columns. ZG can be one of 'G','P','S'. I would like to store this Pivot table as a View:
TRANSFORM Sum([VOLUMEN_EUR]/1000000) AS Vol SELECT T_FIRM.FIRM_LON G, T_DATA.Datum, Sum([VOLUMEN_EUR]/100000000) AS Summe1 FROM T_DATA INNER JOIN T_FIRM ON T_DATA.FIRM = T_FIRM.FIRM_ABB R WHERE (((T_DATA.Datum )=#2/27/2004#)) GROUP BY T_FIRM.FIRM_LON G, T_DATA.Datum PIVOT T_DATA.ZG;
I've read about such a construct, but this only creates columns from rows, but without summing up anything:
select DA.FIRM, DA.DATUM, DA.AK, decode (DA.AK, 'A1', DA.AK) A1, decode (DA.AK, 'A2', DA.AK) A2, decode (DA.AK, 'G1', DA.AK) G1, decode (DA.AK, 'G2', DA.AK) G2, decode (DA.AK, 'G3', DA.AK) G3, decode (DA.AK, 'G4', DA.AK) G4, decode (DA.AK, 'G5', DA.AK) G5, decode (DA.AK, 'G6', DA.AK) G6, decode (DA.AK, 'G7', DA.AK) G7, decode (DA.AK, 'G8', DA.AK) G8, decode (DA.AK, 'I1', DA.AK) I1, decode (DA.AK, 'R1', DA.AK) R1, decode (DA.AK, 'R2', DA.AK) R2, decode (DA.AK, 'X', DA.AK) X FROM T_DATA DA group by DA.FIRM, DA.DATUM, DA.AK
MGFoster <me@privacy.com > wrote: Use the CASE statement to create the summations:
Thank you very much for your answer.
But there is another problem I face:
I have the given table and want to convert rows to columns.
NR ARTICLE DAY STOCK VALUE
073639 C1 31.12.2003 158723 15,3326418
073639 C1 31.01.2004 158723 15,40247992
073639 C1 29.02.2004 158723 14,67552858
073639 C1 31.03.2004 158723 14,86123449
073639 C1 30.04.2004 158723 14,76917515
079373 G1 31.12.2003 158667 9,9008208
079373 G1 31.01.2004 158988 10,2388272
079373 G1 29.02.2004 158988 10,34057952
079373 G1 31.03.2004 158988 10,37873664
079373 G1 30.04.2004 160062 10,6601292
085581 M1 31.12.2003 136398 100,31254512
085581 M1 31.01.2004 136398 100,5798852
085581 M1 29.02.2004 136898 102,09031452
085581 M1 31.03.2004 136898 103,26626834
085581 M1 30.04.2004 136899 102,263553
085820 G2 31.12.2003 160910 11,2234725
085820 G2 31.01.2004 160910 11,2604818
085820 G2 29.02.2004 160910 11,3312822
085820 G2 31.03.2004 143300 10,139908
085820 G2 30.04.2004 143300 10,124145
That's what it should be:
DAY C1 G2 G1 M1
31.12.2003 158723 160910 158667 136398
31.01.2004 158723 160910 158988 136398
29.02.2004 158723 160910 158988 136898
31.03.2004 158723 143300 158988 136898
30.04.2004 158723 143300 160062 136899
That's what I get:
DAY C1 G1 M1 G2
31.12.2003 158723 158667 136398 143300
31.01.2004 158723 158667 136398 143300
29.02.2004 158723 158667 136398 143300
31.03.2004 158723 158667 136398 143300
30.04.2004 158723 158667 136398 143300
With Access' First() function, this works, but not with the following
query:
SELECT T.DAY,
(select distinct FIRST_VALUE(STO CK) over (order by STOCK from T
where NR='073639') "C1",
(select distinct FIRST_VALUE(STO CK) over (order by STOCK from T
where NR='079373') "G1",
(select distinct FIRST_VALUE(STO CK) over (order by STOCK from T
where NR='085581') "M1",
(select distinct FIRST_VALUE(STO CK) over (order by STOCK from T
where NR='085820') "G2"
FROM T
GROUP BY DAY;
How do I have to use this to get only the right values?
Additionaly, what exactly is ROWS/RANGE UNBOUNDED PRECEDING good for
and could it be helpful?
Thanks in advance and have a nice day,
mic
This should do it:
select DAY
max(case when NR = 'C1' then STOCKelse null end) as C1,
max(case when NR = 'G2' then STOCK else null end) as G2,
max(case when NR = 'G1' then STOCK else null end) as G1,
max(case when NR = 'M1' then STOCK else null end) as M1
from T
group by DAY
Looks like you only want one value. You could also use a sum function if
you want to add up all of the stock values.
don't know what you mean by ROWS/RANGE UNBOUNDED PRECEDING.
Kevin
"Michael John" <am********@gmx .at> wrote in message
news:8d******** *************** ***@posting.goo gle.com... MGFoster <me@privacy.com > wrote:
Use the CASE statement to create the summations:
Thank you very much for your answer. But there is another problem I face:
I have the given table and want to convert rows to columns.
NR ARTICLE DAY STOCK VALUE 073639 C1 31.12.2003 158723 15,3326418 073639 C1 31.01.2004 158723 15,40247992 073639 C1 29.02.2004 158723 14,67552858 073639 C1 31.03.2004 158723 14,86123449 073639 C1 30.04.2004 158723 14,76917515 079373 G1 31.12.2003 158667 9,9008208 079373 G1 31.01.2004 158988 10,2388272 079373 G1 29.02.2004 158988 10,34057952 079373 G1 31.03.2004 158988 10,37873664 079373 G1 30.04.2004 160062 10,6601292 085581 M1 31.12.2003 136398 100,31254512 085581 M1 31.01.2004 136398 100,5798852 085581 M1 29.02.2004 136898 102,09031452 085581 M1 31.03.2004 136898 103,26626834 085581 M1 30.04.2004 136899 102,263553 085820 G2 31.12.2003 160910 11,2234725 085820 G2 31.01.2004 160910 11,2604818 085820 G2 29.02.2004 160910 11,3312822 085820 G2 31.03.2004 143300 10,139908 085820 G2 30.04.2004 143300 10,124145
That's what it should be:
DAY C1 G2 G1 M1 31.12.2003 158723 160910 158667 136398 31.01.2004 158723 160910 158988 136398 29.02.2004 158723 160910 158988 136898 31.03.2004 158723 143300 158988 136898 30.04.2004 158723 143300 160062 136899
That's what I get: DAY C1 G1 M1 G2 31.12.2003 158723 158667 136398 143300 31.01.2004 158723 158667 136398 143300 29.02.2004 158723 158667 136398 143300 31.03.2004 158723 158667 136398 143300 30.04.2004 158723 158667 136398 143300
With Access' First() function, this works, but not with the following query:
SELECT T.DAY, (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='073639') "C1", (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='079373') "G1", (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='085581') "M1", (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='085820') "G2" FROM T GROUP BY DAY;
How do I have to use this to get only the right values?
Additionaly, what exactly is ROWS/RANGE UNBOUNDED PRECEDING good for and could it be helpful?
Thanks in advance and have a nice day, mic
"Kevin Crosbie" <ca************ **@yahoo.com> wrote in message news:<7e******* *************** ********@news.t eranews.com>... This should do it:
select DAY max(case when NR = 'C1' then STOCKelse null end) as C1, max(case when NR = 'G2' then STOCK else null end) as G2, max(case when NR = 'G1' then STOCK else null end) as G1, max(case when NR = 'M1' then STOCK else null end) as M1 from T group by DAY
Looks like you only want one value. You could also use a sum function if you want to add up all of the stock values.
don't know what you mean by ROWS/RANGE UNBOUNDED PRECEDING.
Kevin
"Michael John" <am********@gmx .at> wrote in message news:8d******** *************** ***@posting.goo gle.com... MGFoster <me@privacy.com > wrote:
Use the CASE statement to create the summations:
Thank you very much for your answer. But there is another problem I face:
I have the given table and want to convert rows to columns.
NR ARTICLE DAY STOCK VALUE 073639 C1 31.12.2003 158723 15,3326418 073639 C1 31.01.2004 158723 15,40247992 073639 C1 29.02.2004 158723 14,67552858 073639 C1 31.03.2004 158723 14,86123449 073639 C1 30.04.2004 158723 14,76917515 079373 G1 31.12.2003 158667 9,9008208 079373 G1 31.01.2004 158988 10,2388272 079373 G1 29.02.2004 158988 10,34057952 079373 G1 31.03.2004 158988 10,37873664 079373 G1 30.04.2004 160062 10,6601292 085581 M1 31.12.2003 136398 100,31254512 085581 M1 31.01.2004 136398 100,5798852 085581 M1 29.02.2004 136898 102,09031452 085581 M1 31.03.2004 136898 103,26626834 085581 M1 30.04.2004 136899 102,263553 085820 G2 31.12.2003 160910 11,2234725 085820 G2 31.01.2004 160910 11,2604818 085820 G2 29.02.2004 160910 11,3312822 085820 G2 31.03.2004 143300 10,139908 085820 G2 30.04.2004 143300 10,124145
That's what it should be:
DAY C1 G2 G1 M1 31.12.2003 158723 160910 158667 136398 31.01.2004 158723 160910 158988 136398 29.02.2004 158723 160910 158988 136898 31.03.2004 158723 143300 158988 136898 30.04.2004 158723 143300 160062 136899
That's what I get: DAY C1 G1 M1 G2 31.12.2003 158723 158667 136398 143300 31.01.2004 158723 158667 136398 143300 29.02.2004 158723 158667 136398 143300 31.03.2004 158723 158667 136398 143300 30.04.2004 158723 158667 136398 143300
With Access' First() function, this works, but not with the following query:
SELECT T.DAY, (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='073639') "C1", (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='079373') "G1", (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='085581') "M1", (select distinct FIRST_VALUE(STO CK) over (order by STOCK from T where NR='085820') "G2" FROM T GROUP BY DAY;
How do I have to use this to get only the right values?
Additionaly, what exactly is ROWS/RANGE UNBOUNDED PRECEDING good for and could it be helpful?
Thanks in advance and have a nice day, mic
The columns used by a crosstab query can either be of a fixed number
or calculated at runtime. The former can be expressed as an Oracle
view if you employ derived tables. The latter cannot be expressed
using a view - a rather complicated stored procedure is required.
We have software that automatically converts Access crosstab queries
to Oracle. If you send me an Access database with the two tables and
crosstab query, I'll convert it for you.
/
Ron in SF This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michael John |
last post by:
Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access.
Given are two tables to join:
T_FIRM:
FIRM_ABBR VARCHAR2(3 BYTE),
FIRM_LONG VARCHAR2(70 BYTE),
CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
|
by: Rob |
last post by:
I'm just getting around to using pivot tables and charts. I find the
Pivot table interface to be INCREDIBLY frustrating.
When I view a table in Design view, then choose Pivot table view, I
get the PivotTable Field list, from which I can choose to add fields
to the view.
1. How do you get RID of a field once you put it in? I can not right
click on anywhere in the view and have ANY of the submenu options be
availble. From the PivotTable...
|
by: SJM |
last post by:
I have a reporting database that has a series of pre-defined Pivot Table
screens. What I would like to do is to enable users to be able to save their
own configurations. I found 2 ways of doing this. One involved saving the
Pivot Table XML, which seemed a great concept but one which seemed ot only
work on MS Access 2003 and not on Access 2002. The other method involved
having code to save the Pivot Table as a new form, and populating a...
|
by: Jerome Ranch |
last post by:
Okay
So I've got pivot tables setup in Access 2003.
Only about 30K records in the current 2005 databases...the pivots
summarize the info in a number of nice ways.
I need to get the pivot tables into a document so I can distribute the
tables, as is, as an electronic report (without the detailed data)
So I export to rtf and xls, and I get an error that there is a too
much information. I don't want all the data, just the summary table!
|
by: PeteCresswell |
last post by:
I've got something called "Reference Rates".
The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury Bonds, the
Prime rate, and so-forth.
We associate a security with one of those rates.
There are a set of rates for each calendar day, and the rates for that
| |
by: Pourya99 |
last post by:
Hello,
I have an Access Data Access Page which has a pivot table. The data source of the pivot table is a SQL database table. The data in the pivot table itself is not a problem.
I have a text box below the pivot table. Let's say the pivot table has: First Name, Last Name, and Address. When I click on a record in the pivot table, I want the Address for that selected record to be displayed in the textbox below.
What I have currently...
|
by: Adu |
last post by:
Pivot Tables
--------------------------------------------------------------------------------
Hi guys,
I have two questions:
First Question: I have created a Pivot Table on my Access database. I don't know much about them. However anytime I click the button on my form to view it. It gives me a form view results instead of Pivot Table view. I check the properties and it is set to Pivot Table. What should I do?
The second question is...
|
by: benchpolo |
last post by:
I have data extracted from Access db to Excel with a pivot table. Somehow, I am having issues with the pivot table were it doesnt update the totals. For example, the first extract i did in Access has 6000 data rows and pivot table in excel should refresh on open and reflect the 6000 data rows.
2nd extract in Access has 10000 data rows when pivot table opens it doesn't refresh the summary count on the pivot table based on the 10000 data...
|
by: STUFIX |
last post by:
Hi all,
I created a pivot table that allowed users to select certain customers from a drop down list and display the relevant data - it worked fine but has now stopped allowing them to do that.
At the same time, I can no longer create a Pivot Table with page, row or column fileds. All of the Pivot Table menu items except Refresh Data have greyed out.
I've tried creating a new db with a simple table and pivot and it is the same issue, so...
|
by: mld01s |
last post by:
I really need help!!! I dont know if its possible to share pivot tables, or see pivot tables in other machines that the one where the tables were created.
This is what happens:
I created a database to track different daily results. In a form in that database I added a pivot table, this table lets me see the results, sort them, and everthing a pivot table should let users do. Below the pivot table on the same form I have some fields for...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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: 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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |