Post reply

SubQuery Error

  • April 23, 2007 at 4:02 pm

    #177525

    I am receiving the following error when attempting to update a column with values in another table.  The script is as follows and I'm not sure how to fix the problem.  I am using a SQL view as the source data to update the table.  Any help would be appreciated:

    Error Message:

    Server: Msg 512, Level 16, State 1, Procedure ATEC_PriceStatus_UPSERT, Line 5

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

    SQL Script I'm using:

    UPDATE IV00105 SET LISTPRCE = MSRP

    FROM Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG

    INNER JOIN IV00105 ON

    RTRIM(IV00105.ITEMNMBR) = RTRIM(Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG.ProductNumber)

    WHERE

    Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG.USCATVLS_3 LIKE '1.%'

    AND  Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG.MSRP > = IV00105.LISTPRCE

    AND IV00105.LISTPRCE =0

    When I run the Select statement and the results appear fine and are disclosed as follows:

    0.0000 0 VARI-VZS6953G5JM 200W 2-4GHz TWT Amplifier                                                                             .00000 1.d.        .0000 .0000 VARI-VZS6953G5JM                                0 3 .00000 9807

    0.0000 0 ITHA-3921 10Hz-15kHz Lock-In Amplifier                                                                          .00000 1.          .0000 .0000 ITHA-3921                                       0 3 .00000 7268

    0.0000 0 MINI-TIA-900-10 100MHz-900MHz 10W RF Amplifier, 28dB Gain, BNC                                                        .00000 1.e.        .0000 .0000 MINI-TIA-900-10                                 0 3 .00000 8022

    0.0000 0 MICR-LAB1C-2040-5 2-4GHz 5W Solid State Amplifier, Type N                                                               .00000 1.d.        .0000 .0000 MICR-LAB1C-2040-5                               0 3 .00000 8031

    4500.0000 1995 AMPL-75A220 10kHz-220MHz 75W RF Amplifier                                                                         .00000 1.e.        .0000 .0000 AMPL-75A220                                     0 3 .00000 8183

    6825.0000 2006 AGIL-83017A 500M-26.5GHz Microwave System Amplifier 25dB/18dBm                                                    .00000 1.d.        .0000 .0000 AGIL-83017A                                     0 3 .00000 8866

    0.0000 0 AHSY-PAM-0118 20MHz-18GHz Preamplifier, 38dB gain, Type N                                                           .00000 1.d.        995.0000 .0000 AHSY-PAM-0118                                   0 3 .00000 9565

    0.0000 0 AMPL-10HA 225-410MHz 10W RF Amplifier                                                                           .00000 1.e.        .0000 .0000 AMPL-10HA                                       0 3 .00000 7472

    0.0000 0 AGIL-489A 1-2GHz 1W TWT Amplifier                                                                               .00000 1.d.        .0000 .0000 AGIL-489A                                       0 3 .00000 7295

    0.0000 0 PACI-8255 Bridge Transducer Amplifier Module, Reqs Mainframe                                                    .00000 1.          .0000 .0000 PACI-8255                                       0 3 .00000 7613

    0.0000 0 VARI-VZS6953G5EM 200W 2-4GHz TWT Amplifier                                                                             .00000 1.d.        .0000 .0000 VARI-VZS6953G5EM                                0 3 .00000 9828

    0.0000 0 KALM-737LCS-CE 10kHz-1GHz 30W RF Amplifier, 45dB gain                                                                .00000 1.e.        1072.5000 .0000 KALM-737LCS-CE                                  0 3 .00000 8375

    0.0000 0 AMPL-100W1000M1 80MHz-1GHz 100W RF Amplifier, 50dB,Type N, w/Mtr                                                      .00000 1.e.        .0000 .0000 AMPL-100W1000M1                                 0 3 .00000 8223

    0.0000 0 EGG-5209 0.5Hz-120kHz Lock-In Amplifier, 100nV-3V FullScale                                                    .00000 1.          325.0000 .0000 EGG-5209                                        0 3 .00000 9535

     

     

  • April 23, 2007 at 4:11 pm

    #702042

    your code does not show a subquery.

    typically this happens when a query is constructed this way.

    Select *

    From Mytable

    Where MyCol = (select SomeCol

                          from ATable

                          Where Value = 'This')

    If the subquery returns more than 1 row the select will fail, unless you use the "IN" clause instead of the "="

    Since you say your inserting into a view, Is there perhaps a trigger using a subquery in this manner?'

     

  • April 23, 2007 at 5:18 pm

    #702048

    The SQL View I'm using is as follows.  I'm trying to update data from this SQL view into another table as described in the original posted UPDATE SQL script.  I'm just starting to learn SQL so I'm not sure if and how a SQL trigger maybe impacting this view when trying to use it in an Update statement. 

    CREATE VIEW dbo.vATEC_ProductBase_ProductExtension_IV00101_EG

    AS

    SELECT     TOP 100 PERCENT CASE WHEN Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFCmfg_last_list_price IS NOT NULL

                          THEN CONVERT(varchar, CONVERT(money, Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFCmfg_last_list_price), 2)

                          ELSE CONVERT(varchar, CONVERT(money, 0), 2) END AS MSRP,

                          CASE WHEN Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFImfg_last_list_year IS NOT NULL

                          THEN Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFImfg_last_list_year ELSE 0 END AS MLLY,

                          Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase.ProductNumber, ATEC.dbo.IV00101.ITEMDESC, ATEC.dbo.IV00105.LISTPRCE,

                          ATEC.dbo.IV00101.USCATVLS_3, ATECREPORTS.dbo.ITEMREPORT.monthly, ATECREPORTS.dbo.ITEMREPORT.sale

    FROM         Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase INNER JOIN

                          Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase ON

                          Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.ProductId = Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase.ProductId

                           INNER JOIN

                          ATEC.dbo.IV00101 ON Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase.ProductNumber = ATEC.dbo.IV00101.ITEMNMBR INNER JOIN

                          ATEC.dbo.IV00105 ON ATEC.dbo.IV00101.ITEMNMBR = ATEC.dbo.IV00105.ITEMNMBR INNER JOIN

                          ATECREPORTS.dbo.ITEMREPORT ON ATEC.dbo.IV00101.ITEMNMBR = ATECREPORTS.dbo.ITEMREPORT.ITEMNMBR

    WHERE     (ATEC.dbo.IV00101.ITMCLSCD = 'INVENTORY')

  • April 24, 2007 at 1:57 pm

    #702253

    Well

    the error message indicates the error is bubbling up from

    Server: Msg 512, Level 16, State 1, Procedure ATEC_PriceStatus_UPSERT, Line 5

    Can you post the entire procedure.

     

  • April 25, 2007 at 9:55 am

    #702420

    I have received a similar error message when trying to update a table that has a trigger.  If you have a trigger on that table, you must stop it and restart it when the procedure is finished.

  • April 25, 2007 at 12:29 pm

    #702466

    Thanks! The trigger on the table appears to be the problem. You mention to stop the trigger and then restart it when the procedure is finished.  I'm a novice SQL user and not too familiar with Triggers.  Can I build it into my existing script to do that?  If so, how do I go about doing that.

  • April 25, 2007 at 12:42 pm

    #702473

    I figured out how to Disable and Enable the trigger on the table.  Thanks so much for everyone's help. 

  • April 27, 2007 at 4:47 pm

    #703083

    That's a bit, well, insane... why is the trigger there in the first place if you're going to just disable it?  Either the trigger is bad or your code is bad.  Fix it instead of patching it with disabling a trigger.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 27, 2007 at 5:13 pm

    #703088

    In my case the trigger is there to effect MANUAL data changes - if those changes are not needed and another corrective change is, the trigger must be disabled and enabled after the needed changes.

  • April 27, 2007 at 7:34 pm

    #703099

    Then, the trigger is wrong... it should be able to handle both.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply