CODEWITHSUNDEEP
sql-server
Asif Ganaie
Asif Ganaie

Reputation: 11

while selecting the data from tables im getting the error

SQL :

select E.ID
    , cp = (
        select ISNULL(C.CNAME, E.CP) CP
        from TBL_EXPEDITE_MASTER E
        left join [NEPTUNE122].BB_PROVISION.DBO.BI_CUSTOMERS C on C.spid = E.CP
        )
    , E.ECO_ORDER
    , E.DN
    , E.CBUK
    , E.XMPF_ID
    , E.CP
    , E.RECEIVED
    , E.CP_OWNER
    , E.CP_CONTACT_NUMBER
    , CP_EMAIL
    , E.CPREF
    , E.SITE_CONTACT_NAME
    , E.SITE_CONTACT_NUMBER
    , E.EXISTING_CRD
    , E.NEW_CRD
    , E.EXPEDITE_CRD
    , E.status
    , E.LINE
    , E.CHARGEABLE
    , E.RESPONSE
    , LAST_UPDATED
    , ORDER_TYPE
    , INITIALRESP
    , NOTES
    , BTW_EXPEDITE_REF --1.2
    , ProductType --1.2
    , InstallationAddress --1.2
    , SITE_CONTACT_OTHER --1.2
    , FastTrackReason --1.2
    , FastTrackFOC --1.2
    , FOC_Reason --1.2
    , CP_EMAIL_OTHER --1.2
    , Received_Via --1.2
    , E2EData --1.2
    , E.organisationName
    , E.postCode
    , E.thoroughfareDescriptor
    , E.county
    , E.country
from TBL_EXPEDITE_MASTER E with (nolock)

Error Message

enter image description here

1:

Upvotes: 0

Views: 27

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

You are getting this error because in the First Select the SubQuery you are using has returned more than 1 Record for the same row.

You May try using top 1 to avoid this error or any aggregate functions or Join the subquery as a separate CTE will also work fine.

For a Quick Fix, you can change the query like this

SELECT cp =
(
    SELECT TOP 1 ISNULL(C.CNAME, E.CP) CP
    FROM TBL_EXPEDITE_MASTER E
         LEFT JOIN [NEPTUNE122].BB_PROVISION.DBO.BI_CUSTOMERS C ON C.spid = E.CP 
ORDER BY C.CNAME, E.CP
);

but looking at your query, I think this will also work

SELECT 
    E.ID,
    cp = ISNULL(C.CNAME, E.CP),
    E.ECO_ORDER,
    E.DN,
    E.CBUK,
    E.XMPF_ID,
    E.CP,
    E.RECEIVED,
    E.CP_OWNER,
    E.CP_CONTACT_NUMBER,
    CP_EMAIL,
    E.CPREF,
    E.SITE_CONTACT_NAME,
    E.SITE_CONTACT_NUMBER,
    E.EXISTING_CRD,
    E.NEW_CRD,
    E.EXPEDITE_CRD,
    E.STATUS,
    E.LINE,
    E.CHARGEABLE,
    E.RESPONSE,
    LAST_UPDATED,
    ORDER_TYPE,
    INITIALRESP,
    NOTES,
    BTW_EXPEDITE_REF,
    ProductType,
    InstallationAddress,
    SITE_CONTACT_OTHER,
    FastTrackReason,
    FastTrackFOC,
    FOC_Reason,
    CP_EMAIL_OTHER,
    Received_Via,
    E2EData,
    E.organisationName,
    E.postCode,
    E.thoroughfareDescriptor,
    E.county,
    E.country
    FROM TBL_EXPEDITE_MASTER E WITH (NOLOCK)
       LEFT JOIN [NEPTUNE122].BB_PROVISION.DBO.BI_CUSTOMERS C 
          ON C.spid = E.CP

Upvotes: 1

Related Questions