Conerting ANSI to Jet 2

[miller1975]

Hello all,
Here is a portion of query I am running from DB2. Would like to convert it to Access (Jet) sql.
Please help

SELECT A.CUSTOMER_NBR, A.CONTRACT_ID,
A.BRANCH_CO_NBR, A.BRANCH_NBR, C.BRANCH_NAME,
D.DEALER_NBR, D.DEALER_NM, D.SOG_NBR, E.SOG_NAME,
A.CNTR_BILL_ADDR,
VALUE(I.LAST_NAME,'?')               AS BILLTO_NAME,
A.ACCT_PLAN_ID, A.SETUP_DATE, F.CONT_COLL_STAT,
VALUE(J.PSYS_EMPLOYEE_ID,'????????') AS EMPLOYEE_WORKING,
F.RT_DAYS_PST_DUE, A.CYCLE_DAY,
A.TOTAL_RENT                         AS CONTRACT_BAL,
A.TOTAL_RENT + B.TOT_NOW_DUE         AS TOT_CONTRACT_BAL,
B.TOT_NOW_DUE                        AS RENT_NOW_DUE,
B.TOT_CURRENT_AGED AS RENT_CURR_AGED,
B.TOT_OVER_30_AGED AS RENT_OV30_AGED,
B.TOT_OVER_60_AGED AS RENT_OV60_AGED,
B.TOT_OVER_90_AGED AS RENT_OV90_AGED,
B.TOT_OVER_120_AGD AS RENT_OV120_AGED,
B.TOT_150_AGED_AMT AS RENT_OV150_AGED,
B.TOT_210_AGED_AMT + B.TOT_240_AGED_AMT + B.TOT_270_AGED_AMT +
B.TOT_300_AGED_AMT + B.TOT_330_AGED_AMT + B.TOT_360_AGED_AMT +
B.TOT_180_AGED_AMT                   AS RENT_OV180_AGED,
VALUE(J.PSYS_TEAM_ID,'????')         AS TEAM,
VALUE(H.LEGAL_NAME,' ')              AS CUSTOMER_NAME

FROM ACRM1.CONTRACT A
ACRM1.SCHEDULE_TOTALS AS B
ACRM1.BRANCH AS C
ACRM1.DEALER AS D
ACRM1.SOG AS E
ACRM1.CONTRACT_II AS F
ACRM1.CUSTOMER AS H
ACRM1.CONTRACT_NAMES AS I
ACRM1.CUST_CONT_TEAM AS J
WITH UR

 

[PHV]

Simply replace 'VALUE(' by 'Nz('
But where are the joins condtions in your post ?
what is the meaning of 'WITH UR' ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

miller1975

If you need us to help you with converting SQL statements, you need to give us the FULL statement, not just part of it.


PHV


UR is an isolation clause on DB2. It means "Uncommitted Read" and is defined as
Uncommitted Read (UR)

For a SELECT INTO, a FETCH with a read-only cursor, subquery, or subselect used in an INSERT statement, level UR allows:

* Any row read during the unit of work to be changed by other activation groups that run under a different commitment definition.
* Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group running under a different commitment definition to be read even if the change has not been committed.

On this particular case it will only be important if the query is used on a pass through query, not if done on Access or linked tables.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[miller1975]

Frederico,
Here is the full thing
Check ur mail also...

SELECT A.CUSTOMER_NBR, A.CONTRACT_ID,
A.BRANCH_CO_NBR, A.BRANCH_NBR, C.BRANCH_NAME,
D.DEALER_NBR, D.DEALER_NM, D.SOG_NBR, E.SOG_NAME,
A.CNTR_BILL_ADDR, VALUE(I.LAST_NAME,'?') AS BILLTO_NAME,
A.ACCT_PLAN_ID, A.SETUP_DATE, F.CONT_COLL_STAT,
VALUE(J.PSYS_EMPLOYEE_ID,'????????') AS EMPLOYEE_WORKING,
F.RT_DAYS_PST_DUE, A.CYCLE_DAY,
A.TOTAL_RENT                         AS CONTRACT_BAL,
A.TOTAL_RENT + B.TOT_NOW_DUE         AS TOT_CONTRACT_BAL,
B.TOT_NOW_DUE                        AS RENT_NOW_DUE,
B.TOT_CURRENT_AGED AS RENT_CURR_AGED,
B.TOT_OVER_30_AGED AS RENT_OV30_AGED,
B.TOT_OVER_60_AGED AS RENT_OV60_AGED,
B.TOT_OVER_90_AGED AS RENT_OV90_AGED,
B.TOT_OVER_120_AGD AS RENT_OV120_AGED,
B.TOT_150_AGED_AMT AS RENT_OV150_AGED,
B.TOT_210_AGED_AMT + B.TOT_240_AGED_AMT + B.TOT_270_AGED_AMT +
B.TOT_300_AGED_AMT + B.TOT_330_AGED_AMT + B.TOT_360_AGED_AMT +
B.TOT_180_AGED_AMT                   AS RENT_OV180_AGED,
VALUE(J.PSYS_TEAM_ID,'????')         AS TEAM,
VALUE(H.LEGAL_NAME,' ')              AS CUSTOMER_NAME

FROM ACRM1.CONTRACT    A

INNER JOIN ACRM1.SCHEDULE_TOTALS AS B
   ON   B.CONTRACT_ID   = A.CONTRACT_ID
   AND  B.CUSTOMER_NBR  = A.CUSTOMER_NBR
   AND  B.SCHEDULE      = 'B'
   AND  B.SCHEDULE_TYPE = 'RENT'

INNER JOIN ACRM1.BRANCH AS C
   ON   C.BRANCH_NBR    = A.BRANCH_NBR
   AND  C.BRANCH_CO_NBR = A.BRANCH_CO_NBR

INNER JOIN ACRM1.DEALER AS D
   ON   D.DEALER_NBR    = C.DEALER_NBR
   AND  D.DEALER_CO_NBR = C.DEALER_CO_NBR

INNER JOIN ACRM1.SOG AS E
   ON   E.SOG_NBR    = D.SOG_NBR
   AND  E.SOG_CO_NBR = D.SOG_CO_NBR

INNER JOIN ACRM1.CONTRACT_II AS F
   ON   F.CONTRACT_ID  = A.CONTRACT_ID
   AND  F.CUSTOMER_NBR = A.CUSTOMER_NBR

INNER JOIN ACRM1.CUSTOMER AS H
   ON   H.CUSTOMER_NBR = A.CUSTOMER_NBR

INNER JOIN ACRM1.CONTRACT_NAMES AS I
   ON   I.CONTRACT_ID  = A.CONTRACT_ID
   AND  I.CUSTOMER_NBR = A.CUSTOMER_NBR
   AND  I.NAME_TYPE     = 'BLTO'

LEFT OUTER JOIN ACRM1.CUST_CONT_TEAM AS J
   ON   J.CUSTOMER_NBR = A.CUSTOMER_NBR
   AND  J.CONTRACT_ID  = A.CONTRACT_ID

WHERE  (A.CONTRACT_STATUS = 'A'
        OR (A.CONTRACT_STATUS  = 'T'
            AND A.CLOSE_BILL_IND <> 'T'))
  AND  A.ACCT_PLAN_ID NOT IN ('OPR','OPRC')

WITH UR

 

[PHV]

In addition to my previous suggestion you have to move the conditions against constants from the JOIN clauses to the WHERE clause (eg AND B.SCHEDULE = 'B') and get rid of the WITH UR stuff.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[miller1975]

The UR clause will sure go away..when its converted to Access SQL..as Access doenst support UR..