June 19, 2014 at 1:43 am
Hi can someone please tell me why sql says
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
WHERE
ct.id = (select
max(id)
from
cat_transaction ct
where
case_id = 121
and
cast((id) as varchar(60))
not in
(selectext_reference from Patricia.dbo.BUDGET_LINE bl
intersect
select max(cast(id as varchar(60))) from cat_transaction
)
)
If I run just the query without the ct.id =, it ruturns one value.
Why is SSMS complaining about returning more than one value when I reun the whole script?
June 19, 2014 at 2:32 am
Because you are comparing a single value, ct.id, against the result set of a query.
If they query returns multiple rows, how can it compare it against the single value?
For example:
5 = (1,2,3,4,5)?
Either make sure the query returns only one value, or use IN instead of =.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 19, 2014 at 3:05 am
The code you've posted can't throw that error, a SELECT MAX().. with no group by can't return multiple rows.
Are you sure that subquery is the cause, not an other subquery somewhere else in the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2014 at 3:06 am
Hey sorry man, the problem was where I define the case id like
SET @CaseID = (
SELECT
bl.CASE_ID
FROM
Patricia.dbo.BUDGET_LINE bl
JOIN cat_transaction ct ON bl.EXT_REFERENCE = ct.id
WHERE
ct.CASE_ID = bl.CASE_ID
)
If I hard code the @caseid to say 121, then it works. But how can I get it to work where the case id is like the above?
June 19, 2014 at 3:07 am
crazy_new (6/19/2014)
... But how can I get it to work where the case id is like the above?
Either make sure the query returns only one value, or use IN instead of =.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 19, 2014 at 3:23 am
Hey GilaMonster,
Your right as usual hehe. It was my @case id. It work if I hard code it , but this exercise is pointless if I hardcode the case id, so how would I be able to resolve this? Any suggestions would be awesome
June 19, 2014 at 3:39 am
You need to check what select query is returning. Select query should only return one result set. if its more then one then you will get this issue.
one other possibility is this you might be getting same ID more then once if so then you can use DISTINCT to avoid this issue.
but whatever the case is, you 1st need to make sure the select query is return the appropriate result set for you or not.
Hope it helps
June 19, 2014 at 3:43 am
crazy_new (6/19/2014)
Hey sorry man, the problem was where I define the case id likeSET @CaseID = (
SELECT
bl.CASE_ID
FROM
Patricia.dbo.BUDGET_LINE bl
JOIN cat_transaction ct ON bl.EXT_REFERENCE = ct.id
WHERE
ct.CASE_ID = bl.CASE_ID
)
You need to decide which CaseID to set the variable to when that query returns more than one. Do you want the highest? Lowest? Latest? Oldest? Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2014 at 3:59 am
It can be any case ID, depends what the user is working on. Here is the whole script. I need to increment the @LineNumber per CaseID, and right it into another table(Budget line) if its not there yet.
DECLARE @LineNumber SMALLINT,
@CaseID SMALLINT,
@ChargeingID SMALLINT
SET @CaseID = (121)
-- SELECT
--bl.CASE_ID
-- FROM
--Patricia.dbo.BUDGET_LINE bl
-- JOIN cat_transaction ct ON bl.EXT_REFERENCE = ct.id
-- WHERE
--ct.CASE_ID = bl.CASE_ID
--)
----Get Line numbers for cases
SET @LineNumber = (
SELECT
ISNULL(MAX(B_L_SEQ_NUMBER),0)
FROM
Patricia.dbo.BUDGET_LINE
WHERE
CASE_ID = 121
)
SET @LineNumber = @LineNumber + 1
INSERT INTO Patricia.dbo.BUDGET_LINE
SELECT
@LineNumber,
ctt.WorkCodeID,
ctde.[pagecount],
null,--unit price
cv.name,
null,--offer number
null,--amount (need a price)
null,--invoice number
ct.Case_ID,
@ChargeingID,--need id to pass into variable
null,--b_l_is_external
null,--external_invoice_id
null,--external_invoice_date
null,--bl_org_quantity
null,--bl_org_unit_price
null,--bl_org_amount
null,--bl_org_journal_no_time_reg
null,--bl_advance_number
null,--bl_advance_date
0,--show time comment
null,--discount_prec
null,--discount_amount
null,--bl_unit_price_no_discount
0,--invoice preview
null,--currency_id
null,--exchange_rate
null,--collective_inv_log_id
null,--bl_comment
cv.name,
null,--earliest_invoice_date
null,--pl_org_unit_price
null,--pl_org_unit_price_no_discount
null,--pl_org_amount
null,--plorg_currency_id
'CH',--indicator
null,--purchase_order_number
null,--is_purchase
ct.creation,--recorded_date
ct.id--ext_reference
FROM
cat_transaction ct
JOIN
cat_validation cv ON cv.id = ct.chargeid
JOIN
cat_trxtype ctt ON ctt.trxtype = ct.trxtype
JOIN
cas_trx_doc_ext ctde ONctde.x_id = ct.id
WHERE
ct.id = (select
max(id)
from
cat_transaction ct
where
case_id = 121
and
cast((id) as varchar(60))
not in
(selectext_reference from Patricia.dbo.BUDGET_LINE bl
intersect
select cast(id as varchar(60)) from cat_transaction
)
)
June 19, 2014 at 4:23 am
First of all how can this query will know ? the value will be 121
SELECT
bl.CASE_ID
FROM
Patricia.dbo.BUDGET_LINE bl
JOIN cat_transaction ct ON bl.EXT_REFERENCE = ct.id
WHERE
ct.CASE_ID = bl.CASE_ID
what is your logic to get this 121 id in the 1st place ?
the above query will all Case_IDs on the basis of the join factor
June 19, 2014 at 4:28 am
Because it is the only id at the moment in both tables. I set the default case in the cat_transactions table to 121 for the moment so that I can test. So that's why its the only case in both tables
June 19, 2014 at 4:34 am
If this query returns multiple Cat_IDs, which one do you want to be assigned to the variable?
SET @CaseID = (121)
-- SELECT
--bl.CASE_ID
-- FROM
--Patricia.dbo.BUDGET_LINE bl
-- JOIN cat_transaction ct ON bl.EXT_REFERENCE = ct.id
-- WHERE
--ct.CASE_ID = bl.CASE_ID
--)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2014 at 4:41 am
The one where it is the same in both tables. Im not sure If I can do it like this? Can this same variable contain different values at different times?
June 19, 2014 at 4:56 am
Following is the understanding
Cat_Transaction.ID should not be the part of the budget_line.Ref_Number Per Case_ID. all these rows needed to inserted into the budget_Line table.
if so then following is the query
DECLARE
@ChargeingID SMALLINT
--@LineNumber SMALLINT,
--@CaseID SMALLINT,
INSERT INTO Patricia.dbo.BUDGET_LINE
SELECT
ISNULL(xSeqNo.B_L_SEQ_NUMBER,1) AS B_L_SEQ_NUMBER,
ctt.WorkCodeID,
ctde.[pagecount],
null, --unit price
cv.name,
null, --offer number
null, --amount (need a price)
null, --invoice number
ct.Case_ID,
@ChargeingID, --need id to pass into variable
null, --b_l_is_external
null, --external_invoice_id
null, --external_invoice_date
null, --bl_org_quantity
null, --bl_org_unit_price
null, --bl_org_amount
null, --bl_org_journal_no_time_reg
null, --bl_advance_number
null, --bl_advance_date
0, --show time comment
null, --discount_prec
null, --discount_amount
null, --bl_unit_price_no_discount
0, --invoice preview
null, --currency_id
null, --exchange_rate
null, --collective_inv_log_id
null, --bl_comment
cv.name,
null, --earliest_invoice_date
null, --pl_org_unit_price
null, --pl_org_unit_price_no_discount
null, --pl_org_amount
null, --plorg_currency_id
'CH', --indicator
null, --purchase_order_number
null, --is_purchase
ct.creation, --recorded_date
ct.id --ext_reference
FROM cat_transaction ct
JOIN cat_validation cv ON cv.id = ct.chargeid
JOIN cat_trxtype ctt ON ctt.trxtype = ct.trxtype
JOIN cas_trx_doc_ext ctde ON ctde.x_id = ct.id
Cross Apply
(
Select MAX(B_L_SEQ_NUMBER) + 1 AS B_L_SEQ_NUMBER
from Patricia.dbo.BUDGET_LINE bl
where ct.CASE_ID = bl.CASE_ID
) xSeqNo
WHERE NOT EXISTS ( Select 1
from Patricia.dbo.BUDGET_LINE bl
where bl.EXT_REFERENCE = ct.id
and ct.CASE_ID = bl.CASE_ID
)
Hope it helps, Do test it thou
June 19, 2014 at 6:06 am
I'll try this code now thanks...If I may ask a stupid question that would make future queries easier for everyone, how do I post a block with the code in like you just did?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply