CODEWITHSUNDEEP
sqlsql-servert-sql
michNik
michNik

Reputation: 7

Subquery with systables returned more than 1 value

declare @Table nvarchar(150) = 'T_PROJEKT'
declare @Column nvarchar(max) =(
select STRING_AGG(CONCAT(col.name, ' ',  typ.name
                   , CASE
                        WHEN typ.name in ('nchar' , 'varchar', 'nvarchar')
                            THEN concat('(',col.max_length/2 , ')')
                        WHEN typ.name = 'datetime2'
                            THEN '(7)'
                        WHEN typ.name in ('decimal' , 'numeric')
                            THEN concat('(',col.precision , ',' ,col.scale, ')')
                        ELSE '' end), ',' )           
FROM sys.objects obj
JOIN sys.columns col on col.object_id = obj.object_id
JOIN sys.types typ ON col.user_type_id = typ.user_type_id
JOIN DISPO.T_TABELLEN dt on COALESCE(dt.OBJECT_ID_VIEW,dt.OBJECT_ID_HERKUNFT) = obj.object_id
                        AND dt.OBJECT_ID IS NULL
Where TAB_NAME = @Table
GROUP BY obj.name, SCH_NAME, TAB_NAME
)

This is the result:

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

Where can be my mistake? Any suggestions Thanks a lot!

Upvotes: 0

Views: 58

Answers (1)

BrianOrion
BrianOrion

Reputation: 59

Well the mistake is in the subquery as it states in error. So i would guess the error comes somewhere in between the case. To pin point the issue some data would be very useful. If you can't give any i would suggest you try using TOP() OR MAX() , where you think you get more results.

Upvotes: 0

Related Questions