Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    27

    Unanswered: Outer Join - need correct code

    Good Morning,

    I need to move this part T1."EepEEID"=T3."EecEEID" from Where to From
    because this need to be a Left Outer join. Not sure how to do it
    properly..



    from "ULTIPRO_GPF"."dbo"."EmpPers" T1, (("ULTIPRO_GPF"."dbo"."EmpComp" T3
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on
    T3."EecLocation"=T2."LocCode") LEFT OUTER JOIN
    "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and
    1=T4."OrgLvl")

    where T1."EepEEID"=T3."EecEEID" and T1."EepDateOfBirth"<'1954-01-01
    00:00:00.000' and T3."EecUDField05"!='BOFA' and T3."EecUDField05"!='HEAD'
    and T3."EecEmplStatus"!='T' and T3."EecEEType" in ('REG', 'RPT', 'COM')
    and T3."EecDateOfOriginalHire"<'1998-01-01 00:00:00.000'
    order by 2 asc, 1 asc

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    from ULTIPRO_GPF.dbo.EmpPers T1
    left outer join ULTIPRO_GPF.dbo.EmpComp T3
    on T1.EepEEID=T3.EecEEID
    and T3.EecUDField05!='BOFA'
    and T3.EecUDField05!='HEAD'
    and T3.EecEmplStatus!='T'
    and T3.EecEEType in ('REG', 'RPT', 'COM')
    and T3.EecDateOfOriginalHire < '1998-01-01 00:00:00.000'
    LEFT OUTER JOIN ULTIPRO_GPF.dbo.Location T2 on T3.EecLocation=T2.LocCode
    LEFT OUTER JOIN ULTIPRO_GPF.dbo.OrgLevel T4 on T3.EecOrgLvl1=T4.OrgCode and 1=T4.OrgLvl)
    where T1.EepDateOfBirth < '1954-01-01 00:00:00.000'

  3. #3
    Join Date
    Dec 2003
    Posts
    27
    b, thanks - for some reason - this doesn't work ...
    from "ULTIPRO_GPF"."dbo"."EmpPers" T1
    left outer join "ULTIPRO_GPF"."dbo"."EmpComp" T3
    T1."EepEEID"=T3."EecEEID"
    and T3.EecUDField05!='BOFA'
    and T3.EecUDField05!='HEAD'
    and T3.EecEmplStatus!='T'
    and T3.EecEEType in ('REG', 'RPT', 'COM')
    and T3.EecDateOfOriginalHire < '1998-01-01 00:00:00.000'
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on T3."EecLocation"=T2."LocCode"
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and 1=T4."OrgLvl")
    where T1.EepDateOfBirth < '1954-01-01 00:00:00.000'


    ----

    I came up with (using their logic) doesn't give any errors, but doesn't produce the results wanted

    from ((("ULTIPRO_GPF"."dbo"."EmpPers" T1 LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."EmpComp" T3 on T1."EepEEID"=T3."EecEEID")
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on T3."EecLocation"=T2."LocCode")
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and 1=T4."OrgLvl")


    where T1."EepDateOfBirth"<'1954-01-01 00:00:00.000' and T3."EecUDField05"!='BOFA' and T3."EecUDField05"!='HEAD' and T3."EecEmplStatus"!='T' and T3."EecEEType" in ('REG', 'RPT', 'COM') and T3."EecDateOfOriginalHire"<'1998-01-01 00:00:00.000' order by 2 asc, 1 asc

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to specify what errors or erroneous results are being returned.

    "this doesn't work" doesn't give me much information.

    And why do you keep putting double quotes around your fields?

    blindman

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Yup ... using [] around your table,db,user names will provide more readability
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Dec 2003
    Posts
    27
    sure, it says

    Error number -239:

    DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
    DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
    Line 1: Incorrect syntax near 'T1'.
    DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
    General SQL Server error: Check messages from the SQL Server.
    -----------------


    the whole code is below. Line 1 they mean the very first line ? if yes, no chaes are being made to it at any point, and no errors are given when its run with other FROM. This program is supposed to use regular T-SQL, so...

    --

    select T1."EepNameFirst" c1, T1."EepNameLast" c2, T2."LocDesc" c3, T3."EecLocation" c4, T4."OrgDesc" c5, T3."EecOrgLvl1" c6, T1."EepAddressLine1" c7, T1."EepAddressLine2" c8, rtrim(convert(varchar(255),T1."EepAddressCity")) + ', ' + rtrim(convert(varchar(255),T1."EepAddressState")) + ' ' + rtrim(convert(varchar(255),T1."EepAddressZipCode") ) c9, T3."EecDateOfOriginalHire" c10, T3."EecDateOfLastHire" c11, case when not T1."EepSSN" is null then substring(T1."EepSSN",1,3) + '-' + substring(T1."EepSSN",4,2) + '-' + substring(T1."EepSSN",6,4) else NULL end c12, T1."EepDateOfBirth" c13, T3."EecEmplStatus" c14

    from "ULTIPRO_GPF"."dbo"."EmpPers" T1
    left outer join "ULTIPRO_GPF"."dbo"."EmpComp" T3
    T1."EepEEID"=T3."EecEEID"
    and T3.EecUDField05!='BOFA'
    and T3.EecUDField05!='HEAD'
    and T3.EecEmplStatus!='T'
    and T3.EecEEType in ('REG', 'RPT', 'COM')
    and T3.EecDateOfOriginalHire < '1998-01-01 00:00:00.000'
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on T3."EecLocation"=T2."LocCode"
    LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and 1=T4."OrgLvl")
    where T1.EepDateOfBirth < '1954-01-01 00:00:00.000'

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Is this SQL Server ..never seen that error before
    "DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'."
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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