ORACLE ODBC JOIN ISSUE WITHIN CRYSTAL 8

[BRL123]

SELECT
METERREADINGS."READINGTYPE", METERREADINGS."KWHREAD", METERREADINGS."KWREAD", METERREADINGS."KVARHREAD", METERREADINGS."KVARREAD",
TRANSACTIONS."TRANSACTIONTYPE", TRANSACTIONS."DEVICEID", TRANSACTIONS."LOGDATE", TRANSACTIONS."LOGBY", TRANSACTIONS."RESERVED1", TRANSACTIONS."RESERVED2",
SERVICE."SERVICEID",
PREMISE."CITY", PREMISE."SERVICEADDRLINE1"
FROM
"MTS_E1"."METER" METER, "MTS_E1"."METERREADINGS" METERREADINGS, "MTS_E1"."TRANSACTIONS" TRANSACTIONS, "MTS_E1"."SERVICE" SERVICE, "MTS_E1"."PREMISE" PREMISE
WHERE
METER."METERID" = METERREADINGS."METERID" AND METER."METERID" = TRANSACTIONS."DEVICEID" AND SERVICE."SERVICEID" (+)= TRANSACTIONS."RESERVED1" AND (TRANSACTIONS."TRANSACTIONTYPE" = 'DRC' OR TRANSACTIONS."TRANSACTIONTYPE" = 'RCV' OR TRANSACTIONS."TRANSACTIONTYPE" = 'SET' OR TRANSACTIONS."TRANSACTIONTYPE" = 'RMV') AND TRANSACTIONS."DEVICEID" = '0101GE' AND SERVICE."PREMISENUMBER" = PREMISE."PREMISENUMBER"
ORDER BY
TRANSACTIONS."DEVICEID" ASC

THE RESERVED1 FIELD ON THE TRANSACTION TABLE IS ONLY POPULATED WITH A SERVICEID WITH THE TRANSACTION TYPES (SET AND RMV) HOWEVER I STILL NEED THE OTHER TWO TRANSACTION TYPES TO BE INCLUDED IN THE REPORT(ALTHOUGH THEY NEED NO INFORMATION FROM THE SERVICE TABLE) I HOPE THIS MAKES SENSE TO SOMEONE. THANKS

 

[LKBrwnDBA]

Try changing this:

...etc...
........= '0101GE' AND SERVICE."PREMISENUMBER"[b][COLOR=red](+)[/color][/b] = PREMISE."PREMISENUMBER"...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BRL123]

I am still only getting the transaction types(set and rmv)that have a service id in the reserved1 field.
There is a constraint that requires all of the premise numbers in the service table to match up with the premise table.
Is there something else that I am just not seeing?

 

[LKBrwnDBA]

THE RESERVED1 FIELD ON THE TRANSACTION TABLE IS ONLY POPULATED WITH A SERVICEID WITH THE TRANSACTION TYPES (SET AND RMV) HOWEVER I STILL NEED THE OTHER TWO TRANSACTION TYPES TO BE INCLUDED IN THE REPORT(ALTHOUGH THEY NEED NO INFORMATION FROM THE SERVICE TABLE)

Does'nt make sense, if the transaction table is only populated with a serviceid with transaction types (SET and RMV), where do the other transaction types come from?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BRL123]

Sorry, I wasn't sure how deep you wanted me to explain the data. There are many different transaction types. I am using only 4 on this report. The reserved fields are populated with different data depending on what the transaction type is. I need all 4 transaction types on the report. I only need the link to the service table on the SET and RMV transactions.
Does that make any more sense?

 

[LKBrwnDBA]

OK, maybe you need to manipulate the results:

SELECT
    METERREADINGS."READINGTYPE"
  , METERREADINGS."KWHREAD"
  , METERREADINGS."KWREAD"
  , METERREADINGS."KVARHREAD"
  , METERREADINGS."KVARREAD"
  , TRANSACTIONS."TRANSACTIONTYPE"
  , TRANSACTIONS."DEVICEID"
  , TRANSACTIONS."LOGDATE"
  , TRANSACTIONS."LOGBY"
  , TRANSACTIONS."RESERVED1"
  , TRANSACTIONS."RESERVED2"
  , CASE 
      WHEN TRANSACTIONS."TRANSACTIONTYPE" IN ('SET','RMV')
        THEN SERVICE."SERVICEID"
        ELSE NULL
    END AS "SERVICEID"
  , CASE 
      WHEN TRANSACTIONS."TRANSACTIONTYPE" IN ('SET','RMV')
        THEN PREMISE."CITY"
        ELSE NULL
    END AS "CITY"
  , CASE 
      WHEN TRANSACTIONS."TRANSACTIONTYPE" IN ('SET','RMV')
        THEN PREMISE."SERVICEADDRLINE1"
        ELSE NULL
    END AS "SERVICEADDRLINE1"
FROM
    "MTS_E1"."METER" METER
  , "MTS_E1"."METERREADINGS" METERREADINGS
  , "MTS_E1"."TRANSACTIONS" TRANSACTIONS
  , "MTS_E1"."SERVICE" SERVICE
  , "MTS_E1"."PREMISE" PREMISE 
WHERE
      METER."METERID" = METERREADINGS."METERID"
  AND METER."METERID" = TRANSACTIONS."DEVICEID" 
  AND TRANSACTIONS."DEVICEID" = '0101GE' 
  AND (TRANSACTIONS."TRANSACTIONTYPE" = 'DRC'
    OR TRANSACTIONS."TRANSACTIONTYPE" = 'RCV' 
    OR TRANSACTIONS."TRANSACTIONTYPE" = 'SET' 
    OR TRANSACTIONS."TRANSACTIONTYPE" = 'RMV')
  AND SERVICE."SERVICEID"(+) = TRANSACTIONS."RESERVED1"
  AND PREMISE."PREMISENUMBER"= SERVICE."PREMISENUMBER"
ORDER BY
     TRANSACTIONS."DEVICEID" ASC;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cmmrfrds]

I am curious of the use of a right join for sparsely needed data.
AND SERVICE."SERVICEID"(+) = TRANSACTIONS."RESERVED1"
In this case the where clause is not asking for the Null records on the left side of the Join for "drc" and "rcv".

A left join should fix in this case.
AND SERVICE."SERVICEID" = TRANSACTIONS."RESERVED1"(+)

or on the right join explicitly ask for the null records where
drc" and "rcv"