Home » SQL & PL/SQL » SQL & PL/SQL » NEED SQL HELP PLEASE
NEED SQL HELP PLEASE [message #422962] Mon, 21 September 2009 08:52 Go to next message
moorev
Messages: 5
Registered: September 2009
Junior Member
Migrating data and converting mainframe SAS code to Oracle/SQL.
Here's a mock-up of the table...

COL1 COL2 COL3 COL4
M1 P1 G1 T1
M2 P1 G2 T2
M3 P1 G3 T2
M4 P1 G3 T3

In SAS I could use this code to "collapse" those records into 1...
PROC SORT DATA=table NODUPKEY;
by COL2;

Using a SORT utility, I could do the same....
SORT FIELDS=(COL2)
SUM=NONE

In both cases the result would be.....
M1 P1 G1 T1


If SQL would let me, I'd do something like this.....
Select COL2, COL1, COL3, COL4
From TABLE
GROUP BY COL2

Theoretically the 4 rows would collapse into 1, keeping all fields from whichever of the rows it collapsed into.
However, SQL requires that the "GROUP BY" statement includes all fields in the 'SELECT" statement, so can't get this to work.

The other "theoretical" would be to be able to specify that I want to return all columns one the FIRST or LAST row where COL2 = P1.

Is there any way to accomplish this in SQL? I've tried several variations with no luck so far.



Re: NEED SQL HELP PLEASE [message #422965 is a reply to message #422962] Mon, 21 September 2009 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: NEED SQL HELP PLEASE [message #422966 is a reply to message #422965] Mon, 21 September 2009 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or even just detailed examples of what you want, based on the sample data you've shown.

I can cope with turning it into insert statements:
create table test_table (COL1 varchar2(10), COL2 varchar2(10), COL3 varchar2(10), COL4 varchar2(10))

insert into table test_table (COL1, COL2, COL3, COL4) values ('M1','P1','G1','T1');
insert into table test_table (COL1, COL2, COL3, COL4) values ('M2','P1','G2','T2');
insert into table test_table (COL1, COL2, COL3, COL4) values ('M3','P1','G3','T2');
insert into table test_table (COL1, COL2, COL3, COL4) values ('M4','P1','G3','T3');
Re: NEED SQL HELP PLEASE [message #422968 is a reply to message #422966] Mon, 21 September 2009 09:21 Go to previous messageGo to next message
moorev
Messages: 5
Registered: September 2009
Junior Member
I appreciate the tips, as am new to this forum.

Given the test table Jrowbottom illustrated, is it possible to code a SQL statement that would return all 4 columns of only one row where "COL2=P1"?
Re: NEED SQL HELP PLEASE [message #422969 is a reply to message #422968] Mon, 21 September 2009 09:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Which values would you want this row to display?

Would you want to see ('M1','P1','G1','T1'), or ('M2','P1','G2','T2'), or....
Re: NEED SQL HELP PLEASE [message #422971 is a reply to message #422969] Mon, 21 September 2009 09:29 Go to previous messageGo to next message
moorev
Messages: 5
Registered: September 2009
Junior Member
I know it sounds weird, but for this particular application, it doesn't matter which row. Just verifying that a row for COL2=P1 does exist and loading the column values of any one and only one row as a point of reference.
Re: NEED SQL HELP PLEASE [message #422973 is a reply to message #422971] Mon, 21 September 2009 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT col1
      ,col2
      ,col3
      ,col4
FROM   test_table
WHERE  COL2 = 'P1'
AND    rownum = 1;
Re: NEED SQL HELP PLEASE [message #422974 is a reply to message #422971] Mon, 21 September 2009 09:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to check that a row with the criteria COL2 = 'P1' exists as part of another query, you can use the syntax:
AND EXISTS (SELECT null
            FROM   test_table
            WHERE  col2 = 'P1'
Re: NEED SQL HELP PLEASE [message #422975 is a reply to message #422973] Mon, 21 September 2009 10:01 Go to previous messageGo to next message
moorev
Messages: 5
Registered: September 2009
Junior Member
That would be the obvious answer, but in the "real world" the table also contains multiple rows having other values in COL2(P2, P3, etc). I don't know how many rows have P1 in COL2, So I won't know what the ROWNUM is for retrieving the first row where COL2=P2, etc.
The "AND EXISTS" coding doesn't look like it will return the values in the other columns on the row.
I think what I'm "hearing" is that "it can't be done".
Re: NEED SQL HELP PLEASE [message #422976 is a reply to message #422975] Mon, 21 September 2009 10:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The rownum for the first returned row will ALWAYS be 1, since rownum is assigned during the fetching of the rows.
Re: NEED SQL HELP PLEASE [message #422978 is a reply to message #422975] Mon, 21 September 2009 10:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The 'AND rownum = 1' just tells the database to stop fetching rows after it's got the first one.

Rownum is a dynamic value, applied to the rows as they are retrieved from the database, but before they are Ordered. The first row fetched back from the Db always has Rownum = 1

Trust us - we know what we're talking about.

[drat - beaten to it]

[Updated on: Mon, 21 September 2009 10:12]

Report message to a moderator

Re: NEED SQL HELP PLEASE [message #422981 is a reply to message #422978] Mon, 21 September 2009 10:18 Go to previous messageGo to next message
moorev
Messages: 5
Registered: September 2009
Junior Member
Well, I certainly misuderstood the ROWNUM assignment. I honestly thought it was statis, referring to all rows in the source table (number 1 thru 999)!!

Trust?...that's why I asked! I've never before participated, but have used your forum as a source of reference for a while now.

THANKS!!!
Re: NEED SQL HELP PLEASE [message #422991 is a reply to message #422981] Mon, 21 September 2009 11:50 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not a priori think the meaning of a function (or pseudo-column) always refer to the reference:
SQL Reference
Chapter 3 Pseudocolumns
Section ROWNUM Pseudocolumn

Regards
Michel
Previous Topic: Replicate Rows
Next Topic: cursors with where in clause
Goto Forum:
  


Current Time: Fri Dec 09 11:52:38 CST 2016

Total time taken to generate the page: 0.18074 seconds