Help with CASE, Subquery returned more than 1 value.

Question

Post reply

Help with CASE, Subquery returned more than 1 value.

jason 93512

Old Hand

Points: 315
More actions
March 24, 2012 at 6:15 pm

#270727

First Post. Trying to write an upload file for work. Just started playing with SQL, it is amazing. Ran into a problem generating a single column. Thanks in advance, sorry for the mess!
Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Just want the column to have one of six values based on a substring search.
,(select
CASE
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99281%') is not null) then '1'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99282%') is not null) then '2'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99283%') is not null) then '3'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99284%') is not null) then '4'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99285%') is not null) then '5'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99291%') is not null) then '6'
ELSE ''
END
from #OutcomeCPT
where account = PATIENT_NUMBER)
AS tq_em
Here's the rest of what I have, everything else works except this column.
CREATE TABLE #OutcomeCPT(
account char(8),
cptcode char(500) )
INSERT INTO #OutcomeCPT(account,cptcode)
SELECT p1.acct,
reverse(stuff(reverse(( SELECT cpt + ':'
FROM (SELECT TOP (100) PERCENT dbo.od_ar_demographics.PATIENT_NUMBER as acct, dbo.OD_IVM1.IVM1CPTFIL as cpt
FROM dbo.OD_IP1 RIGHT OUTER JOIN
dbo.od_ar_demographics ON dbo.OD_IP1.IP0NUMBER = dbo.od_ar_demographics.PATIENT_NUMBER LEFT OUTER JOIN
dbo.od_ar_charges LEFT OUTER JOIN
dbo.OD_IVM1 ON dbo.od_ar_charges.CHG_CHARGE_NUMBER = dbo.OD_IVM1.IVM1NUMBER ON
dbo.od_ar_demographics.PATIENT_NUMBER = dbo.od_ar_charges.CHG_PATNUM
WHERE (NOT (dbo.OD_IVM1.IVM1CPTFIL IS NULL)) AND
(NOT (dbo.OD_IVM1.IVM1CPTFIL IS NULL)) AND (dbo.od_ar_demographics.PATIENT_TYPE = '3') AND
(dbo.OD_IP1.IP1DISC_DATE < CONVERT(DATETIME, '2012-02-01 00:00:00', 102)) AND (dbo.OD_IP1.IP1DISC_DATE > CONVERT(DATETIME,
'2012-01-01 00:00:00', 102)) OR
(NOT (dbo.OD_IVM1.IVM1CPTFIL IS NULL)) AND (dbo.od_ar_demographics.PATIENT_TYPE = '2') AND (dbo.od_ar_demographics.SERVICE_CODE = 'N')
AND (dbo.OD_IP1.IP1DISC_DATE < CONVERT(DATETIME, '2012-02-01 00:00:00', 102)) AND (dbo.OD_IP1.IP1DISC_DATE > CONVERT(DATETIME,
'2012-01-01 00:00:00', 102))
) p2
WHERE p2.acct = p1.acct
FOR XML PATH('') )), 1, 1, '')) AS cpt
FROM (SELECT TOP (100) PERCENT dbo.od_ar_demographics.PATIENT_NUMBER as acct, dbo.OD_IVM1.IVM1CPTFIL as cpt
FROM dbo.OD_IP1 RIGHT OUTER JOIN
dbo.od_ar_demographics ON dbo.OD_IP1.IP0NUMBER = dbo.od_ar_demographics.PATIENT_NUMBER LEFT OUTER JOIN
dbo.od_ar_charges LEFT OUTER JOIN
dbo.OD_IVM1 ON dbo.od_ar_charges.CHG_CHARGE_NUMBER = dbo.OD_IVM1.IVM1NUMBER ON
dbo.od_ar_demographics.PATIENT_NUMBER = dbo.od_ar_charges.CHG_PATNUM
WHERE (NOT (dbo.OD_IVM1.IVM1CPTFIL IS NULL)) AND
(NOT (dbo.OD_IVM1.IVM1CPTFIL IS NULL)) AND (dbo.od_ar_demographics.PATIENT_TYPE = '3') AND
(dbo.OD_IP1.IP1DISC_DATE < CONVERT(DATETIME, '2012-02-01 00:00:00', 102)) AND (dbo.OD_IP1.IP1DISC_DATE > CONVERT(DATETIME,
'2012-01-01 00:00:00', 102)) OR
(NOT (dbo.OD_IVM1.IVM1CPTFIL IS NULL)) AND (dbo.od_ar_demographics.PATIENT_TYPE = '2') AND (dbo.od_ar_demographics.SERVICE_CODE = 'N')
AND (dbo.OD_IP1.IP1DISC_DATE < CONVERT(DATETIME, '2012-02-01 00:00:00', 102)) AND (dbo.OD_IP1.IP1DISC_DATE > CONVERT(DATETIME,
'2012-01-01 00:00:00', 102))
) p1
GROUP BY acct ;
SELECT TOP (100) PERCENT dbo.od_ar_demographics.PATIENT_NUMBER AS patient_os_id, CONVERT(VARCHAR(10), dbo.OD_IP1.IP1DISC_DATE, 101)
AS tq_encounterdt, CONVERT(VARCHAR(10), dbo.od_ar_demographics.PATIENT_DOB, 101) AS tq_dob,
CASE WHEN od_ar_demographics.PAT_INSURANCE_1 = 'M' THEN '1' WHEN od_ar_demographics.PAT_INSURANCE_1 = 'X' THEN '2' WHEN od_ar_demographics.PAT_INSURANCE_1
= 'P' THEN '4' ELSE '3' END AS jc_pmtsrce, dbo.od_ar_demographics.MR_NUMBER AS tq_hospmrn, dbo.OD_IP1.IP1FIRSTNAME AS tq_firstname,
dbo.OD_IP1.IP1LASTNAME AS tq_lastname,
dbo.od_ar_demographics.PAT_INSURANCE_1 + dbo.od_ar_demographics.PAT_INSURANCE_2 + dbo.od_ar_demographics.PAT_INSURANCE_3 AS tq_insurance,
CASE WHEN LEN(OD_IP1.IP1PAT_ZIP) = 9 THEN RIGHT(OD_IP1.IP1PAT_ZIP, 4) ELSE '' END AS jc_zip2, CASE WHEN (OD_IP1.IP1PAT_ZIP)
= 0 THEN '' ELSE LEFT(OD_IP1.IP1PAT_ZIP, 5) END AS jc_zip1,
CASE WHEN od_ar_demographics.PATIENT_SEX = 'M' THEN '1' WHEN od_ar_demographics.PATIENT_SEX = 'F' THEN '2' ELSE '3' END AS tq_gender,
dbo.Outcome_Discharge.Outcome AS tq_discstatus,
dbo.jf_RACE.Outcome_RaceCode AS tq_race,
CASE WHEN od_ar_demographics.PAT_INSURANCE_1 = 'M' THEN '1' ELSE '2' END AS tq_pmtsrce2,
dbo.OD_IP0.IP0ERADMIT_DATE_1,dbo.OD_IP0.IP0ERADMIT_TIME_1,
NULL AS tq_arrdatetime,
dbo.OD_IP0.IP0ERDISC_DATE_1,
dbo.OD_IP0.IP0ERDISC_TIME_1,
dbo.MakeICDdot(dbo.OD_IP5.IP5DIAGNOSIS_1) AS tq_principaldx,
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_2),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_3),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_4),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_5),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_6),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_7),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_8),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_9),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_10),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_11),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_12),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_13),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_14),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_15),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_16),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_17),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_18),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_19),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_20),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_21),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_22),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_23),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_24),'')+':'+
coalesce(dbo.MakeICDdot(IP5DIAGNOSIS_25),'') AS tq_icd9,
(select cptcode from #OutcomeCPT where account=PATIENT_NUMBER) AS tq_cpt
--below assumes that there will not EVER be more than one E/M code on the account. It takes the first found.
,(select
CASE
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99281%') is not null) then '1'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99282%') is not null) then '2'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99283%') is not null) then '3'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99284%') is not null) then '4'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99285%') is not null) then '5'
WHEN ((SELECT cptcode FROM #OutcomeCPT WHERE cptcode LIKE '%99291%') is not null) then '6'
ELSE ''
END
from #OutcomeCPT
where account = PATIENT_NUMBER)
AS tq_em

Viewing 8 posts - 1 through 7 (of 7 total)

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

Try this chunk instead

,(select

CASE

WHEN cptcode LIKE '%99281%' then '1'

WHEN cptcode LIKE '%99282%' then '2'

WHEN cptcode LIKE '%99283%' then '3'

WHEN cptcode LIKE '%99284%' then '4'

WHEN cptcode LIKE '%99285%' then '5'

WHEN cptcode LIKE '%99291%' then '6'

ELSE ''

END

from #OutcomeCPT

where account = PATIENT_NUMBER)

jason 93512 Old Hand Points: 315 More actions · Answer 2

Awesome! It worked! Apparently I overcomplicated it. Sorry, never used SQL until this project. (occasional ms access use) Jack of all trades, master of few.. =)

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

