[dhatfield]

I'm trying to query an oracle 8i database using vb.net front end. Attempting a left join query that will return the first record only from the right table. The SQL statement works great if I remove the section that calls for just the first record in the right table:

First(OP.RESOURCE_ID) as FirstID

I'm getting the error
ORA-00904: Invalid Column Name

I've never written a query to do this but I know I have to be close. I can get the query to work when written entirely using MS-ACCESS 2003 to assist me with syntex. Can someone tell me what I'm doing wrong??

Thanks!

		Dim rsPrint As New ADODB.Recordset
		Dim strSQL As String

		strSQL = "SELECT WO.DRAWING_ID, WO.DRAWING_REV_NO, WO.PART_ID, WO.BASE_ID, WO.DESIRED_WANT_DATE, WO.STATUS, WO.DESIRED_QTY, First(OP.RESOURCE_ID) as FirstID  " _
		& "FROM Sysadm.Work_Order WO, SYSADM.OPERATION OP where WO.BASE_ID = OP.WORKORDER_BASE_ID " _
		& "GROUP BY WO.DRAWING_ID, WO.DRAWING_REV_NO, WO.PART_ID, WO.BASE_ID, WO.DESIRED_WANT_DATE, WO.STATUS, WO.DESIRED_QTY, WO.TYPE, WO.PRINTED_DATE " _
		& "HAVING (((WO.STATUS='R') Or (WO.STATUS='F')) AND (TYPE='W') AND (WO.PRINTED_DATE Is Null)) " _
		& "ORDER BY BASE_ID"

		With rsPrint
			.ActiveConnection = MatCon
			.LockType = ADODB.LockTypeEnum.adLockReadOnly
			.CursorType = ADODB.CursorTypeEnum.adOpenStatic
			.Open(strSQL)
		End With

[P4L]

One thing I see is change the "having" statement to your "where" statement. If this is for SQL Server, try using "TOP 1" or "max".

TOP 1(OP.RESOURCE_ID) as FirstID

or

Max(OP.RESOURCE_ID) as FirstID

[dhatfield]

P4L, on 12 Dec, 2008 - 07:28 AM, said:

One thing I see is change the "having" statement to your "where" statement. If this is for SQL Server, try using "TOP 1" or "max".

TOP 1(OP.RESOURCE_ID) as FirstID

or

Max(OP.RESOURCE_ID) as FirstID

TOP 1 and MAX dont' return the same results. I noticed after trying this I'm missing something from my query anyway. I needed to sort by OP.Sequence_No. The resource ID is the department/machine number that the current operation will be done at. I need the FIRST operation's resource ID, which is probably not the Top 1 and definitely not the MAX one.

For now I'm grabbing all of the records without the FIRST comamnd and then filtering in my code with IF statements... a solution that works, though not very "economical".

If anyone else has any ideas???