To remove the dependency

S

Sudhahar

Hi

This is the query.In this I have to remove the dependency
between TransType ,DOOption tables.(tt.transtype =
doo.transtype).
I have tried with left join.But I was getting 'Join
expression not supported'.

I have tried with group by it is not giving expected
result.

I have used iif(doo.TransType IS NULL,'NULL',tt.transdesc)
in select statement,but I was getting duplicate rows.

But I got the correct result with union.But it feel it is
not optimised.I copied the query below.(SEE UNION)




SELECT DISTINCTROW (ec.ECMCodeBase + '.' + ec.ECMCodeRev)
AS ECMCodeExpr, ppo.EngModel AS EngineModel, 'N/A' AS
Market, ec.CPL AS CPL, (sco.AdvPower + ' hp @ ' +
sco.AdvPowerRPM + ' rpm') AS AdvPowerExpr,
sco.GovernedSpeed AS GovernedSpeed, 'N/A' AS
GovernedPower, (sco.TorquePeak + ' lb-ft @ ' +
sco.TorquePeakRPM + ' rpm') AS TorquePeakExpr, doo_OEMName
AS OEMName, doo_OEMModel AS OEMModel, doo_OEMApplication
AS ApplicationComments, tt.TransTypeDesc AS
TransmissionType, 'N/A' AS AxleType, 'N/A' AS
PerformanceOptions, 'N/A' AS CentinelCPL,
sco.CalibrationVoltage AS Voltage, ppo.PPComments AS
CPLComments, sco.SCComments AS SCComments, doo.DOComments
AS DOComments, 'N/A' AS FROption, ec.SCOption AS SCOption,
ec.DOOption AS DOOption, ccr.result AS
CertificationCodes, 'N/A' AS CertificationYear
FROM ECMCODE AS ec, DOOPTION AS doo, SCOPTION AS sco,
PPOPTION AS ppo, ENGAPPL AS ea, CERTCODE AS cc, ENGFAM AS
ef, TRANSTYPE AS tt, ECMAP AS eap, CERTCODERESULT AS ccr
WHERE doo.TransType = tt.TransType AND
ec.SCOption=sco.SCOption AND ec.DOOption=doo.DOOption AND
ec.CPL=ppo.CPL AND ec.EngFamilyCode=ef.EngFamilycode AND
ec.ECMPartNumber = [ECMPartNumberValue] AND
((ec.ECMCodeBase)=[eap].[ECMCodeBase]) AND ((ec.ECMCodeRev)
=[eap].[ECMCodeRev]) AND ec.SupersessionFlag LIKE
[SupersessionFlagValue] AND cc.APOption=eap.APOption AND
INSTR([SelectedCertCodesValue], cc.CertCode) > 0 AND
((ec.ECMCodeBase+'.'+ec.ECMCodeRev) =(ccr.ECMCodeExpr))


UNION
SELECT DISTINCTROW (ec.ECMCodeBase + '.' + ec.ECMCodeRev)
AS ECMCodeExpr, ppo.EngModel AS EngineModel, 'N/A' AS
Market, ec.CPL AS CPL, (sco.AdvPower + ' hp @ ' +
sco.AdvPowerRPM + ' rpm') AS AdvPowerExpr,
sco.GovernedSpeed AS GovernedSpeed, 'N/A' AS
GovernedPower, (sco.TorquePeak + ' lb-ft @ ' +
sco.TorquePeakRPM + ' rpm') AS TorquePeakExpr, doo_OEMName
AS OEMName, doo_OEMModel AS OEMModel, doo_OEMApplication
AS ApplicationComments, 'NULL' AS TransmissionType, 'N/A'
AS AxleType, 'N/A' AS PerformanceOptions, 'N/A' AS
CentinelCPL, sco.CalibrationVoltage AS Voltage,
ppo.PPComments AS CPLComments, sco.SCComments AS
SCComments, doo.DOComments AS DOComments, 'N/A' AS
FROption, ec.SCOption AS SCOption, ec.DOOption AS
DOOption, ccr.result AS CertificationCodes, 'N/A' AS
CertificationYear
FROM ECMCODE AS ec, DOOPTION AS doo, SCOPTION AS sco,
PPOPTION AS ppo, ENGAPPL AS ea, CERTCODE AS cc, ENGFAM AS
ef, TRANSTYPE AS tt, ECMAP AS eap, CERTCODERESULT AS ccr
WHERE doo.TransType IS NULL AND
ec.SCOption=sco.SCOption AND ec.DOOption=doo.DOOption AND
ec.CPL=ppo.CPL AND ec.EngFamilyCode=ef.EngFamilycode AND
ec.ECMPartNumber = [ECMPartNumberValue] AND
((ec.ECMCodeBase)=[eap].[ECMCodeBase]) AND ((ec.ECMCodeRev)
=[eap].[ECMCodeRev]) AND ec.SupersessionFlag LIKE
[SupersessionFlagValue] AND cc.APOption=eap.APOption AND
INSTR([SelectedCertCodesValue], cc.CertCode) > 0 AND
((ec.ECMCodeBase+'.'+ec.ECMCodeRev) =(ccr.ECMCodeExpr))


Please help me to solve the problem.

Regards
Sudhahar
 
Top