I am having some issues with a query to obtain some information

select id_column, d_description
from table1
where (select substring(cast(g_xml_comprobante as nvarchar(max)), charindex('contrato=', cast(g_xml_comprobante as nvarchar(max)))   10, 15) as 'contract' 
       from table1 a, table 3 b
       where convert(varchar(6), b.d_date, 112) > '202108' 
         and b.id_column = a.id_column) = '2019896177'

I get this error:

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

The situation is like this, we have XML information in a column inside table1, in the XML we have a contract number. So what I want is to obtain all of the IDs from certain contracts, I have the contracts values that is why I need it to be in the where.

Is it possible to accomplish it?

If I am not clear please let me know so I can clarify.

Thanks for the help!

CodePudding user response：

What Serge wrote is also right. But if you are just checking that particular contract Id exists in your subquery and for some reason multiple rows are having same contract so what you can do is. You can use In clause as below

select id_column, d_description
from table1
where '2019896177' in (select substring(CAST(g_xml_comprobante as nvarchar(max)),CHARINDEX('contrato=',CAST(g_xml_comprobante as nvarchar(max))) 10,15) as 'contract' from table1 a,table3 b
where convert(varchar(6),b.d_date,112) > '202108' and b.id_column=a.id_column
)

however second approach which is quite common while dealing with such type of error but that mainly depends on your data, what data is being returned. if your subquery is returning same value for all rows, then best way is to use Top 1 in your subquery.

select id_column, d_description
from table1
where (select Top 1 substring(CAST(g_xml_comprobante as nvarchar(max)),CHARINDEX('contrato=',CAST(g_xml_comprobante as nvarchar(max))) 10,15) as 'contract' from table1 a,table3 b
where convert(varchar(6),b.d_date,112) > '202108' and b.id_column=a.id_column
)='2019896177'

CodePudding user response：

Your query has a number of issues:

• Your primary issue: you are querying table1 a second time, there by cross joining the whole table again.
• Corollary to that, table3 also may have multiple results. So you actually need an EXISTS here.
• Furthermore, you can use XQuery to parse XML, instead of horrible string manipulation
• Dates should be queried by using date ranges, and if you really needed to truncate the time component, you can use cast( ... as date)
• Always use proper join syntax, rather than the deprecated old-style comma-join
• Make your queries readable: use whitespace, it's free
• Use sensible table aliases, not meaningless a b c

select
  t1.id_column,
  t1.d_description
from table1 t1
where exist (select 1
    from [table 3] t3
    where t3.d_date > '202108'
      and t3.id_column = t1.id_column
       -- not sure exactly what XQuery you are looking for
      and t3.g_xml_comprobante.exist('//@contrato[. = "2019896177"]') = 1
);

CodePudding user response：

You are trying to compare subquery results with constant. But your subquery returns more than one value. In such case, you may use ANY or ALL before the subquery. ANY means that the result is true if any of returned value fits the condition. ALL means that the result is true if all of returned values fit the condition.