Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: syntax error 102....

    Hi all,

    If some could explain why the below is giving me a syntax error I would be most grateful.

    Code:
    ....
    inner join (
    Select count(*) times, 
    compS.PK_X
    from DB2ADMIN.COMPSYS compS 
    inner join DB2ADMIN.USERDATA userD on userD.ObjRef_x = compS.GUID_X
    group by compS.PK_X
    ) userDataInstances on cs.PK_X = userDataInstances.PK_X
    ....
    SQL Analyser is telling me that:

    Server: Msg 102, Level 15, State 1, Line 119
    Incorrect syntax near 'userDataInstances'.

    And apart from throwing the server in a big lake, I am running out of ideas....

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It could be a missed close parenthesis above this query. Hard to say from this fragment.

  3. #3
    Join Date
    May 2009
    Posts
    3
    ahh but the rest of the query was working fine until I added this in.

    however I have the rest listed below:

    Code:
    select
    cs.DISPLAYNAME_X,
    cs.JDOCLASSX,
    --This sets the system created by flag.
    ( CASE 
    WHEN cs.CREATEDBY_X LIKE 'system' THEN '1'
    ELSE '0'
    END ) AS createdBySystemFlag,
    cs2v.FDQN,
    cs2v.IP_ADDRESS,
    cs2v.IP_ADDRESS_STRING,
    cs2v.OS_NAME,
    cs2v.CPU_TYPE,
    cs2v.NUM_CPUS,
    cs2v.CPU_SPEED,
    cs2v.PRIMARY_MAC_ADDRESS,
    cs2v.SERIAL_NUMBER,
    cs2v.MANUFACTURER,
    cs2v.MODEL,
    cs2v.SYSTEM_TYPE,
    --The below gets the account name for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN 'no account' 
    WHEN Length(ud.userAttr4_x) = 0 THEN 'no account' 
    ELSE VARCHAR(ud.userAttr4_x,80)
    END ) as ClarifyAccountName,
    --The below gets the account assigned status for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN '0' 
    WHEN Length(ud.userAttr4_x) = 0 THEN '0' 
    ELSE '1' 
    END ) AS AccountAssigned,
    udm.globalname_x,
    VARCHAR(ud.userAttr1_x,80) As CustomDescription,
    VARCHAR(ud.userAttr2_x,80) As UAMID,
    VARCHAR(ud.userAttr3_x,80) As SourceSystem,
    VARCHAR(ud.userAttr5_x,80) As ClarifyAccountObjid,
    VARCHAR(ud.userAttr6_x,80) As "Controlled CI",
    VARCHAR(ud.userAttr7_x,80) As "Document Hyperlink",
    VARCHAR(ud.userAttr8_x,80) As ESClientName,
    VARCHAR(ud.userAttr9_x,80) As ServerVPStatus,
    VARCHAR(ud.userAttr10_x,15) As SelfServiceCases,
    VARCHAR(ud.userAttr11_x,15) As SelfServiceCRs,
    VARCHAR(ud.userAttr12_x,80) As ThirdPartyProvider,
    udm1.globalname_x AS windowsDomain,
    VARCHAR(ud1.userAttr1_x,80) As Domain,
    VARCHAR(ud1.userAttr2_x,80) As LoginName,
    VARCHAR(ud1.userAttr3_x,80) As UserFullName,
    VARCHAR(ud1.userAttr4_x,80) As LandeskComputerIdn
    from DB2ADMIN.COMPSYS cs
    inner join DB2ADMIN.CM_COMPUTER_SYSTEMS_2_V cs2v on cs.PK_X = cs2v.PK_C1
    --left outer join DB2ADMIN.USERDATA ud on cs2v.PK_C1 = CONCAT(ud.objref_x,'-0')
    left outer join DB2ADMIN.USERDATA ud on cs2v.COMP_GUID = ud.objref_x
    left outer join DB2ADMIN.USERDATAMETA udm on ud.pk__metaref_x = udm.pk_x
    left outer join DB2ADMIN.USERDATA ud1 on cs2v.COMP_GUID = ud1.objref_x
    left outer join DB2ADMIN.USERDATAMETA udm1 on ud1.pk__metaref_x = udm1.pk_x
    where udm.globalname_x = 'com.collation.platform.model.topology.sys.ComputerSystem'
    and udm1.globalname_x = 'com.collation.platform.model.topology.sys.windows.WindowsComputerSystem'
    --This finds all of the non windows systems.
    union
    Select cs.DISPLAYNAME_X,
    cs.JDOCLASSX,
    --This sets the system created by flag.
    ( CASE 
    WHEN cs.CREATEDBY_X LIKE 'system' THEN '1'
    ELSE '0'
    END ) AS createdBySystemFlag,
    cs2v.FDQN,
    cs2v.IP_ADDRESS,
    cs2v.IP_ADDRESS_STRING,
    cs2v.OS_NAME,
    cs2v.CPU_TYPE,
    cs2v.NUM_CPUS,
    cs2v.CPU_SPEED,
    cs2v.PRIMARY_MAC_ADDRESS,
    cs2v.SERIAL_NUMBER,
    cs2v.MANUFACTURER,
    cs2v.MODEL,
    cs2v.SYSTEM_TYPE,
    --The below gets the account name for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN 'no account'
    WHEN Length(ud.userAttr4_x) = 0 THEN 'no account' 
    ELSE VARCHAR(ud.userAttr4_x,80) 
    END ) as ClarifyAccountName,
    --The below gets the account assigned status for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN '0' 
    WHEN Length(ud.userAttr4_x) = 0 THEN '0' 
    ELSE '1' 
    END ) as AccountAssigned,
    udm.globalname_x,
    VARCHAR(ud.userAttr1_x,80) As CustomDescription,
    VARCHAR(ud.userAttr2_x,80) As UAMID,
    VARCHAR(ud.userAttr3_x,80) As SourceSystem,
    VARCHAR(ud.userAttr5_x,80) As ClarifyAccountObjid,
    VARCHAR(ud.userAttr6_x,80) As "Controlled CI",
    VARCHAR(ud.userAttr7_x,80) As "Document Hyperlink",
    VARCHAR(ud.userAttr8_x,80) As ESClientName,
    VARCHAR(ud.userAttr9_x,80) As ServerVPStatus,
    VARCHAR(ud.userAttr10_x,15) As SelfServiceCases,
    VARCHAR(ud.userAttr11_x,15) As SelfServiceCRs,
    VARCHAR(ud.userAttr12_x,80) As ThirdPartyProvider,
    ' ' As WindowsDomain,
    ' ' As Domain,
    ' ' As LoginName,
    ' ' as UserFullName,
    ' ' as LandeskComputerIdn
    from DB2ADMIN.COMPSYS cs
    inner join DB2ADMIN.CM_COMPUTER_SYSTEMS_2_V cs2v on cs.PK_X = cs2v.PK_C1
    left outer join DB2ADMIN.USERDATA ud on cs2v.COMP_GUID = ud.objref_x
    left outer join DB2ADMIN.USERDATAMETA udm on ud.pk__metaref_x = udm.pk_x
    inner join (
    Select count(*) times, 
    compS.PK_X
    from DB2ADMIN.COMPSYS compS 
    inner join DB2ADMIN.USERDATA userD on userD.ObjRef_x = compS.GUID_X
    group by compS.PK_X
    ) userDataInstances on cs.PK_X = userDataInstances.PK_X
    
    where ((cs.JDOCLASSX NOT LIKE 'com.collation.topomgr.jdo.topology.sys.windows.WindowsComputerSystemJdo')
    OR ( userDataInstances.times  <2 ))
    and udm.globalname_x = 'com.collation.platform.model.topology.sys.ComputerSystem'

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Commas are good.
    Code:
    SELECT
    cs.DISPLAYNAME_X,
    cs.JDOCLASSX,
    --This sets the system created by flag.
    ( CASE 
    WHEN cs.CREATEDBY_X LIKE 'system' THEN '1'
    ELSE '0'
    END ) AS createdBySystemFlag,
    cs2v.FDQN,
    cs2v.IP_ADDRESS,
    cs2v.IP_ADDRESS_STRING,
    cs2v.OS_NAME,
    cs2v.CPU_TYPE,
    cs2v.NUM_CPUS,
    cs2v.CPU_SPEED,
    cs2v.PRIMARY_MAC_ADDRESS,
    cs2v.SERIAL_NUMBER,
    cs2v.MANUFACTURER,
    cs2v.MODEL,
    cs2v.SYSTEM_TYPE,
    --The below gets the account name for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN 'no account' 
    WHEN Len(ud.userAttr4_x) = 0 THEN 'no account' 
    ELSE VARCHAR(ud.userAttr4_x,80)
    END ) as ClarifyAccountName,
    --The below gets the account assigned status for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN '0' 
    WHEN Length(ud.userAttr4_x) = 0 THEN '0' 
    ELSE '1' 
    END ) AS AccountAssigned,
    udm.globalname_x,
    VARCHAR(ud.userAttr1_x,80) As CustomDescription,
    VARCHAR(ud.userAttr2_x,80) As UAMID,
    VARCHAR(ud.userAttr3_x,80) As SourceSystem,
    VARCHAR(ud.userAttr5_x,80) As ClarifyAccountObjid,
    VARCHAR(ud.userAttr6_x,80) As "Controlled CI",
    VARCHAR(ud.userAttr7_x,80) As "Document Hyperlink",
    VARCHAR(ud.userAttr8_x,80) As ESClientName,
    VARCHAR(ud.userAttr9_x,80) As ServerVPStatus,
    VARCHAR(ud.userAttr10_x,15) As SelfServiceCases,
    VARCHAR(ud.userAttr11_x,15) As SelfServiceCRs,
    VARCHAR(ud.userAttr12_x,80) As ThirdPartyProvider,
    udm1.globalname_x AS windowsDomain,
    VARCHAR(ud1.userAttr1_x,80) As Domain,
    VARCHAR(ud1.userAttr2_x,80) As LoginName,
    VARCHAR(ud1.userAttr3_x,80) As UserFullName,
    VARCHAR(ud1.userAttr4_x,80) As LandeskComputerIdn
    from DB2ADMIN.COMPSYS cs
    inner join DB2ADMIN.CM_COMPUTER_SYSTEMS_2_V cs2v on cs.PK_X = cs2v.PK_C1
    --left outer join DB2ADMIN.USERDATA ud on cs2v.PK_C1 = CONCAT(ud.objref_x,'-0')
    left outer join DB2ADMIN.USERDATA ud on cs2v.COMP_GUID = ud.objref_x
    left outer join DB2ADMIN.USERDATAMETA udm on ud.pk__metaref_x = udm.pk_x
    left outer join DB2ADMIN.USERDATA ud1 on cs2v.COMP_GUID = ud1.objref_x
    left outer join DB2ADMIN.USERDATAMETA udm1 on ud1.pk__metaref_x = udm1.pk_x
    where udm.globalname_x = 'com.collation.platform.model.topology.sys.ComputerSystem'
    and udm1.globalname_x = 'com.collation.platform.model.topology.sys.windows.WindowsComputerSystem'
    --This finds all of the non windows systems.
    union
    Select cs.DISPLAYNAME_X,
    cs.JDOCLASSX,
    --This sets the system created by flag.
    ( CASE 
    WHEN cs.CREATEDBY_X LIKE 'system' THEN '1'
    ELSE '0'
    END ) AS createdBySystemFlag,
    cs2v.FDQN,
    cs2v.IP_ADDRESS,
    cs2v.IP_ADDRESS_STRING,
    cs2v.OS_NAME,
    cs2v.CPU_TYPE,
    cs2v.NUM_CPUS,
    cs2v.CPU_SPEED,
    cs2v.PRIMARY_MAC_ADDRESS,
    cs2v.SERIAL_NUMBER,
    cs2v.MANUFACTURER,
    cs2v.MODEL,
    cs2v.SYSTEM_TYPE,
    --The below gets the account name for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN 'no account'
    WHEN Len(ud.userAttr4_x) = 0 THEN 'no account' 
    ELSE VARCHAR(ud.userAttr4_x,80) 
    END ) as ClarifyAccountName,
    --The below gets the account assigned status for the server
    ( CASE 
    WHEN ud.userAttr4_x IS NULL THEN '0' 
    WHEN Length(ud.userAttr4_x) = 0 THEN '0' 
    ELSE '1' 
    END ) as AccountAssigned,
    udm.globalname_x,
    VARCHAR(ud.userAttr1_x,80) As CustomDescription,
    VARCHAR(ud.userAttr2_x,80) As UAMID,
    VARCHAR(ud.userAttr3_x,80) As SourceSystem,
    VARCHAR(ud.userAttr5_x,80) As ClarifyAccountObjid,
    VARCHAR(ud.userAttr6_x,80) As "Controlled CI",
    VARCHAR(ud.userAttr7_x,80) As "Document Hyperlink",
    VARCHAR(ud.userAttr8_x,80) As ESClientName,
    VARCHAR(ud.userAttr9_x,80) As ServerVPStatus,
    VARCHAR(ud.userAttr10_x,15) As SelfServiceCases,
    VARCHAR(ud.userAttr11_x,15) As SelfServiceCRs,
    VARCHAR(ud.userAttr12_x,80) As ThirdPartyProvider,
    ' ' As WindowsDomain,
    ' ' As Domain,
    ' ' As LoginName,
    ' ' as UserFullName,
    ' ' as LandeskComputerIdn
    from DB2ADMIN.COMPSYS cs
    inner join DB2ADMIN.CM_COMPUTER_SYSTEMS_2_V cs2v on cs.PK_X = cs2v.PK_C1
    left outer join DB2ADMIN.USERDATA ud on cs2v.COMP_GUID = ud.objref_x
    left outer join DB2ADMIN.USERDATAMETA udm on ud.pk__metaref_x = udm.pk_x
    inner join (
    Select count(*), times, 
    compS.PK_X
    from DB2ADMIN.COMPSYS compS 
    inner join DB2ADMIN.USERDATA userD on userD.ObjRef_x = compS.GUID_X
    group by compS.PK_X
    ) userDataInstances on cs.PK_X = userDataInstances.PK_X
    
    where ((cs.JDOCLASSX NOT LIKE 'com.collation.topomgr.jdo.topology.sys.windows.WindowsComputerSystemJdo')
    OR ( userDataInstances.times  <2 ))
    and udm.globalname_x = 'com.collation.platform.model.topology.sys.ComputerSystem'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Varchar is not a function as far as I know. What is going on in these lines?
    Code:
    VARCHAR(ud.userAttr1_x,80) As CustomDescription,
    VARCHAR(ud.userAttr2_x,80) As UAMID,
    VARCHAR(ud.userAttr3_x,80) As SourceSystem,
    VARCHAR(ud.userAttr5_x,80) As ClarifyAccountObjid,
    VARCHAR(ud.userAttr6_x,80) As "Controlled CI",
    VARCHAR(ud.userAttr7_x,80) As "Document Hyperlink",
    VARCHAR(ud.userAttr8_x,80) As ESClientName,
    VARCHAR(ud.userAttr9_x,80) As ServerVPStatus,
    VARCHAR(ud.userAttr10_x,15) As SelfServiceCases,
    VARCHAR(ud.userAttr11_x,15) As SelfServiceCRs,
    VARCHAR(ud.userAttr12_x,80) As ThirdPartyProvider,

  6. #6
    Join Date
    May 2009
    Posts
    3
    Thanks Patp but is still giving me a syntax error.

    MCrowley, I am using a cast on the columns because the object class for the column has not been set, and the compiler doesn't like it.

    the first variable is the name of the column and the second is the max lenght of the varchar.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The syntax for casting between datatypes in SQL Server can be found here: CAST and CONVERT (Transact-SQL)
    George
    Home | Blog

Posting Permissions

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