Home » SQL & PL/SQL » SQL & PL/SQL » data from 3 tables and last row (merged)
data from 3 tables and last row (merged) [message #320540] Thu, 15 May 2008 07:53 Go to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
Hi to all?

I have this code in sql:

for three tables

Table1---->ID1,DES1
Table2-->ID2,DES2(an ID1 like foreign key)
Table3-->ID3,DES3(an ID2 like foreign key)

for the group of table1,table2 I want to achive the last row of table3, they are all connected together!I can solve this in SQL
and is here

SELECT DISTINCTROW Table1.Des1, Table2.Des2, Last(Table3.Des3) AS [First Of Des3], Max(Table3.Faza) AS [Max Of Faza]
FROM (Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID1) INNER JOIN Table3 ON Table2.ID2 = Table3.ID2
GROUP BY Table1.Des1, Table2.Des2;

but what is the code in pl/sql for this preoblem!

Thanks iin advance!

[mod-edit] ooops, I typoed the merged subject title.

[Updated on: Fri, 16 May 2008 07:44] by Moderator

Report message to a moderator

Re: THe code in PL/SQL for the last row? [message #320541 is a reply to message #320540] Thu, 15 May 2008 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Last(Table3.Des3) AS [First Of Des3]

Doesn't sound like Oracle. Did you, perhaps, miss the forum (or am I wrong about it)?
Re: THe code in PL/SQL for the last row? [message #320542 is a reply to message #320540] Thu, 15 May 2008 07:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no concept of a 'Last Row' in a Oracle.

The only way that you can guarantee that a set of rows will always return in the same order is by including an Order By clause.

Your query has no Order By, and so there is no proper Last Row.

Out of curiosity, what database did that query come from, because it's not an Oracle Db.
Re: THe code in PL/SQL for the last row? [message #320543 is a reply to message #320540] Thu, 15 May 2008 07:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
bmdat wrote on Thu, 15 May 2008 08:53
Hi to all?

I have this code in sql:

for three tables

Table1---->ID1,DES1
Table2-->ID2,DES2(an ID1 like foreign key)
Table3-->ID3,DES3(an ID2 like foreign key)

for the group of table1,table2 I want to achive the last row



There is no such thing as a last row in a relational database. Pick a ball out of a basket of balls. Keep repeating. When there are none left, you have chosen the last ball. Now, put all of the balls back and repeat. When there are no balls left, you have the last ball. It may be the same ball, it may not.
Re: THe code in PL/SQL for the last row? [message #320544 is a reply to message #320543] Thu, 15 May 2008 08:02 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
This is SQL code....

SELECT DISTINCTROW Table1.Des1, Table2.Des2, Last(Table3.Des3) AS [First Of Des3], Max(Table3.Faza) AS [Max Of Faza]
FROM (Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID1) INNER JOIN Table3 ON Table2.ID2 = Table3.ID2
GROUP BY Table1.Des1, Table2.Des2;

I want to have this code in pl/sql?

Re: THe code in PL/SQL for the last row? [message #320545 is a reply to message #320540] Thu, 15 May 2008 08:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I want to have this code in pl/sql?

SELECT DISTINCTROW Table1.Des1, Table2.Des2, Last(Table3.Des3) AS [First Of Des3], Max(Table3.Faza) AS [Max Of Faza]
FROM (Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID1) INNER JOIN Table3 ON Table2.ID2 = Table3.ID2
GROUP BY Table1.Des1, Table2.Des2;
Re: THe code in PL/SQL for the last row? [message #320547 is a reply to message #320540] Thu, 15 May 2008 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to do something in PL/SQL when you have a SQL to do it?

Regards
Michel
Re: THe code in PL/SQL for the last row? [message #320551 is a reply to message #320547] Thu, 15 May 2008 08:16 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
Hi?

Beacause my program is working with oracle database! Smile
Re: THe code in PL/SQL for the last row? [message #320554 is a reply to message #320551] Thu, 15 May 2008 08:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hmmm, would I be right in thinking that what you are actually looking for, is the code that you posted above to be 'translated' into Oracle's 'flavour' of SQL?
Re: THe code in PL/SQL for the last row? [message #320557 is a reply to message #320554] Thu, 15 May 2008 08:28 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
Yes exactly ?! Smile

Or for this tables

Table1---->ID1,DES1
Table2-->ID2,DES2(an ID1 like foreign key)
Table3-->ID3,DES3,DES4(an ID2 like foreign key)

a query with : DES1,DES2 and the last row of Table3

with PL/SQL....

Re: THe code in PL/SQL for the last row? [message #320558 is a reply to message #320551] Thu, 15 May 2008 08:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll have to tell us what this LAST function does.

In fact, it would be good if you could provide us with a set of sample data, and the expected outcome of the code. We're good, but we're not mindreaders.
Re: THe code in PL/SQL for the last row? [message #320560 is a reply to message #320557] Thu, 15 May 2008 08:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The last row of Table3 when ordered by what?

There has to be an Order By to give a Last row.
Re: THe code in PL/SQL for the last row? [message #320563 is a reply to message #320560] Thu, 15 May 2008 08:35 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
Order by DES3
for ex....
Re: THe code in PL/SQL for the last row? [message #320564 is a reply to message #320557] Thu, 15 May 2008 08:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
with PL/SQL....
Nope, not with PL/SQL. PL/SQL is Oracle's proprietary procedural language. It is not the term used for Oracle's 'flavour' of SQL. Oracle's flavour of SQL is known as .... wait for it .... SQL
Now as Monsiuer Rowbottom points out, you haven't quite told us what your query actually does. Your best bet would be to provide a test case and expected results.
Re: THe code in PL/SQL for the last row? [message #320565 is a reply to message #320564] Thu, 15 May 2008 08:38 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
a query with : DES1,DES2 and the last row of Table3
Re: THe code in PL/SQL for the last row? [message #320566 is a reply to message #320565] Thu, 15 May 2008 08:40 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, in true 'Allo 'Allo style: Listen very carefully, I shall say this only once (Quote from a Britich comedy show - not very funny)
Provide a test case. show us your data and your expected results. Post create table and insert statements so that we can easily replicate your situation and requirements.
Re: THe code in PL/SQL for the last row? [message #320568 is a reply to message #320566] Thu, 15 May 2008 08:48 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
I apologize...!
I think that I descripte this problem in my first post, what is unceleare...!!!
Re: THe code in PL/SQL for the last row? [message #320570 is a reply to message #320568] Thu, 15 May 2008 08:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Post a test case. Post create table and insert scripts. That way, there is no confusion.
Re: THe code in PL/SQL for the last row? [message #320636 is a reply to message #320565] Thu, 15 May 2008 13:30 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Who's on first?
I don't know.
No he's on third?
Who's on third?
No, I already said, Who's on first?
Data from 3 tables? [message #320791 is a reply to message #320540] Fri, 16 May 2008 04:43 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
I have three tables:

Table1-> ID1,DES1
Table2-> ID2,FK_ID2,DES2
Table3-> ID3,FK_ID3,DES3,Value

This is an pl/sql code and it works good

select table1.des1,table2.des2, max(table3.value)
from table1,table2,table3
where table1.id1=table2.fk_id2 and table2.id2=table3.fk_id3
group by table1.des1,table2.des2

here is my point:
I dosn't know how to get also des3 in the query
query must have (des1,des2,max(table3.value)and des3 for this max(table3.value))
Re: Data from 3 tables? [message #320794 is a reply to message #320791] Fri, 16 May 2008 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is an pl/sql code

This is a SQL statement.

Quote:
I dosn't know how to get also des3 in the query
query must have (des1,des2,max(table3.value)and des3 for this max(table3.value))

Use max in its analytic form.

Regards
Michel
Re: Data from 3 tables? [message #320834 is a reply to message #320794] Fri, 16 May 2008 07:07 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
OK ...!!!

Table1 is: ID1 DES1
--------------------
1 A
2 B

Table2 is: ID2 DES2 fk_ID2
--------------------------
122 AAA 1
321 DSS 1
786 DDA 2


Table3 is: FK_ID3 DES3 VALUE
--------------------------
122 ASGH 1
122 JWO 2

How to get query : DES1 DES2 DES3 MAX(VALUE)
------------------------------
A AAA JWO 2

you see this DES3 AND MAX(VALUE)is the last row of table3
Thanks in advance!




Re: Data from 3 tables? [message #320835 is a reply to message #320834] Fri, 16 May 2008 07:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Duplicate (needs to be merged) http://www.orafaq.com/forum/m/320568/105378/#msg_320568
Re: Data from 3 tables? [message #320837 is a reply to message #320835] Fri, 16 May 2008 07:13 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
but in previus question it was so confused....!!!

I thought to clearifay the question
Re: Data from 3 tables? [message #320838 is a reply to message #320837] Fri, 16 May 2008 07:20 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
It is only confused because you seem unwilling or unable to heed the words of those trying to help.
1. You have already been told the difference between SQL and PL/SQL (twice) yet you still insist on mixing the two up.
2. You were asked to provide a test case, you still have not done that.
3. You were advised to look at the max (analytic) function. I bet you haven't done that either.
It would appear that are are simply unwilling to put effort into solving your own problem and that you want others to write the solution for you. Is that the case?
Re: Data from 3 tables? [message #320842 is a reply to message #320834] Fri, 16 May 2008 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're still labouring under the misappreshension that just because a row appears last in a select list, it has some mystical property of 'Lastness'.

however, your example explains things well enough:
create table Table1 ( ID1 number,  DES1 varchar2(1));

insert into table1 values (1,'A');
insert into table1 values (2,'B');


create table Table2 (ID2 number, DES2 varchar2(3), fk_ID1 number);

insert into table2 values (122, 'AAA', 1);
insert into table2 values (321, 'DSS', 1);
insert into table2 values (786, 'DDA', 2);


create table Table3 (FK_ID2 number, DES3 varchar2(5), val number);

insert into table3 values (122, 'ASGH',  1);
insert into table3 values (122, 'JWO', 2);

-- Non Analytic query
select t1.des1
      ,t2.des2
      ,t3.des3
      ,t3.val
from   table1 t1
      ,table2 t2
      ,table3 t3
where  t1.id1 = t2.fk_id1
and    t2.id2 = t3.fk_id2
and    t3.val = (select max(t3_2.val) from table3 t3_2 where t3_2.fk_id2 = t3.fk_id2);

-- Analytic query
select distinct
       t1.des1
      ,t2.des2
      ,last_value(t3.des3) over (partition by t3.fk_id2 order by val asc rows between unbounded preceding and unbounded following) des3
      ,last_value(t3.val)  over (partition by t3.fk_id2 order by val asc rows between unbounded preceding and unbounded following) val
from   table1 t1
      ,table2 t2
      ,table3 t3
where  t1.id1 = t2.fk_id1
and    t2.id2 = t3.fk_id2
Re: Data from 3 tables? [message #320850 is a reply to message #320842] Fri, 16 May 2008 07:57 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
Thank you JRowbottom but When I test this code from above:

-- Analytic query
select distinct
t1.des1
,t2.des2
,last_value(t3.des3) over (partition by t3.fk_id2 order by val asc rows between unbounded preceding and unbounded following) des3
,last_value(t3.val) over (partition by t3.fk_id2 order by val asc rows between unbounded preceding and unbounded following) val
from table1 t1
,table2 t2
,table3 t3
where t1.id1 = t2.fk_id1
and t2.id2 = t3.fk_id2


I get this message

ORA-00439: feature not enabled: OLAP Windows Function



What is wrong !!
Re: Data from 3 tables? [message #320852 is a reply to message #320850] Fri, 16 May 2008 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your version and edition (select * from v$version)?

And FORMAT your post like others did: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Data from 3 tables? [message #320853 is a reply to message #320850] Fri, 16 May 2008 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of the Db are you using?
Re: Data from 3 tables? [message #320854 is a reply to message #320850] Fri, 16 May 2008 08:01 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Either you are on an earlier version that does not support analytic functions or more likely you are using Oracle Express version.
Re: Data from 3 tables? [message #320857 is a reply to message #320853] Fri, 16 May 2008 08:21 Go to previous messageGo to next message
bmdat
Messages: 12
Registered: May 2008
Junior Member
I am using

1 Oracle8i Release 8.1.7.0.0 - Production
2 PL/SQL Release 8.1.7.0.0 - Production
3 CORE 8.1.7.0.0 Production
4 TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
5 NLSRTL Version 3.4.1.0.0 - Production


but JRowbottom I solve the problem with yours
-- Non Analytic query

Thanks again very much !
Re: Data from 3 tables? [message #320865 is a reply to message #320857] Fri, 16 May 2008 08:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You really should consider upgrading to supported version of the software.
Previous Topic: bulk export
Next Topic: take the digit out of the string
Goto Forum:
  


Current Time: Sat Dec 03 08:25:04 CST 2016

Total time taken to generate the page: 8.87798 seconds