• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1865
  • Last Modified:

Inner/Left Joins in same query

I have a query that I am doing inner joins and then a left join to link the same table together to get one result.  I have an error I can't figure out where it is.  

This is the error I am receiving:

AN ON CLAUSE IS INVALID. SQLCODE=-338, SQLSTATE=42972, DRIVER=3.61.65
SELECT 
   ISDB8.MVTITLESUBTX.SITECHANGEDESCRIPTION, ISDB8.MVTITLESUBTX.SITECHANGEREASONID, ISDB8.MVTITLESUBTX.STRGISRETURNEDSITE, 
   ISDB8.SUBTRANSACTION.CREATEDON, ISDB8.SUBTRANSACTION.SUBTRANSNUMBER,
ISDB8.SUBTRANSACTION.OFFICENUMBER, 
 ISDB8.OWNER.LASTNAME,  ISDB8.OWNER.FIRSTNAME, ISDB8.OWNER.MIDDLENAME, ISDB8.OWNER.SUFFIX1, 
   ISDB8.ADDRESS.ADDRESSLINE1, ISDB8.ADDRESS.CITY, ISDB8.ADDRESS.STATE1, 
   ISDB8.ADDRESS.ZIP, ISDB8.VEHICLE.YEAR1, ISDB8.VEHICLE.MAKE, 
   ISDB8.VEHICLE.VIN, ISDB8.TRANSACTION1.TRANSNUMBER, ISDB8.SHIPMENT.SHIPMENTNUMBER, 
   ISDB8.SHIPMENT.SHIPMENTDATE, ISDB8.SALEINFO.NETPRICE, ISDB8.FEECHARGED.FEEAMOUNT, 
   ISDB8.FEESCHEDULE.FEETYPEID, ISDB8.FEESCHEDULE.FEEUNITID, ISDB8.FEESCHEDULE.FEEAMOUNT as SCH_Fee_Amt, 
   ISDB8.MTAS.MTASCODE, ISDB8.MTAS.DESCRIPTION as MTAS_DESC, ISDB8.COUNTY.COUNTYNAME, 
   ISDB8.OPERATOR.OPERATORNUMBER, ISDB8.VM_SHIPMENTSTATUS.DESCRIPTION, 
ISDB8.OFFICE.OFFICENAME
FROM (ISDB8.MVTITLESUBTX
INNER JOIN  ISDB8.SUBTRANSACTION 
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
INNER JOIN  ISDB8.OWNER
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
INNER JOIN ISDB8.VEHICLE
ON  ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
INNER JOIN ISDB8.TRANSACTION1
ON ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
INNER JOIN ISDB8.SHIPMENT
ON ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
INNER JOIN ISDB8.SALEINFO
ON ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
INNER JOIN  ISDB8.FEECHARGED
ON ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
INNER JOIN ISDB8.FEESCHEDULE
ON  ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
INNER JOIN ISDB8.MTAS
ON ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
INNER JOIN ISDB8.COUNTY
ON ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
INNER JOIN ISDB8.OPERATOR
ON ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
INNER JOIN  ISDB8.VM_SHIPMENTSTATUS
ON ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
INNER JOIN ISDB8.OFFICE
ON ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER)

LEFT JOIN ISDB8.ADDRESS ADDRESS1
ON (ADDRESS1.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID)
LEFT JOIN ISDB8.ADDRESS ADDRESS2
ON (ADDRESS2.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID)
  
WHERE
   ADDRESS1.ADDRESSTYPEID = 6
   AND
   ADDRESS2.ADDRESSTYPEID = 3
   AND
   ISDB8.MVTITLESUBTX.SITECHANGEREASONID IS NOT NULL    
   AND
   ISDB8.FEESCHEDULE.FEETYPEID = 22
   AND
   ISDB8.FEESCHEDULE.FEEUNITID = 1;

Open in new window

0
us1975mc
Asked:
us1975mc
  • 8
  • 3
  • 2
  • +1
3 Solutions
 
mensoidCommented:
I need a few mroe details, in theory what your doing is fine, I would start by examining the datatypes of the on clauses see if something is incompatable there. I've never seen this specifc error (it usually tells me more details on the issue, what SQL engine are using, what interface to the DB are you using? Can you provide a DB schema so we can compare the datatypes/conditions your joining on?
0
 
us1975mcAuthor Commented:
Thanks for your response.

All the ID fields that are being done with the inner and left joins are all decimals in my DB2 database.  I am using both Quest for DB2 and a tool called IBM Data Studio.

The error I receive from Quest is:

   [DB2] SQL0338N  An ON clause associated with a JOIN operator or in a MERGE statement is not valid.  SQLSTATE=42972

Does this answer your questions?
0
 
momi_sabagCommented:
you can't have ADDRESS1.SUBTRAN_SUBTRANSACTIONID
this won't work

why do you have the inner join in ()
just remove it and use

FROM ISDB8.MVTITLESUBTX
      INNER JOIN  ISDB8.SUBTRANSACTION
            ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
      INNER JOIN  ISDB8.OWNER
            ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
      INNER JOIN ISDB8.VEHICLE
            ON  ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
      INNER JOIN ISDB8.TRANSACTION1
            ON ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
      INNER JOIN ISDB8.SHIPMENT
            ON ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
      INNER JOIN ISDB8.SALEINFO
            ON ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
      INNER JOIN  ISDB8.FEECHARGED
            ON ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
      INNER JOIN ISDB8.FEESCHEDULE
            ON  ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
      INNER JOIN ISDB8.MTAS
            ON ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
      INNER JOIN ISDB8.COUNTY
            ON ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
      INNER JOIN ISDB8.OPERATOR
            ON ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
      INNER JOIN  ISDB8.VM_SHIPMENTSTATUS
            ON ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
      INNER JOIN ISDB8.OFFICE
            ON ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER
            
      LEFT JOIN ISDB8.ADDRESS ADDRESS1
            ON (ADDRESS1.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID)
      LEFT JOIN ISDB8.ADDRESS ADDRESS2
            ON (ADDRESS2.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID)
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
us1975mcAuthor Commented:
Why won't this work:

ADDRESS1.SUBTRAN_SUBTRANSACTIONID

What should I have?

I removed the () as I was trying different options to get it to work.  

0
 
us1975mcAuthor Commented:
Do I need to use aliases because I need to compare the same table twice?  Is there a better way to accomplish my end goal - One result with 2 different addresses.
0
 
momi_sabagCommented:
you are ok
but since all your inner joins are within ()
outside of those () db2 no longer knows what address1 is
you have to alias the entrire () as a whole
0
 
us1975mcAuthor Commented:
I have taken all the ()  and still receive the same error message.

Below is the code.
LEFT JOIN ISDB8.ADDRESS ADDRESS1
ON ADDRESS1.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
LEFT JOIN ISDB8.ADDRESS ADDRESS2
ON ADDRESS2.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
  
WHERE
   ADDRESS1.ADDRESSTYPEID = 6
   OR
   ADDRESS2.ADDRESSTYPEID = 3
   AND
   ISDB8.MVTITLESUBTX.SITECHANGEREASONID IS NOT NULL    
   AND
   ISDB8.FEESCHEDULE.FEETYPEID = 22
   AND
   ISDB8.FEESCHEDULE.FEEUNITID = 1;

Open in new window

0
 
mensoidCommented:
for diagnositc purposes try this:

Select  [put a few fileds that are valid]

FROM ISDB8.SUBTRANSACTION
LEFT JOIN ISDB8.ADDRESS ADDRESS1
ON ADDRESS1.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
LEFT JOIN ISDB8.ADDRESS ADDRESS2
ON ADDRESS2.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID

and drop the where clause, let's break this down smaller to identiify the issue, then we can rebuild...

Also, I'm not 100% familiar with db2, is aliasing the table as you are allowed? Some variations of Interbase for example require a keyword (as) aka: LEFT JOIN ISDB8.ADDRESS as ADDRESS1
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

I simply removed parentheses, change LEFT JOIN to LEFT OUTER JOIN, and formatted it somewhat logically. Give it a try.

SELECT ISDB8.MVTITLESUBTX.SITECHANGEDESCRIPTION,
       ISDB8.MVTITLESUBTX.SITECHANGEREASONID,
       ISDB8.MVTITLESUBTX.STRGISRETURNEDSITE, 
       ISDB8.SUBTRANSACTION.CREATEDON,
       ISDB8.SUBTRANSACTION.SUBTRANSNUMBER,
       ISDB8.SUBTRANSACTION.OFFICENUMBER, 
       ISDB8.OWNER.LASTNAME,
       ISDB8.OWNER.FIRSTNAME,
       ISDB8.OWNER.MIDDLENAME,
       ISDB8.OWNER.SUFFIX1, 
       ISDB8.ADDRESS.ADDRESSLINE1,
       ISDB8.ADDRESS.CITY,
       ISDB8.ADDRESS.STATE1, 
       ISDB8.ADDRESS.ZIP,
       ISDB8.VEHICLE.YEAR1,
       ISDB8.VEHICLE.MAKE, 
       ISDB8.VEHICLE.VIN,
       ISDB8.TRANSACTION1.TRANSNUMBER,
       ISDB8.SHIPMENT.SHIPMENTNUMBER, 
       ISDB8.SHIPMENT.SHIPMENTDATE,
       ISDB8.SALEINFO.NETPRICE,
       ISDB8.FEECHARGED.FEEAMOUNT, 
       ISDB8.FEESCHEDULE.FEETYPEID,
       ISDB8.FEESCHEDULE.FEEUNITID,
       ISDB8.FEESCHEDULE.FEEAMOUNT as SCH_Fee_Amt, 
       ISDB8.MTAS.MTASCODE,
       ISDB8.MTAS.DESCRIPTION as MTAS_DESC,
       ISDB8.COUNTY.COUNTYNAME, 
       ISDB8.OPERATOR.OPERATORNUMBER,
       ISDB8.VM_SHIPMENTSTATUS.DESCRIPTION, 
       ISDB8.OFFICE.OFFICENAME
  FROM ISDB8.MVTITLESUBTX
  JOIN ISDB8.SUBTRANSACTION 
    ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
  JOIN ISDB8.OWNER
    ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
  JOIN ISDB8.VEHICLE
    ON ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
  JOIN ISDB8.TRANSACTION1
    ON ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
  JOIN ISDB8.SHIPMENT
    ON ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
  JOIN ISDB8.SALEINFO
    ON ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
  JOIN ISDB8.FEECHARGED
    ON ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
  JOIN ISDB8.FEESCHEDULE
    ON ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
  JOIN ISDB8.MTAS
    ON ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
  JOIN ISDB8.COUNTY
    ON ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
  JOIN ISDB8.OPERATOR
    ON ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
  JOIN ISDB8.VM_SHIPMENTSTATUS
    ON ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
  JOIN ISDB8.OFFICE
    ON ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER
  LEFT OUTER JOIN ISDB8.ADDRESS ADDRESS1
    ON ADDRESS1.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
  LEFT OUTER JOIN ISDB8.ADDRESS ADDRESS2
    ON ADDRESS2.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
 WHERE ADDRESS1.ADDRESSTYPEID = 6
   AND ADDRESS2.ADDRESSTYPEID = 3
   AND ISDB8.MVTITLESUBTX.SITECHANGEREASONID IS NOT NULL    
   AND ISDB8.FEESCHEDULE.FEETYPEID = 22
   AND ISDB8.FEESCHEDULE.FEEUNITID = 1
;

Open in new window

0
 
us1975mcAuthor Commented:
Thanks for the response.  I received the same error.

AN ON CLAUSE IS INVALID. SQLCODE=-338, SQLSTATE=42972, DRIVER=3.61.65
0
 
mensoidCommented:
Was that to me or Daveslash?
0
 
us1975mcAuthor Commented:
It could be directed to both   I have tried both with no success.  
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
All I can suggest is try peeling off the JOIN's one by one. Of course, you'll have to adjust the list of selected fields for each layer, but I'd just change it to "select *" until you find the offending clause.

Alternately, you could start with the simple case (one table, no joins) and add the JOIN clauses one by one until it fails.

HTH
DaveSlash
0
 
us1975mcAuthor Commented:
Thanks for response. I'll give it a try.
0
 
us1975mcAuthor Commented:
THANKS!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 8
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now