Query to return first record

ORA-00904: Invalid Column Name .. retrieving first record

Page 1 of 1

2 Replies - 3714 Views - Last Post: 15 December 2008 - 06:57 AM

#1 dhatfield  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-December 08

Query to return first record

Post icon  Posted 12 December 2008 - 07:12 AM

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




Is This A Good Question/Topic? 0
  • +

Replies To: Query to return first record

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Query to return first record

Posted 12 December 2008 - 08:28 AM

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


Was This Post Helpful? 0
  • +
  • -

#3 dhatfield  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-December 08

Re: Query to return first record

Posted 15 December 2008 - 06:57 AM

View PostP4L, 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???
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1