By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,002 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,002 IT Pros & Developers. It's quick & easy.

Convert MS Transact-SQL to Oracle SQL

P: 1
Hello you all,

I have this code that is written in MS Transact-SQL and I need to convert it to work with Oracle. Can anyone please help??? I need the code for a VBscript project.

Thanks!

Here is the code:

======

[code]USE [**********]

-- declare variables
DECLARE @Organization binary(16)
DECLARE @DeliveryRecipient binary(16)
DECLARE @InvoiceRecipient binary(16)
DECLARE @CommMethod binary(16)
DECLARE @MinDate decimal(17,0)
DECLARE @MaxDate decimal(17,0)


-- set the organization guid, please insert the correct code number
SET @Organization = (SELECT [GUID] FROM [ORGANIZATION002] WHERE
Expand|Select|Wrap|Line Numbers
  1.  = '9999999')
  2.  
  3.  
  4. -- set the guid for the partner relation type DeliveryRecipient and Invoicerecipient for further use
  5. SET @DeliveryRecipient = (SELECT [GUID] FROM [PARTNERRELATIONTYPE001] WHERE [NAME] = 'Deliveryrecipient')
  6. SET @InvoiceRecipient = (SELECT [GUID] FROM [PARTNERRELATIONTYPE001] WHERE [NAME] = 'Invoicerecipient')
  7. -- set email as communication method
  8. SET @CommMethod = (SELECT [GUID] FROM [COMMUNICATIONMETHOD] WHERE [NAME] = '300')
  9. -- set minimun and maximum date
  10. SET @MinDate = 11231231000000
  11. SET @MaxDate = 47121230000000000
  12.  
  13.  
  14. SELECT
  15. P.[NUMBER001] CustomerNumber,
  16. P.[NAME] CompanyName,
  17. P.[UPDATEINFOXDELETETIME] DeleteTime,
  18. P.[VALIDFROM] validFrom,
  19. P.[VALIDUNTIL] validUntil,
  20. -- customer address
  21. PA.[STREET] Street,
  22. PA.[POSTALCODE] ZipCode,
  23. PA.[CITY] City,
  24. PAC.[DESCRIPTION] Country,
  25. CD.[COMMDATA] EMail,
  26. -- address of the delivery recipient
  27. DRP.[NUMBER001] DeliveryRecipient,
  28. DRPA.[STREET] Street,
  29. DRPA.[POSTALCODE] ZipCode,
  30. DRPA.[CITY] City,
  31. DRPAC.[DESCRIPTION] Country,
  32. DRCD.[COMMDATA] EMail,
  33. -- address of the invoice recipient
  34. IRP.[NUMBER001] InvoiceRecipient,
  35. IRPA.[STREET] Street,
  36. IRPA.[POSTALCODE] ZipCode,
  37. IRPA.[CITY] City,
  38. IRPAC.[DESCRIPTION] Country,
  39. IRCD.[COMMDATA] EMail,
  40. -- tax number 
  41. PTI.[TAXIDENTIFICATIONNUMBER] taxNumber,
  42. PTI.[LOCALTAXIDENTIFICATIONNUMBER] LocalTaxNumber,
  43. -- =============================================
  44. -- web shop access control
  45. --
  46. C.[FALUWEBSHOPUSER] isUser,
  47. C.[FALUWEBSHOPUSERNAME] Username,
  48. C.[FALUWEBSHOPUSERNAME] Password
  49. -- =============================================
  50. FROM [PARTNER001] P
  51. -- just select partners who are customers for the specified organization
  52. JOIN [ORGANIZATIONALUNITPARTNER] OUP 
  53.     ON  OUP.[GUID] = P.[GUID] 
  54.     AND OUP.[ORGANIZATIONALUNIT] = @Organization
  55. JOIN [CUSTOMER002] C 
  56.     ON  C.[GUID] = P.[GUID] 
  57.     AND C.[ORGANIZATIONALUNIT] = @Organization
  58. LEFT OUTER JOIN [ADDRESSDATA] PA 
  59.     ON  PA.[GUID] = P.[ADDRESSDATA]
  60. LEFT OUTER JOIN [COUNTRY001] PAC 
  61.     ON  PAC.[GUID] = PA.[COUNTRY]
  62. LEFT OUTER JOIN [COMMUNICATIONDATA] CD
  63.     ON  CD.[PARTNER] = P.[GUID]
  64.     AND CD.[METHOD] = @CommMethod
  65. LEFT OUTER JOIN [PARTNERACCOUNTINGDATA] PAD
  66.     ON  PAD.[GUID] = P.[GUID] 
  67.     AND PAD.[ORGANIZATIONALUNIT] = @Organization
  68.     AND PAD.[VALIDUNTIL] > @MaxDate
  69.     AND PAD.[UPDATEINFOXDELETETIME] < @MinDate
  70. LEFT OUTER JOIN [PARTNERTAXIDENTIFICATION] PTI
  71.     ON  PTI.[GUID] = PAD.[GUID]
  72.     AND PTI.[VALIDUNTIL] > @MaxDate
  73.     AND PTI.[UPDATEINFOXDELETETIME] < @MinDate
  74. --
  75. -- resolving delivery recipient
  76. LEFT OUTER JOIN [PARTNERRELATION] DR 
  77.     ON  DR.[TYPE001] = @DeliveryRecipient 
  78.     AND DR.[SOURCE] = P.[GUID] 
  79.     AND DR.[SOURCEORGANIZATIONALUNIT] = @Organization 
  80.     AND DR.[VALIDUNTIL] > @MaxDate
  81. LEFT OUTER JOIN [PARTNER001] DRP 
  82.     ON  DRP.[GUID] = DR.[TARGET]
  83.     AND DRP.[VALIDUNTIL] > @MaxDate
  84. LEFT OUTER JOIN [ADDRESSDATA] DRPA 
  85.     ON  DRPA.[GUID] = DRP.[ADDRESSDATA]
  86. LEFT OUTER JOIN [COUNTRY001] DRPAC 
  87.     ON  DRPAC.[GUID] = DRPA.[COUNTRY]
  88. LEFT OUTER JOIN [COMMUNICATIONDATA] DRCD
  89.     ON  DRCD.[PARTNER] = DRP.[GUID]
  90.     AND DRCD.[METHOD] = @CommMethod
  91. --
  92. -- resolving invoice recipient
  93. LEFT OUTER JOIN [PARTNERRELATION] IR 
  94.     ON  IR.[TYPE001] = @InvoiceRecipient 
  95.     AND IR.[SOURCE] = P.[GUID] 
  96.     AND IR.[SOURCEORGANIZATIONALUNIT] = @Organization
  97.     AND IR.[VALIDUNTIL] > @MaxDate
  98. LEFT OUTER JOIN [PARTNER001] IRP 
  99.     ON  IRP.[GUID] = IR.[TARGET]
  100.     AND IRP.[VALIDUNTIL] > @MaxDate
  101. LEFT OUTER JOIN [ADDRESSDATA] IRPA 
  102.     ON  IRPA.[GUID] = IRP.[ADDRESSDATA]
  103. LEFT OUTER JOIN [COUNTRY001] IRPAC 
  104.     ON  IRPAC.[GUID] = IRPA.[COUNTRY]
  105. LEFT OUTER JOIN [COMMUNICATIONDATA] IRCD
  106.     ON  IRCD.[PARTNER] = IRP.[GUID]
  107.     AND IRCD.[METHOD] = @CommMethod
  108. --
  109. WHERE 
  110.     P.[VALIDUNTIL] > @MaxDate 
  111. AND P.[UPDATEINFOXDELETETIME] < @MinDate
  112. AND OUP.[CUSTOMER] = '1'
  113. ORDER BY P.[NUMBER001]
======
Jan 7 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.