Re: Help witth CConverting a Query

Bill Lucas wrote:

>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
>
>
>

Rather than posting TSQL why don't you try describing what you are trying to do in plain English.

Because what you seem to be doing is do convoluted I can't imagine what the actual purpose is supposed to be.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)