Sybase转Oracle查询结果行数不符，请求修正Oracle SQL语句

Let's work through why your converted Oracle query is returning a different number of rows than the original Sybase query, and how to fix it step by step.

First, Let's Recap Your Queries #

Original Sybase Query #

SELECT OdpdInfo.origLocCd, OdpdInfo.destLocCd, OdpdInfo.prodOffset, 
       Act.locCd, Act.actCd, NRLL.seq, Lane.mandatory24HrDelay, Lane.mandatoryMode, 
       ONRL.effDaysL, NR.mvPercent, NR.routingType, NR.priority, 
       Transit.locCd, Transit.seq, Transit.mandatoryFlag, ProductType.prodType, 
       ProductType.hndlCd, NR.networkRtgId, OdpdInfo.odpdKey 
FROM OdpdInfo, OdpdNetworkRtgLink ONRL, NetworkRtg NR, Activity Act, Transit, 
     NetworkRtgLaneLink NRLL, Lane, ProductType 
WHERE OdpdInfo.odpdKey = ONRL.odpdKey 
  AND ONRL.networkRtgId = NR.networkRtgId 
  AND NR.networkRtgId = NRLL.networkRtgId 
  AND NRLL.laneId = Lane.laneId 
  -- outer join here 
  AND Lane.laneId *= Transit.laneId 
  AND Act.activityId = Lane.destActivityId 
  AND OdpdInfo.prodOffset = ProductType.prodOffset 
  AND Transit.grpKey < 2

Converted Oracle Query (Current Version) #

SELECT Odpd_Info.orig_Loc_Cd, Odpd_Info.dest_Loc_Cd, Odpd_Info.prod_Offset, 
       Act.loc_Cd, Act.act_Cd, NRLL.seq, Lane.mandatory_24_Hr_Delay, Lane.mandatory_Mode, 
       ONRL.eff_Days_L, NR.mv_Percent, NR.routing_Type, NR.priority, 
       Transit.loc_Cd, Transit.seq, Transit.mandatory_Flag, Product_Type.prod_Type, 
       Product_Type.hndl_Cd, NR.network_Rtg_Id, Odpd_Info.odpd_Key 
FROM Odpd_Info, Odpd_Network_Rtg_Link ONRL, Network_Rtg NR, Activity Act, Transit, 
     Network_Rtg_Lane_Link NRLL, Lane, Product_Type 
WHERE Odpd_Info.odpd_Key = ONRL.odpd_Key 
  AND ONRL.network_Rtg_Id = NR.network_Rtg_Id 
  AND NR.network_Rtg_Id = NRLL.network_Rtg_Id 
  AND NRLL.lane_Id = Lane.lane_Id 
  -- outer join here 
  AND Lane.lane_Id (+)= Transit.lane_Id 
  AND Act.activity_Id = Lane.dest_Activity_Id 
  AND Odpd_Info.prod_Offset = Product_Type.prod_Offset 
  AND Transit.grp_Key < 2 
  AND Odpd_Info.common_case_oid = 1 
  AND ONRL.common_case_oid = 1 
  AND NR.common_case_oid = 1 
  AND Act.common_case_oid = 1 
  AND Transit.common_case_oid = 1 
  AND NRLL.common_case_oid = 1 
  AND Lane.common_case_oid = 1 
  AND Product_Type.common_case_oid = 1

Two Key Reasons for the Row Count Mismatch #

1. Outer Join Filter Placement Is Broken #

Sybase's *= left outer join syntax behaves differently than Oracle's old (+) syntax when filtering the outer-joined table.

In your Sybase query, Transit.grpKey < 2 works with the *= join to retain all rows from Lane—even if there's no matching Transit row (NULL values from Transit are allowed here).

But in Oracle's (+) syntax, putting Transit.grpKey < 2 in the WHERE clause turns your left outer join into an inner join. Any row where Transit is NULL will fail this condition (NULL < 2 evaluates to unknown, so those rows get excluded entirely).

2. Extra common_case_oid = 1 Filters #

You added common_case_oid = 1 for every table in your Oracle query. If this filter wasn't present in the original Sybase query, this is definitely reducing your row count—you're filtering out rows that the original query included. Double-check if these filters are actually required for your business logic.

Corrected Oracle Query (Using Modern ANSI Syntax) #

I recommend using ANSI join syntax in Oracle—it's more readable and avoids the pitfalls of the old (+) syntax. Here's the fixed version:

SELECT oi.orig_Loc_Cd, oi.dest_Loc_Cd, oi.prod_Offset, 
       a.loc_Cd, a.act_Cd, nrll.seq, l.mandatory_24_Hr_Delay, l.mandatory_Mode, 
       onrl.eff_Days_L, nr.mv_Percent, nr.routing_Type, nr.priority, 
       t.loc_Cd, t.seq, t.mandatory_Flag, pt.prod_Type, 
       pt.hndl_Cd, nr.network_Rtg_Id, oi.odpd_Key 
FROM Odpd_Info oi
-- Inner joins for tables that must have matching rows
JOIN Odpd_Network_Rtg_Link onrl 
  ON oi.odpd_Key = onrl.odpd_Key
  -- Remove this line if common_case_oid wasn't in the original Sybase query
  AND oi.common_case_oid = 1 
  AND onrl.common_case_oid = 1
JOIN Network_Rtg nr 
  ON onrl.network_Rtg_Id = nr.network_Rtg_Id
  AND nr.common_case_oid = 1
JOIN Network_Rtg_Lane_Link nrll 
  ON nr.network_Rtg_Id = nrll.network_Rtg_Id
  AND nrll.common_case_oid = 1
JOIN Lane l 
  ON nrll.lane_Id = l.lane_Id
  AND l.common_case_oid = 1
-- Left join to retain all Lane rows, even without matching Transit
LEFT JOIN Transit t 
  ON l.lane_Id = t.lane_Id
  -- Move the Transit filter here to preserve outer join behavior
  AND t.grp_Key < 2 
  AND t.common_case_oid = 1
JOIN Activity a 
  ON l.dest_Activity_Id = a.activity_Id
  AND a.common_case_oid = 1
JOIN Product_Type pt 
  ON oi.prod_Offset = pt.prod_Offset
  AND pt.common_case_oid = 1

What Changed? #

• Switched to ANSI JOINs: Makes join types (inner vs left outer) explicit, eliminating ambiguity.
• Moved Transit.grpKey < 2 to the LEFT JOIN condition: Now, even if there's no matching Transit row, the Lane row is still retained (with NULLs for Transit columns), matching the Sybase query's behavior.
• Added table aliases: Makes the query cleaner and easier to maintain.
• Highlighted common_case_oid filters: Reminds you to remove these if they weren't part of the original Sybase logic—this is a common accidental change that drastically reduces row counts.

Final Checks #

1. If you didn't have common_case_oid = 1 in the original Sybase query, delete all those lines first—this is likely the biggest culprit for the row count mismatch.
2. Test the corrected query and compare row counts to the original Sybase query. If they still don't match, check for subtle differences like case sensitivity in column/table names, or differing NULL value handling in other conditions.

内容的提问来源于stack exchange，提问作者shital Dongare