Professional Web Applications Themes

sqlstate 21000 error 512 - Microsoft SQL / MS SQL Server

the subquery you are using is returning more that one value which is not allowed when you are writing a query which uses comparison operator like =, !=, <, <= , >, >= etc. The error message you've received is more specific to error description. Make sure the subquery you are using is returning not more than one value. If you can not avoide multiple values returned by the subquery then you can use in/exists operator (remember this may not be applicable in all the scenarios) Ex: select * from customers where customerid = (select customerid from orders where orderid ...

  1. #1

    Default Re: sqlstate 21000 error 512

    the subquery you are using is returning more that one value which is not
    allowed when you are writing a query
    which uses comparison operator like =, !=, <, <= , >, >= etc. The error
    message you've received is more specific
    to error description. Make sure the subquery you are using is returning not
    more than one value.

    If you can not avoide multiple values returned by the subquery then you can
    use in/exists operator
    (remember this may not be applicable in all the scenarios)
    Ex:
    select * from customers where customerid =
    (select customerid from orders where orderid in(10248,10249))
    --Above query will give you error.
    select * from customers where customerid in
    (select customerid from orders where orderid in(10248,10249))
    --you can replace = with in clause

    --
    -Vishal

    "David Truhler" <com> wrote in message
    news:075801c34a2a$ab5c2350$gbl... 


    Vishal Guest

  2. #2

    Default Re: sqlstate 21000 error 512

    here is the script if you can determine anything I would
    appreciate it

    DECLARE INVOICE_CURSOR CURSOR FOR
    SELECT [No.],
    [Reason],
    [Buy-from Vendor No.],
    [Dealer No.],
    CONVERT(money,[Product Amount]),
    CONVERT(money,[Freight Amount]),
    CONVERT(money,[Other Amount]),
    [Other Description],
    [Vendor Invoice No.],
    [Account No.],
    [Pro No.],
    [Posting Date],
    [Due Date]
    FROM excell.dbo.[Excell Marketing &
    Procurement$Purch. Inv. Header] ih
    WHERE [Posting Date] > GETDATE() - 30
    AND ih.Reason <> ''

    OPEN INVOICE_CURSOR

    DECLARE POID int,
    DealerID int,
    VendorID int,
    DealerCode varchar(20),
    VendorCode varchar(20),
    NaviNo varchar(20),
    PONumber varchar(20),
    ProductAmount money,
    FreightAmount money,
    OtherAmount money,
    OtherDescription varchar(40),
    InvoiceNo varchar(20),
    AccountNo varchar(20),
    ProNo varchar(20),
    InvoiceDate datetime,
    DueDate datetime,
    Count int

    FETCH NEXT FROM INVOICE_CURSOR
    INTO
    NaviNo,PONumber,VendorCode,DealerCode,Product Amount,
    FreightAmount,OtherAmount,OtherDescription,Inv o
    iceNo,AccountNo,
    ProNo,InvoiceDate,DueDate

    SELECT Count = 0

    WHILE FETCH_STATUS = 0
    BEGIN
    -- get the associated po if it's not already
    invoiced
    SELECT DealerID = (SELECT CompanyID FROM
    dbo.EXM_COMPANY_ONLINE_ACCESS WHERE CompanyLogon =
    DealerCode)
    SELECT VendorID = (SELECT CompanyID FROM
    dbo.EXM_COMPANY_ONLINE_ACCESS WHERE CompanyLogon =
    VendorCode)
    SELECT POID = (SELECT PurchaseOrderID
    FROM dbo.EXM_VW_PO_STATUS
    WHERE PONumber = PONumber
    AND DealerCompanyID =
    DealerID
    AND MfctrCompanyID =
    VendorID
    AND StatusID NOT IN
    (13,19,21))
    IF NOT POID IS NULL
    BEGIN
    -- get the product amount to see if there
    was an adjustment
    DECLARE POProductAmount money,
    OtherAccountID int

    SELECT POProductAmount = (SELECT SUM
    (Quantity * Price)
    FROM
    EXM_PURCHASE_ORDER_ITEMS
    WHERE
    PurchaseOrderID = POID)
    IF LEN(OtherDescription) > 0
    SELECT OtherAccountID = (SELECT
    OtherReasonID FROM EXM_LOOKUP_OTHER_REASONS WHERE
    OtherReason = OtherDescription)
    ELSE
    SELECT OtherAccountID = 0

    -- first update the detail
    UPDATE EXM_PURCHASE_ORDER_DETAIL
    SET ProductAmountAdjustment =
    (ProductAmount - POProductAmount),
    FreightCost = FreightAmount,
    OtherCost = OtherAmount,
    OtherReasonID = OtherAccountID,
    VendorInvoiceNo = InvoiceNo,
    VendorAcctNo = AccountNo,
    FreightTrackingNo = ProNo,
    InvoiceDate = InvoiceDate,
    InvoiceDueDate = DueDate
    WHERE PurchaseOrderID = POID

    -- now update the status
    INSERT INTO EXM_PURCHASE_ORDER_STATUS

    (PurchaseOrderID,StatusID,StatusNote,AuditDate,Aud i
    tEmployeeID,AuditReasonID)
    VALUES
    (POID,21,'Invoiced manually',GETDATE
    (),0,5)

    SELECT Count = Count + 1
    END

    FETCH NEXT FROM INVOICE_CURSOR
    INTO
    NaviNo,PONumber,VendorCode,DealerCode,Product Amount,
    FreightAmount,OtherAmount,OtherDescription,Inv o
    iceNo,AccountNo,
    ProNo,InvoiceDate,DueDate
    END

    PRINT 'Update records count = ' + CONVERT(varchar
    (40),Count)
    David Guest

  3. #3

    Default Re: sqlstate 21000 error 512

    There might be one of following queries which may be troubleing you. Make
    sure it returns one row only. you will have to run each query seperately by
    passing actual values to it. and check how many rows does it retrieve. you
    may also use aggregate function min/max etc. to return only a single row but
    that depends on the logic that is required to retrieve the row.
    Obviously IN/EXISTS can not work here. hence you've to rewrite your queries
    so that it will return only one row.

    SELECT DealerID = (SELECT CompanyID FROM
    dbo.EXM_COMPANY_ONLINE_ACCESS WHERE CompanyLogon =
    DealerCode)

    SELECT VendorID = (SELECT CompanyID FROM
    dbo.EXM_COMPANY_ONLINE_ACCESS WHERE CompanyLogon =
    VendorCode)

    SELECT POID = (SELECT PurchaseOrderID
    FROM dbo.EXM_VW_PO_STATUS
    WHERE PONumber = PONumber
    AND DealerCompanyID =
    DealerID
    AND MfctrCompanyID =
    VendorID
    AND StatusID NOT IN
    (13,19,21))

    SELECT OtherAccountID = (SELECT
    OtherReasonID FROM EXM_LOOKUP_OTHER_REASONS WHERE
    OtherReason = OtherDescription)

    --
    -Vishal

    "David Truhler" <com> wrote in message
    news:02a501c34a2f$012312e0$gbl... 



    Vishal Guest

Similar Threads

  1. How do I define custom SQLState messages
    By Derek Clarkson in forum IBM DB2
    Replies: 2
    Last Post: August 6th, 01:49 PM
  2. #25206 [Fbk->Csd]: ifx_connect(): E [SQLSTATE=IX 000 SQLCODE=-461
    By jkshin at naver dot com in forum PHP Development
    Replies: 0
    Last Post: August 22nd, 11:29 PM
  3. Replies: 1
    Last Post: August 11th, 08:35 PM
  4. text extender error (sqlstate 38X13)
    By Roberto in forum IBM DB2
    Replies: 0
    Last Post: August 4th, 12:56 PM
  5. Replies: 0
    Last Post: July 27th, 05:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139