Please HELP ME !!!
When I make not important change in WHERE clause result is completly
different. Please read it down...
I have view created by:
CREATE VIEW v_voyage_nr (VCode,Name,VGroup,VoyNo,VoyType,AntiBal,
DFrom,DUpTo,Owner,MOwner,Firm,Depart,Division,Ver,ActFl,
TC_D,PTC_D,VResultD,Miles,Laden,Balast,NotPaid,DemDes,
Freight,Curren,Dem,Dis,DemCurr,CommodC,Commod,Qtty,CargoGr,
Stem,LayFrom,LayUpTo,DelDate,ReDelDate,LPortC,LPort,LArea,
DPortC,DPort,DArea,Cntrno,ChartC,Chart,Type,Dated,BLdate,
AdrCommis,Broker,Brokerage,Others1,Others2,IFOu,IFOt,IFOc,
IFOp,MDOu,MDOt,MDOc,MDOp,Bunker) AS
SELECT vessel.Code,
vessel.Name,
vessel.vgroup,
voyage.VoyNo,
voyage.VoyType,
voyage.AntiBal,
Date(voyage.DFrom),
Date(voyage.DUpTo),
voyage.Owner,
voyage.MOwner,
voyage.Firm,
voyage.Depart,
voyage.Division,
result.Ver,
result.ActFl,
result.TC_D,
result.PTC_D,
result.VResultD,
result.Miles,
result.Laden,
result.Miles-result.Laden,
result.NotPaid,
Round(result.Dem_D-result.Des_D,2),
voycargo.FRate,
voycargo.FCurren,
voycargo.Dem,
voycargo.Dis,
voycargo.DemCurr,
voyage.Commod,
Cargo.Name,
Round(voyage.Qtty, 0),
cargo.Group,
voycargo.Stem,
voycargo.LayFrom,
voycargo.LayUpTo,
date(""),
date(""),
voyage.PortL,
portL.Descr,
portL.Area,
voyage.PortD,
portD.Descr,
portD.Area,
contract.ContrNo,
contract.Chart,
chart.sname,
contract.Type,
contract.Dated,
v_bldate.BLDate,
contract.AdrCommis,
cntr_broker.Broker,
v_cntr_b.Commis,
voycargo.prem,
voycargo.prem2,
Round(voyage.IFOu-voyage.IFOt,3),
voyage.IFOt,
voyage.IFOc,
voyage.IFOp,
Round(voyage.MDOu-voyage.MDOt,3),
voyage.MDOt,
voyage.MDOc,
voyage.MDOp,
result.Bunker_D
FROM voyage,vessel,voycargo,contract,party chart, outer cargo,
outer port portL,outer port portD,
outer v_cntr_b, outer cntr_broker,
outer v_bldate, outer result
WHERE voyage.VoyType < 3
AND voyage.VCode = vessel.Code
AND voyage.VCode = result.VCode
AND voyage.VoyNo = result.VoyNo
AND voyage.VCode = voycargo.VCode
AND voyage.VoyNo = voycargo.VoyNo
AND voycargo.CargoNo = 1
AND voycargo.ContrNo = contract.ContrNo
AND voycargo.ContrNo = v_cntr_b.ContrNo
AND cntr_broker.ContrNo = contract.ContrNo
AND cntr_broker.SeqNo = 1
AND v_bldate.VCode = voyage.VCode
AND v_bldate.VoyNo = voyage.VoyNo
AND cargo.Code = voyage.Commod
AND portL.code = voyage.portL
AND portD.code = voyage.portD
AND chart.Code = contract.Chart
When I ask :
select vcode,voyno,firm,depart from v_voyage_nr
where firm = 2
and (type=1 or type=3)
and chartC = "FR0058"
and (bldate between "01/01/96" and "12/31/96")
and actfl="*";
result is:
vcode voyno firm depart
0251 23 2 201
0257 24 2 201
0259 33 2 201
0258 34 2 201
0254 24 2 201
0250 16 2 201
0254 27 2 201
0255 32 2 201
0259 21 2 201
it correct result.
When I little midified this querry and add "and depart=201" :
select vcode,voyno,firm,depart from v_voyage_nr
where firm = 2
and depart=201
and (type=1 or type=3)
and chartC = "FR0058"
and (bldate between "01/01/96" and "12/31/96")
and actfl="*"
result is VERY STRANGE some records are multiplayed 3 times without reason:
vcode voyno firm depart
0251 23 2 201
0257 24 2 201
0259 33 2 201
0258 34 2 201
0254 24 2 201
0254 24 2 201
0254 24 2 201
0250 16 2 201
0250 16 2 201
0250 16 2 201
0254 27 2 201
0254 27 2 201
0254 27 2 201
0255 32 2 201
0259 21 2 201
vcode and voyno its unique key. What is going on ????
I rebuild view few times, I recreate all indexes, after oncheck -ci or
oncheck -cI there are no errors.Statistics was updated... What I can do more ???
Appreciate for answers....
Regards
Jarek