0 Replies Latest reply: Dec 19, 2012 6:42 PM by user12021881 RSS

    Question Regarding SQL logic error!

    user12021881
      Hello everyone,

      I get the following error when executing an Informatica mapping with teh following SQL in the Source Qualifier.
      error - OR- 00936: missing expression.

      Can anyone help please!

      SELECT
      PS_CUSTOMER.SETID
      , PS_CUSTOMER.CUST_ID
      , PS_CUSTOMER.CUST_STATUS
      , PS_CUSTOMER.ADD_DT
      , PS_CUSTOMER.NAME1
      , PS_CUSTOMER.TAXPAYER_ID
      , PS_CUSTOMER.WEB_URL
      , PS_CUSTOMER.LAST_MAINT_OPRID
      , PS_CUSTOMER.DATE_LAST_MAINT
      , PS_CUST_SIC_CODES.SIC_CD_QUAL
      , PS_CUST_SIC_CODES.SIC_CODE
      , PS_CUST_CGRP_LNK.CUST_GRP_TYPE
      , PS_CUST_CGRP_LNK.CUSTOMER_GROUP
      , PS_CUST_CGRP_LNK.LASTUPDDTTM
      , PS_CUST_ID_NBRS.STD_ID_NUM
      , PS_CUSTOMER.SUBCUST_QUAL1
      FROM PS_CUSTOMER
      , (
      select SIC1.SETID, SIC1.CUST_ID, MAX(SIC1.SIC_CODE) SIC_CODE, SIC1.SIC_CD_QUAL
      from PS_CUST_SIC_CODES SIC1
      where SIC1.SIC_CD_QUAL =
      ( select MAX(SIC_CD_QUAL)
      from PS_CUST_SIC_CODES SIC2
      where SIC2.CUST_ID = SIC1.CUST_ID
      and SIC2.SETID = SIC1.SETID
      )
      group by SETID, CUST_ID, SIC1.SIC_CD_QUAL
      ) PS_CUST_SIC_CODES
      , (
      select A.CUST_ID, A.SETID, A.CUST_GRP_TYPE
      , MAX(A.CUSTOMER_GROUP) CUSTOMER_GROUP, A.LASTUPDDTTM
      from PS_CUST_CGRP_LNK A
      where A.CUST_GRP_TYPE =
      (
      SELECT MAX(CUST_GRP_TYPE)
      FROM PS_CUST_CGRP_LNK B
      WHERE A.CUST_ID = B.CUST_ID
      AND A.SETID = B.SETID
      AND A.LASTUPDDTTM = B.LASTUPDDTTM
      AND B.LASTUPDDTTM =
      (
      SELECT MAX(LASTUPDDTTM)
      FROM PS_CUST_CGRP_LNK C
      WHERE C.CUST_ID = B.CUST_ID
      AND C.SETID = B.SETID
      )
      )
      GROUP BY CUST_ID, SETID, CUST_GRP_TYPE, LASTUPDDTTM
      ) PS_CUST_CGRP_LNK
      , (
      select SETID, CUST_ID, STD_ID_NUM
      from PS_CUST_ID_NBRS
      where STD_ID_NUM_QUAL = 'DNS'
      ) PS_CUST_ID_NBRS
      WHERE
      { PS_CUSTOMER
      LEFT OUTER JOIN
      PS_CUST_SIC_CODES ON
      PS_CUSTOMER.SETID = PS_CUST_SIC_CODES.SETID
      AND PS_CUSTOMER.CUST_ID = PS_CUST_SIC_CODES.CUST_ID
      LEFT OUTER JOIN
      PS_CUST_CGRP_LNK ON
      PS_CUSTOMER.SETID = PS_CUST_CGRP_LNK.SETID
      AND PS_CUSTOMER.CUST_ID = PS_CUST_CGRP_LNK.CUST_ID
      LEFT OUTER JOIN
      PS_CUST_ID_NBRS ON
      PS_CUSTOMER.SETID = PS_CUST_ID_NBRS.SETID
      AND PS_CUSTOMER.CUST_ID = PS_CUST_ID_NBRS.CUST_ID
      }
      ORDER BY PS_CUSTOMER.CUST_ID , PS_CUSTOMER.SETID

      Thanks,
      Ajay.