-
Hi,
I am trying to generate a report from remote database.
I have 4Q's each Q joines 2 tables.
Q1(travel_amt) , Q2(payroll_amt), Q3(received_amt),
Q4(voucher_amt).
I am trying to put these data in one table and then generate
my report. The problem is that the 4Q's has to be ordered by
job_no before I can put them in my table (TO MAKE SURE THAT I AM GETTING THE COREECT RECORD).
to do this, I've tried to create 4 views and then create my table based on these views. I couldn't do this because I wasn't able to create my views using order by( because views & order by is not supported in oracle8.0.5!!!! ).
any ideas !!!!!!! or maybe work around it.
thanks
-
What does the output of your queries look like?
What does the structure of the table look like?
Answers to these questions will help determine the approach to take.
-
Hi,
basically my report will look like this
job_no voucher_amt travel_amt payr_amt rec_amt
------- ---------------- ------------- ---------- ----------
Z232 xxxx xxxx xxx xxx
the structure of views:
vouch_v, travel_v, pay_v, rec_v
that is why I thought of creating a table reim_tab to hold
the amts for the 4 views.
the trick is when I create these views I have to use order by
to make sure the data is in order!!!!!!
[Edited by ocpdude on 11-09-2000 at 05:37 PM]
-
Sorry, don't mean to be dense, but...
1. Are you using "view" and "query" interchangeably here?
2. Do the views/queries also include the job number? If not, where is the job number in the table coming from?
-
May be I should explain more:
what is happing now to generte this report is basically
creating 4 seperate Q's in oracle report and put them
phiscally next to each other to look like they came from
one table.
job_no voucher_amt travel_amt payr_amt rec_amt
------- ---------------- ------------- ---------- ----------
Z001 10.00 15.00 5.00 14.00
Z002 23.00 17.00 16.00 13.00
-the job#, travel_amt have same table
-I have to put the result of the 4Q's in one table so I can
do manipulate them.
that is why I thougt it would be much easier to manipulate the data if I could create a view to hold these
data and then use the view to generate my report.
remember that each query has an order by.
tha is why I thought of creating 4 views
ex. create view travel_v as
select x from Q1
order by x
but the problem is I couldn't use order by in creating my views. so I've created the views without order by.
now the question is how can I create a table to hold the data
coming from my views in order???.
otherwise I might get something like this
job_no voucher_amt travel_amt payr_amt rec_amt
------- ---------------- ------------- ---------- ----------
Z001 23.00 15.00 16.00 14.13
Z002 10.00 17.00 5.00 13.0
rather than this
job_no voucher_amt travel_amt payr_amt rec_amt
------- ---------------- ------------- ---------- ----------
Z001 10.00 15.00 5.00 14.00
Z002 23.00 17.00 16.00 13.00
I hope this helped!!!!
[Edited by ocpdude on 11-09-2000 at 06:22 PM]
-
If you are using Oracle, you can't guarantee the data will be loaded into the table in any particular order - and it doesn't matter anyway (a basic tenet of RDBMS). You sort the data when you query it out of the table. So if you are generating a report from a query, just sort the data in the order you want it.
Perhaps if your queries aren't too long, you could post them. It might help sort out what you are trying to do. I am not sure what you mean by Q (I had assumed you meant query, but then you were trying to build a view as SELECT x FROM Q1, so now I'm not even sure of that anymore). But if you are successfully populating the table (as I believe you are saying), then just query the data out of it and sort it.
-
OK, I'm going to make some assumptions and suggest an approach.
Assumption 1. You have four queries, one each for the values you wish to display (travel, voucher, payr, and rec). I will refer to these queries as Q1..Q4. In the following code, you would replace each Q# with the actual SELECT statement.
Assumption 2. Each query includes the job# and voucher#.
Try something like this:
SELECT a.voucher#, a. job#, a.voucher_amt, b.travel_amt, c.payr_amt, d.rec_amt
FROM (Q1) a,
(Q2) b,
(Q3) c,
(Q4) d
WHERE a.voucher# = b.voucher#
AND a.job# = b.job#
AND b.voucher# = c.voucher#
AND b.job# = c.job#
AND c.voucher# = d.voucher#
AND c.job# = d.job#
ORDER BY a.voucher#, a.job#;
-
Hi Carp,
remember that I need to create one table to contain the 4 views(travel_amt, voucher_amt,payr_amt,rec_amt) I am creating. and then I would select whatever
I need and do my manipulations.
THE QUESTION IS HOW TO CREATE THIS TABLE?????and remember that when inserting records in this table they have to be in ORDER BY job_no to insure I am getting the correct record! please take alook at the example in the previous messages.
again:
every column is coming from different view( which combined 2 tables).
every single view has job_nbr but, it is not unique#.
the values for the job_no is identical in all 4 views.
but I only need to see it once !!
I hope this helped!!!!
-
Very confusing.. I think you want to get the data from four different tables and want to put them in one table.
If my asumptions are correct then you can join the tables by primary key fileds.
fyi... views don't hold any data physically. Only the SQL statement of the view is saved and data is always retrieved from the base table whenever you do a query on view.
Hope this helps.
Sanjay
-
Why can't you create the view without the order by, then when querying from your view do:
SELECT * FROM viewname
ORDER BY field;
????
You can query the view with ORDER BY even if it wasn't populated with an ORDER BY clause.
Am I over simplifying your needs?