jason 93512 (3/24/2012)
of all trades, master of few.. =)

LOL.. I'm master of NONE :w00t:

jason 93512 Old Hand Points: 315 More actions · Answer 4

Thanks for the response. Funny thing you mention Cobol, as our system is still based on it. (not that i know it more than the semester i took in school 15 years ago)

The top 100 percent is something sql server automatically sticks on the query when designing it graphically.

I appreciate you taking the time to go over some issues you found with the query i have muddled together. I obviously don't know SQL but amazingly got it to work.

I do have an insane amount of whitespace and ':' that i need to clear up. The uploader would rather see null in the multi value column than a bunch of delimiters.

Played with access but never had to write SQL until this project. Sub-queries and cross-tabs and temporary tables...wow! So much more to learn.

I would like to index my temporary table but as it works currently and this only needs to run once a month, not sure if i will get to improving it. Also, there is a query that is called multiple times that would probably benefit a temporary table as well. Any suggestions/recommendations would be appreciated.

I will be writing many more reports as I have built ODBC access to our Hospital Information System. Lots of data integrity and analysis to be done.

Aloha!

Grass Ten Centuries Points: 1149 More actions · Answer 5

Hi everyone:

[Execute SQL Task] Error: Executing the query "/* removes Matters from timekeepers frequently use..." failed with the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am getting the above error and can't able to figure out where is the problem. Here is the chunk of the code:

