Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: issue in DB2 joins

    Hi guys, I am migrating some views from SQL server to Db2. while creating some of the views in Db2 I get below error message.

    SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid.

    Beside this I would like to know the best approach to migrate views from SQL server to Db2 because currently I am doing all the changes manually which is taking lats of time. would appreciate if anyone can suggest some tools or any other approach.

    one of the view I am facing SQL0338N problem is given below.



    CREATE VIEW DAHLIA.VVotPhases AS
    SELECT
    VFilteredCases.*,
    Vot.eventText AS vot,
    Vot.event1 AS vottype,
    -- VotOK.event1 AS votok,
    CASE WHEN (
    -- the only event that should exist is the 'start' event, the rest have to be null (is this correct ?)
    STARTEvent.event1 IS NOT NULL AND
    ANSSEvent.event1 IS NULL AND
    SUPMGOPT1Event.event1 IS NULL AND
    SINAFEvent.event1 IS NULL AND
    ANSMGODKEvent.event1 IS NULL AND
    ANSMAFSLAGEvent.event1 IS NULL AND
    ANSMTILBEvent.event1 IS NULL
    ) THEN 1
    ELSE 0
    END AS PhaseVotStart,
    STARTEvent.decisionDate AS PhaseVotStartDate,
    CASE WHEN (
    ANSSEvent.event1 IS NOT NULL AND
    LMSAREvent.event1 IS NULL) THEN 1 ELSE 0 END AS PhaseVotAss,
    ANSSEvent.actDate AS PhaseVotAssDate,
    AssDeadline.deadlineDays AS PhaseVotAssDeadline,
    CASE WHEN (
    SUPMGOPT1Event.event1 IS NOT NULL AND
    SINAFEvent.event1 IS NULL AND
    ANSMGODKEvent.event1 IS NULL AND
    ANSMAFSLAGEvent.event1 IS NULL AND
    ANSMTILBEvent.event1 IS NULL) THEN 1 ELSE 0 END AS PhaseVotOpf,
    SUPMGOPT1Event.decisionDate AS PhaseVotOpfDate,
    OpfDeadline.deadlineDays AS PhaseVotOpfDeadline,
    CASE WHEN (
    SINAFEvent.event1 IS not NULL AND
    ANSMGODKEvent.event1 IS NULL AND
    ANSMAFSLAGEvent.event1 IS NULL AND
    ANSMTILBEvent.event1 IS NULL) THEN 1 ELSE 0 END AS PhaseVotAfs,
    SINAFEvent.actDate AS PhaseVotAfsDate,
    AfsDeadline.deadlineDays AS PhaseVotAfsDeadline,
    -- Error handling --
    -- If > 2 then error: For mange SUPM+GOPT
    (SELECT COUNT(1) FROM VDriftEvents WHERE VFilteredCases.caseID = VDriftEvents.caseID AND event1 = 'SUPM' AND event2 = 'GOPT') AS SupmGoptCount,
    -- If > 0 then error: SUPM+GOPT før LMSAF
    (SELECT COUNT(1) FROM VDriftEvents WHERE VFilteredCases.caseID = VDriftEvents.caseID AND event1 = 'SUPM' AND event2 = 'GOPT' AND actDate < LMSAREvent.actDate) AS SupmGoptFoerLmsar,
    -- If > 1 the error: For mange SINAF
    (SELECT COUNT(1) FROM VDriftEvents WHERE VFilteredCases.caseID = VDriftEvents.caseID AND event1 = 'SINAF') AS SinafCount,
    DAG60Event.decisionDate AS DAG60Date,
    DAG70Event.decisionDate AS DAG70Date
    FROM VFilteredCases
    INNER JOIN VDriftEvents AS Vot ON VFilteredCases.caseId = Vot.caseID
    AND Vot.event1 IN ('BVOT', 'EKVOT', 'KVOT', 'RVOT')
    AND Vot.actDate =
    (SELECT MAX(VDriftEvents.actDate) FROM VDriftEvents
    WHERE VDriftEvents.caseId = Vot.caseID
    AND VDriftEvents.event1 = Vot.event1
    AND VDriftEvents.eventText <> '')
    LEFT JOIN VDriftEvents AS VotOK ON VFilteredCases.caseId = VotOK.caseID
    AND VotOK.event1 IN ('BVOTOK', 'EKVOTOK', 'KVOTOK', 'RVOTOK')
    AND VotOK.actDate =
    (SELECT MAX(VDriftEvents.actDate) FROM VDriftEvents
    WHERE VDriftEvents.caseId = VotOK.caseID
    AND VDriftEvents.event1 = VotOK.event1
    AND VDriftEvents.actDate >= Vot.actDate
    AND SUBSTRING(Vot.event1, 1, 2) = SUBSTRING(VotOK.event1, 1, 2))
    LEFT JOIN VDriftEvents AS STARTEvent ON VFilteredCases.caseID = STARTEvent.caseID AND STARTEvent.event1 = 'START'
    LEFT JOIN VDriftEvents AS ANSSEvent ON VFilteredCases.caseID = ANSSEvent.caseID AND ANSSEvent.event1 = 'ANSS'
    LEFT JOIN VDriftEvents AS LMSAREvent ON VFilteredCases.caseID = LMSAREvent.caseID AND LMSAREvent.event1 = 'LMSAR'
    LEFT JOIN VDriftEvents AS SINAFEvent ON VFilteredCases.caseID = SINAFEvent.caseID AND SINAFEvent.event1 = 'SINAF'
    LEFT JOIN VDriftEvents AS SUPMGOPT1Event ON VFilteredCases.caseID = SUPMGOPT1Event.caseID AND SUPMGOPT1Event.event1 = 'SUPM' AND SUPMGOPT1Event.event2 = 'GOPT'
    AND (SUPMGOPT1Event.decisionDate < SINAFEvent.actDate OR SINAFEvent.actDate IS NULL)
    LEFT JOIN VDriftEvents AS SUPMGOPT2Event ON VFilteredCases.caseID = SUPMGOPT2Event.caseID AND SUPMGOPT2Event.event1 = 'SUPM' AND SUPMGOPT2Event.event2 = 'GOPT'
    AND (SUPMGOPT2Event.decisionDate > SINAFEvent.actDate AND SINAFEvent.actDate IS NOT NULL)
    LEFT JOIN VDriftEvents AS ANSMGODKEvent ON VFilteredCases.caseID = ANSMGODKEvent.caseID AND ANSMGODKEvent.event1 = 'ANSM' AND ANSMGODKEvent.event2 = 'GODK'
    LEFT JOIN VDriftEvents AS ANSMAFSLAGEvent ON VFilteredCases.caseID = ANSMAFSLAGEvent.caseID AND ANSMAFSLAGEvent.event1 = 'ANSM' AND ANSMAFSLAGEvent.event2 = 'AFSLAG'
    LEFT JOIN VDriftEvents AS ANSMTILBEvent ON VFilteredCases.caseID = ANSMTILBEvent.caseID AND ANSMTILBEvent.event1 = 'ANSM' AND ANSMTILBEvent.event2 = 'TILB'
    LEFT JOIN CaseVotDeadlines AS AssDeadline ON VFilteredCases.caseTypeID = AssDeadline.caseTypeID AND AssDeadline.casePhase = 'ASS'
    LEFT JOIN CaseVotDeadlines AS OpfDeadline ON VFilteredCases.caseTypeID = OpfDeadline.caseTypeID AND OpfDeadline.casePhase = 'OPF'
    LEFT JOIN CaseVotDeadlines AS AfsDeadline ON VFilteredCases.caseTypeID = AfsDeadline.caseTypeID AND AfsDeadline.casePhase = 'AFS'
    LEFT JOIN VDriftEvents AS AnyINDUDEvent ON VFilteredCases.caseID = AnyINDUDEvent.caseID AND AnyINDUDEvent.eventText LIKE '%INDUD%'
    LEFT JOIN VDriftEvents AS EKSTRAEvent ON VFilteredCases.caseID = EKSTRAEvent.caseID AND EKSTRAEvent.event1 = 'EKSTRA'
    LEFT JOIN VDriftEvents AS DAG60Event ON VFilteredCases.caseID = DAG60Event.caseID AND DAG60Event.event1 = 'DAG60'
    LEFT JOIN VDriftEvents AS DAG70Event ON VFilteredCases.caseID = DAG70Event.caseID AND DAG70Event.event1 = 'DAG70'
    WHERE
    (
    projectManager IS NOT NULL
    AND
    (
    caseProcedureCode IN ('NATIONAL', 'NaturNat', 'VitaminNat', 'Homøop')
    OR
    (caseProcedureCode = 'MRP' AND caseTypeCode IN ('UP', 'UG') )
    OR
    (caseProcedureCode = 'MRP' AND isOnVisList=1 AND caseTypeCode IN ('AT', 'TA', 'TB', 'TE', 'TL', 'TM', 'TP', 'TR', 'UA', 'UB', 'UD', 'UE', 'UL', 'UM', 'UR') )
    OR
    (caseProcedureCode = 'MRP' AND AnyINDUDEvent.eventText LIKE '%INDUD%' AND caseTypeCode IN ('TW', 'UW') )
    OR
    (caseProcedureCode = 'AR3031' AND caseTypeCode = 'AR')
    OR
    (caseProcedureCode = 'CP')
    OR
    (caseProcedureCode = 'DCP' AND (caseRoleCode = 'RMS' OR caseRoleCode = 'CMS') AND caseTypeCode IN ('AA', 'AB', 'AD', 'AE', 'AJ', 'AP', 'BA', 'BB', 'BC', 'BE', 'BG', 'BT', 'TE', 'TR', 'UE', 'UR') )
    )
    AND VotOK.swedisCaseEventID IS NULL
    )
    OR
    (
    caseProcedureCode = 'MRP' AND caseRoleCode = 'RMS' AND DAG60Event.decisionDate IS NOT NULL
    )
    ;

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    ...INNER JOIN VDriftEvents AS Vot ON VFilteredCases.caseId = Vot.caseID
    AND Vot.event1 IN ('BVOT', 'EKVOT', 'KVOT', 'RVOT')
    AND Vot.actDate =
    (SELECT MAX(VDriftEvents.actDate) FROM VDriftEvents
    WHERE VDriftEvents.caseId = Vot.caseID
    AND VDriftEvents.event1 = Vot.event1
    AND VDriftEvents.eventText <> '')...
    SQLCODE = -338 AN ON CLAUSE IS INVALID....

    A subquery is not allowed in the ON clause


    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •