Genral SQL Server Error. Check the Messages from SQL Server

[dbielenda]

After going through my report field by field, formula by formula, I have finally discovered why I get this error --> a left outer join on BILP351C.TRNTPC *= SAWP652E.TRNTYE. When I take this out of the query, I get records. Here is the query that Crystal generates:

SELECT
BILP350C.BILLC, BILP350C.BILAUC, BILP350C.BILAMC, BILP350C.BILJLC, BILP350C.INVNOC, BILP350C.REFNOC, BILP350C.CEMMC, BILP350C.CEMYC,BILP352F.OCOMMF,
BILP351C.STKVLC, BILP351C.UOMCDC, BILP351C.ZONEC, BILP351C.AISLEC, BILP351C.BINC, BILP351C.LEVELC,
INVP100A.ITEMA, BILP351C.LOCVLC, BILP351C.LOCFSC, BILP305E.RUNDSE, BILP351C.DIMUMC, SAWP700A.NAMEA, SAWP700A.CITYA, SAWP700A.STATEA, SAWP700A.ADD1A, SAWP700A.ADD2A, SAWP700A.ADD3A, SAWP700A.ZIPA, SAWP700A.CONTYA, SAWP700A.CNTRA, SAWP652E.TRDSCE
FROM
BILP350C BILP350C,
BILP352F BILP352F,
BILP351C BILP351C,
BILP305E BILP305E,
INVP100A INVP100A,
SAWP700A SAWP700A,
SAWP652E SAWP652E
WHERE
BILP350C.BILLC *= BILP352F.BILLF AND
BILP350C.BILAUC *= BILP352F.BILAUF AND
BILP350C.BILLC *= BILP351C.BILLC AND
BILP350C.BILAUC *= BILP351C.BILAUC AND
BILP350C.RTUNTC *= BILP305E.RTUNTE AND
BILP350C.ITEMC *= INVP100A.ITEMA AND
BILP350C.OWNERC = SAWP700A.OWNERA AND
BILP350C.OWNCOC = SAWP700A.OWNCOA AND
BILP350C.OWNDVC = SAWP700A.OWNDVA AND
BILP351C.TRNTPC *= SAWP652E.TRNTYE AND
BILP350C.INVNOC = '7'
ORDER BY
BILP350C.CHGJLC ASC,
BILP350C.BILLC ASC,
BILP350C.BILAUC ASC

How can I include this left outer join and avoid that error message? Linking still seems to be an issue for me! This query works just fine in an Oracle version. How can that be?

Thank you...

 

[bjrollet]

Change all of the '*=' type joins to 'LEFT OUTER JOIN' and you should be ok.

 

[dbielenda]

I thought these were left outer joins? This is the query that Crystal created. I don't think I can manipulate it, can I?

Thanks...

 

[bjrollet]

Its the syntax that is giving you the problem. Your database can't interprete '*=', so change it to 'LEFT OUTER JOIN'. You can chage the syntax. Just Show SQL, and make the changes.

 

[dbielenda]

So, go from this:
BILP350C.BILLC *= BILP352F.BILLF AND
BILP350C.BILAUC *= BILP352F.BILAUF AND
BILP350C.BILLC *= BILP351C.BILLC AND
BILP350C.BILAUC *= BILP351C.BILAUC AND
BILP350C.RTUNTC *= BILP305E.RTUNTE AND
BILP350C.ITEMC *= INVP100A.ITEMA AND
BILP350C.OWNERC = SAWP700A.OWNERA AND
BILP350C.OWNCOC = SAWP700A.OWNCOA AND
BILP350C.OWNDVC = SAWP700A.OWNDVA AND
BILP351C.TRNTPC *= SAWP652E.TRNTYE AND
BILP350C.INVNOC = '7'

to this:
BILP350C.BILLC left outer join BILP352F.BILLF AND
BILP350C.BILAUC left outer join BILP352F.BILAUF AND
BILP350C.BILLC left outer join BILP351C.BILLC AND
BILP350C.BILAUC left outer join BILP351C.BILAUC AND
BILP350C.RTUNTC left outer join BILP305E.RTUNTE AND
BILP350C.ITEMC left outer join INVP100A.ITEMA AND
BILP350C.OWNERC = SAWP700A.OWNERA AND
BILP350C.OWNCOC = SAWP700A.OWNCOA AND
BILP350C.OWNDVC = SAWP700A.OWNDVA and
BILP351C.TRNTPC left outer join SAWP652E.TRNTYE and
BILP350C.INVNOC = '7'

Correct?