/* removes Matters from timekeepers frequently used matters page */

DECLARE @Matter_ID varchar(8)

DECLARE @Project_ID int

DECLARE @Project_Desc varchar(150)

DECLARE @User_ID int

DECLARE @Listing_ID int

DECLARE @Billing_ID varchar(4)

/* declare a cursor from the table holding the matters added on the previous day */

DECLARE Matters_To_Add CURSOR FOR SELECT TimeKeeperNumber, MatterNumber FROM int_maslon_matter_recent

OPEN Matters_To_Add

FETCH NEXT FROM Matters_To_Add INTO @Billing_ID, @Matter_ID

WHILE @@FETCH_STATUS = 0

BEGIN

/* Find the Project_ID and the Description from the Matter Number */

SET @Project_ID = (SELECT project_id FROM int_aux_project WHERE project_cd = @Matter_ID)

SET @Project_Desc = (SELECT project_nm FROM int_aux_project WHERE project_cd = @Matter_ID)

/* Find the InterAction User_ID from the Billing ID, going through the int_aux_lst_custom table */

SET @Listing_ID = (SELECT listing_id FROM int_aux_lst_custom WHERE string_value = @Billing_ID AND LST_CUSTOM_DEF_ID = '-10017')

SET @User_ID= (SELECT user_id FROM int_user WHERE listing_id = @Listing_ID)

/* Only remove items from the int_aux_favorites table - this is consistent with InterAction's native behavior */

/* Verify that the project already exists in the user's Favorites menu */

IF EXISTS (SELECT * FROM int_aux_favorites where user_id = @User_ID and entity_id = @Project_ID)

BEGIN

/* Remove from the Favorites table */

DELETE FROM int_aux_favorites WHERE user_id = @User_ID and entity_id = @Project_ID

END

/* Loop to the next record in the source Cursor */

FETCH NEXT FROM Matters_To_Add INTO @Billing_ID, @Matter_ID

END

CLOSE Matters_To_Add

DEALLOCATE Matters_To_Add

Need Help!

Thanks.

Grass Ten Centuries Points: 1149 More actions · Answer 6

Hi: