December 17, 2015 at 1:28 pm
I am using the following query to get a new date. I am getting the error that the subquery returned more than 1 value. I have racked my brains to figure it out and feel like it should be relatively quick but I am stuck. please HELP
select
l.LDC_ACCT_NO,
c.CYCLE_DT,
(SELECT DateMinus3WorkDays = DTInt
FROM dbo.Calendar
WHERE WD#Next = (SELECT WD#Next-3 FROM dbo.Calendar WHERE DTInt = c.CYCLE_DT)) NEW_CYCLE,
c.CYCLE_GRP_DT,
wg.Contract_Effective_Date,
c.VENDOR_ID
from CYCLE c inner join rmprod.dbo.LDC_ACCOUNT l on l.LDC_VENDOR_ID = c.VENDOR_ID
inner join Reporting.dbo.ops_CYCLE_DATES wg on wg.account_number = l.LDC_ACCT_NO and wg.Cycle_Day = cast(c.CYCLE_NO as varchar)
where c.CYCLE_TY_CD = 'INV'
and l.LDC_VENDOR_ID in ('1429','1446')
and c.CYCLE_GRP_DT in ('20151231','20160131')
and wg.Contract_Effective_Date in ('12/1/2015','1/1/2016')
December 17, 2015 at 1:39 pm
Run your sub query separately and it will become obvious what the problem is.
SELECT WD#Next-3 FROM dbo.Calendar WHERE DTInt = c.CYCLE_DT
What value is that statement supposed to use when more than one value is returned with an equality operator?
I'm not sure what your requirements are but changing '=' to 'IN' might be what you are looking for.
WHERE WD#Next IN (SELECT WD#Next-3 FROM dbo.Calendar WHERE DTInt = c.CYCLE_DT)) NEW_CYCLE
December 17, 2015 at 2:09 pm
its grabbing the c.CYCLE_DT from the CYCLE table. If I add an ACCT_ID in the WHERE clause of the query it will return the result expected.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply