CODEWITHSUNDEEP
sqloracle-databasepervasive
User456789
User456789

Reputation: 397

Pervasive sql to Oracle

I am having trouble converting this Pervasive sql query to Oracle. The errors that oracle is giving me does not help. It is giving me an error at SQL_Date.

This is what I have in Pervasive:

SELECT 
    (CASE 
      WHEN joefdt = 0 
        THEN NULL 
      WHEN joefdt > 0 
        THEN CONVERT(LEFT(CONVERT(joefdt, SQL_CHAR),4) + '-' + RIGHT(LEFT(CONVERT(joefdt, SQL_CHAR),6),2) + '-' + RIGHT(CONVERT(joefdt, SQL_CHAR),2),SQL_DATE) END) AS EFFECTIVEDATE, 
    (CASE 
      WHEN jopost = 0 
        THEN NULL 
      WHEN jopost > 0 
        THEN CONVERT(LEFT(CONVERT(jopost, SQL_CHAR),4) + '-' + RIGHT(LEFT(CONVERT(jopost, SQL_CHAR),6),2) + '-' + RIGHT(CONVERT(jopost, SQL_CHAR),2),SQL_DATE) END) AS POSTDATE, 
    (SELECT ltrim(rtrim(cdname)) from money.code WHERE cdtabl = 3 AND cdnumb = joetyp) AS TYPE, 
    (SELECT ltrim(rtrim(cdname)) from money.code WHERE cdtabl = 5 AND cdnumb = jodelm) AS DELIVERYMETHOD,
    joissd AS SHARESISSUED, jocand AS SHARESCANCELLED , joprsn AS BROKERID, joprsn AS TRANSACID  
FROM money.jour 
WHERE joiss = 1 AND joetyp in (2,3,4) AND (JOISSD > 0 OR JOCAND > 0) 
  AND joefdt >= '19920127' AND joefdt <= '20040726'
ORDER BY joefdt;

This is what I have tried, but it doesn't work:

SELECT 
    (CASE 
      WHEN joefdt = 0 
        THEN NULL 
      WHEN joefdt > 0 
        THEN CONVERT(substr(CONVERT(joefdt, SQL_CHAR),4) + '-' + RIGHT(LEFT(CONVERT(joefdt, SQL_CHAR),6),2) + '-' + RIGHT(CONVERT(joefdt, SQL_CHAR),2),SQL_DATE) END) AS EFFECTIVEDATE, 
    (CASE 
      WHEN jopost = 0 
        THEN NULL 
      WHEN jopost > 0 
        THEN CONVERT(substr(CONVERT(jopost, SQL_CHAR),4) + '-' + RIGHT(LEFT(CONVERT(jopost, SQL_CHAR),6),2) + '-' + RIGHT(CONVERT(jopost, SQL_CHAR),2),SQL_DATE) END) AS POSTDATE, ----Error at SQL_DATE
    (SELECT ltrim(rtrim(cdname)) from money.code WHERE cdtabl = 3 AND cdnumb = joetyp) AS TYPE, 
    (SELECT ltrim(rtrim(cdname)) from money.code WHERE cdtabl = 5 AND cdnumb = jodelm) AS DELIVERYMETHOD,
    joissd AS SHARESISSUED, jocand AS SHARESCANCELLED , joprsn AS BROKERID, joprsn AS TRANSACID  
FROM money.jour 
WHERE joiss = 1 AND joetyp in (2,3,4) AND (JOISSD > 0 OR JOCAND > 0) 
  AND joefdt >= '20000302' AND joefdt <= '20000302'
ORDER BY joefdt;

Upvotes: 0

Views: 289

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

In Oracle CONVERT function translates character string from one character set to another. It cannot be used to convert something to date (does Pervasive means this?) or something. Oracle does not know identifiers SQL_DATE and SQL_CHAR, so you cannot use them in this manner. And last, conditions like joefdt >= '20000302' are doubtful. Maybe it means something like joefdt >= DATE '2000-03-02'

Upvotes: 1

Related Questions