Oracle to Sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Oracle to Sql

Author

Topic

bhabbu
Starting Member

1 Post

Posted - 2012-09-05 : 04:52:35

Hello All,

I had the following oracle query i need to convert to sql query.Can any one help me how to do

--select sys_context( 'userenv', 'current_schema' ) from dual;
select HT.PROTOCOLNO, HT.LOTNO, HT.TRAYNO, HT.HYDRATIONSTEP, HT.CYCLETIME, HT.MOVEIN_TIME, HT.MOVEOUT_TIME, HP.SOLTYPENAME, HP.SOLTYPEVALUE, HP.SOLTYPECAPTUREDON,
HP.SOLTEMPNAME, HP.SOLTEMPVALUE, HP.target,HP.upperlimit,HP.lowerlimit, HP.SOLTEMPCAPTUREDON, HT.HYDRATIONTANK,
HT.SCRAPPED
from
(
SELECT lot.CONTAINERNAME lotno
, trayname.CONTAINERNAME trayno
, p.MFGORDERNAME protocolno
, wfs.WORKFLOWSTEPNAME HydrationStep
, (mh.CYCLETIME * 1440) CYCLETIME
, (HML.TXNDATE - mh.cycletime) MOVEIN_TIME
, hml.TXNDATE MOVEOUT_TIME
, case when TrayName.LotContainerId is null then 'Yes' else ' ' end scrapped
, RESOURCEUSED.RESOURCENAME HydrationTank
FROM Container Lot
INNER JOIN Container Tray ON (Lot.ContainerId = Tray.LotContainerId or Tray.CONTAINERNAME like lot.CONTAINERNAME + '-%')
INNER JOIN MoveHistory mh ON Tray.ContainerId = mh.HistoryId
INNER JOIN WorkflowStep wfs ON wfs.WorkflowStepId = mh.StepId
INNER JOIN HistoryMainline hml ON mh.HistoryMainlineId = hml.HistoryMainlineId
INNER JOIN HistoryCrossRef hcr on (hml.HistoryId = hcr.HistoryId
AND hml.TxnId >= hcr.StartTxnId
AND hml.TxnId <= hcr.EndTxnId)
INNER JOIN Container TrayName ON TrayName.ContainerId = hcr.TrackingId
INNER JOIN MfgOrder p ON Lot.MfgOrderId = p.MfgOrderId
INNER JOIN VistProtocolParameterlist Param ON
(wfs.WorkflowStepId = Param.StepId AND LOT.ContainerName = Param.LotNumber)
INNER JOIN cdodefinition cdo on cdo.CDODEFID = mh.CDOTYPEID
inner join (
select L.CONTAINERNAME lotno, WFS.WORKFLOWSTEPNAME stepname, HML.TXNTYPE, R.RESOURCENAME
from container l
, historymainline hml
, resourcedef r
, mfgorder p
, workflowstep wfs
where L.CONTAINERID = HML.CONTAINERID
and HML.RESOURCEID = R.RESOURCEID
and P.MFGORDERID = L.MFGORDERID
and P.MFGORDERNAME = @ProtocolNo
and HML.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
and HML.TXNTYPE = '4718612'
) RESOURCEUSED on RESOURCEUSED.LOTNO = LOT.CONTAINERNAME and RESOURCEUSED.stepname = WFS.WORKFLOWSTEPNAME

WHERE p.MfgOrdername = ProtocolNo AND
Param.IsHydrationTime = 1
AND mh.CDOTYPEID <> '6915' -- filter out Transfer transaction (from Start Tray)
AND mh.TXNID = hml.TxnId
--Order by p.MFGORDERNAME, lot.CONTAINERNAME, trayname.CONTAINERNAME, mh.STEPID
) HT,

(
select p.mfgordername protocolno
, l.CONTAINERNAME lotno
, t.CONTAINERNAME trayno
, wfs.WORKFLOWSTEPNAME HydrationStep
, soltypename.PARAMETERNAME SOLTYPENAME
, soltypevalue.PARAMETERVALUE SOLTYPEVALUE
, soltypevalue.MOVEINTXNDATETIME SOLTYPEcapturedon
, soltempname.PARAMETERNAME SOLTEMPNAME
, soltempvalue.PARAMETERVALUE SOLTEMPVALUE
, soltempvalue.MOVEINTXNDATETIME SOLTEMPcapturedon
, soltempname.parametervalue target
,soltempname.upperlimit upperlimit
,soltempname.lowerlimit lowerlimit
from container l
, container t
, mfgorder p
, workflowstep wfs
, vistprotocolparameterlist soltypename
, edhrd.visthydrationparamresults soltypevalue
, vistprotocolparameterlist soltempname
, edhrd.visthydrationparamresults soltempvalue
where t.LOTCONTAINERID = l.CONTAINERID
and p.MFGORDERID = l.MFGORDERID
and p.MFGORDERID = t.MFGORDERID
and soltypevalue.VISTTRAYCONTAINERID = t.CONTAINERID
and soltypevalue.WORKFLOWSTEPID = wfs.WORKFLOWSTEPID
and soltypename.MFGORDERID = p.MFGORDERID
and soltypename.LOTNUMBER = l.CONTAINERNAME
and soltypename.STEPID = wfs.WORKFLOWSTEPID
and soltypename.PARAMETERNAME = soltypevalue.PARAMETERNAME
and soltypename.ISSOLUTIONTYPE = 1
and soltempvalue.VISTTRAYCONTAINERID = t.CONTAINERID
and soltempvalue.WORKFLOWSTEPID = wfs.WORKFLOWSTEPID
and soltempname.MFGORDERID = p.MFGORDERID
and soltempname.LOTNUMBER = l.CONTAINERNAME
and soltempname.STEPID = wfs.WORKFLOWSTEPID
and soltempname.PARAMETERNAME = soltempvalue.PARAMETERNAME
and soltempname.ISSOLTEMPERATURE = 1
and p.MfgOrdername = :ProtocolNo
--order by p.mfgordername, l.containername, t.CONTAINERname, wfs.WORKFLOWSTEPID, soltypevalue.PARAMETERNAME
) HP
where HT.PROTOCOLNO = HP.PROTOCOLNO
and HT.LOTNO = HP.LOTNO
and HT.TRAYNO = HP.TRAYNO
and HT.HYDRATIONSTEP = HP.HYDRATIONSTEP
order by 1,2,3,4

bharat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-05 : 21:21:22

did you try running this in sql server. so far as it is ANSI related syntax, it should work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources