Help witth CConverting a Query

From: Bill Lucas <Homebrew42_at_hotmail.ccom>
Date: Tue, 02 Sep 2003 22:51:14 GMT
Message-ID: <C995b.35184$Nc.7548929@news1.news.adelphia.net>

We are redoing our backend in Oracle 9i 9.2 (I think).

The original back end was written for MSSQL Server 2000 (They wanted tithe MSDE Version for the 1 to 5 user market).

We are now finishing up the Enterprise Version (Which will run on Oracle).

I am trying to convert the following TSQL Statement into its PLSQL Equivalent

  UPDATE @tblRollbackInfo

   SET LastActiveDueDate = DueDate,
    NewLAD = HistoryDate
  FROM tblHistories
  WHERE HistoryID = (SELECT TOP 1 HistoryID

       FROM tblHistories
       WHERE EventID = RBEventID AND Status & @BMV_STATUS_DELETED = 0
       ORDER BY CASE
          WHEN ISDATE(DueDate) = 1 THEN CAST(DueDate AS datetime)
          ELSE        NULL
          END DESC)

The statement is used to gather information for rolling back event due dates when a history for the event is deleted.

I am having problems writing the same thing is PLSQL

I came up with this but it will not compile because it loses reference to the tmpRollbackinfo in the derived table select

Here is the current PLSQL Code I have

UPDATE tmpRollbackInfo RB
SET (LastActiveDueDate, NewLAD)=
(SELECT H.DueDate, H.HistoryDate
FROM tblHistories H
WHERE HistoryID = (SELECT HistoryID
FROM (SELECT HistoryID
FROM tblHistories H2

WHERE EventID = RBEVENTID AND            <<<<<<<<<<<<<<<<<<<< Problem is

right here with this RBEventID Which is a column in tmpRollbackInfo BITAND(H2.Status, eSTATUS_DELETED.BitValue) = 0 ORDER BY CASE
WHEN BMQR_UTIL.IsDate(H2.DueDate,
BMQR_COMMON.GetDateConvertFormat(recUserDetails.DatasetID)) = 1 THEN to_Date(H2.DueDate,
BMQR_COMMON.GetDateConvertFormat(recUserDetails.DatasetID)) ELSE NULL
END DESC)
WHERE RowNum = 1));

Code Notes
BMQR_UTIL.IsDate Custom Function that checks to make sure the value provided is a date in a format specific to a dataset Returns 0 or 1 BMQR_COMMON.GetDateConvertFormat Returns a string with the format for all dates within a user defined dataset Sample output 'MM/DD/YYYY'

I understand that without the DDL and the rest of the supporting functions it is impossible to run this query. I can try to provide some of the package code, but I have to jump through hoops to get permission before posting source code on a public forum. So if someone can tell me an alternate way to try to accomplish this from what I provided that would be great, if not, email me AT WCLucas AT COOLBLUE DOT COM and I should be able to provide enough information to anyone willing to look into it and help me.

Much Thanks

Bill Received on Tue Sep 02 2003 - 17:51:14 CDT