Subquery Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Subquery Error

Author

Topic

cwilli35
Starting Member

17 Posts

Posted - 2009-05-14 : 13:10:03

Hello All...When running the below isted SQL, I receive the following error upon execution:

"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."

How can I fix this? Thanks.

INSERT INTO AltirisHDS_TEST.dbo.workitem
(source, external_reference, priority_lookup_id, status_lookup_id
, category_lookup_id, type_lookup_id, category_tree_value
, assigned_to_worker_id, created_by_worker_id, modified_by_worker_id
, created_by_worker_contact_id, modified_by_worker_contact_id
, created_on, minutes_spent, [action], title, managed_object_id, contact_id, tag_collection_id
, is_scheduled, bulletin_status, comment, auxdata, total_minutes_spent
, owned_by_worker_id, rating, comment_visible_to_guest
, rules_fired, updatelock, urgency_lookup_id, impact_lookup_id
, close_code_lookup_id, modified_by_rule_guid, modified_by_rule_history_id
, modified_by_workitem_number, modified_by_workitem_version
, organization_lookup_id)

SELECT
(Select distinct C4 From ARRemedy_TEST.dbo.T10 Where Exists (select distinct ARRemedy_TEST.dbo.T10.C1, ARRemedy_TEST.dbo.T33.C538000011, ARRemedy_TEST.dbo.T10.C4 AS Assigned_to, ARRemedy_TEST.dbo.T10.C538000109
FROM ARRemedy_TEST.dbo.T10, ARRemedy_TEST.dbo.T33
WHERE ARRemedy_TEST.dbo.T10.C538000030 =
ARRemedy_TEST.dbo.T33.C538000030 AND ARRemedy_TEST.dbo.T10.C1 = ARRemedy_TEST.dbo.T33.C538000245)),
T10.C1,
case T10.C538000103
When 'HIGH' then 200
When 'MEDIUM' then 300
When 'LOW' then 400
else 200
end,
case T10.C538000109
When 'Requested' then 200
When 'Resolved' then 400
When 'Initiated' then 525
When '[In] Progress' then 538
When 'Pending CAB Approval' then 550
When 'Release Management' then 575
When 'Pending EDS' then 588
When 'Pending MCNOSC' then 594
When 'Closed' then 600
else 200
end,
1, 10, 'Application Maintenance\Software Maintenance', 5,
5, 5, 5, 5, dateadd(second, T10.C3,'1970-01-01'), 0, 'In Progress', 'REMEDY TO ALTIRIS DATA CONVERSION', 5, 5, 5, 0,'i', T10.C538000144, T10.C4, 0, 5, 0, 1, '620,638,644,352,319', 'd', 200, 200, 100, '00000000-0000-0000-0000-000000000000', 0, 0, 0,

case T10.C538000114
When '[Not] Specified' then 50
When 'Headquarters' then 100
When 'Logistics Operation Center' then 200
When 'Logistics Capabilities Center' then 300
When 'Supply Management Center' then 400
When 'Maintenance Management Center' then 500
When 'Program Support Center' then 600
When 'Distribution Management Center' then 700
When 'Manpower' then 800
When 'Workforce Development Office' then 900
When 'Command Inspection EEO Office' then 1000
When 'Office [of] Counsel' then 1100
When 'Strategic Communications and Change Management' then 1200
When 'Programs and Resources Office' then 1300
When 'Contracts Department' then 1400
When 'Command Control Computers and Communications' then 1500
When 'Installations Environment & Safety Office' then 1600
When 'Logistics Modernization' then 1700
When 'Small Business Office' then 1800
When 'Band' then 1900
else 50
end

FROM ARRemedy_TEST.dbo.T10 T10
WHERE T10.C538000109 != 'Closed'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 14:18:49

the error message suggests your subquery is returning more than one value. so i think what you need is to join with subquery on common column

cwilli35
Starting Member

17 Posts

Posted - 2009-05-14 : 15:23:28

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources