CODEWITHSUNDEEP
sqlsql-servert-sql
user3561219
user3561219

Reputation: 17

Please help me correct the syntax error

I get the an error when I execute the query below. what I'm doing wrong?

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

select

     so.name 'Table Name'
    ,so.id 'Table ID'
     ,so.xtype
     ,sc.name 'Column Name'
     ,sc.id 'Column ID'
     ,sf.constid
     ,sf.fkeyid 'Object ID of the table with FOREIGN KEY'
     ,sf.rkeyid 'Referenced Table ID'
     ,(select o.name 'Referenced Table' 
    from sysforeignkeys f
     inner join sysobjects o
      on o.id=f.rkeyid
      where o.xtype='U') 

    from sysobjects so
   inner join syscolumns sc 
    on so.id=sc.id
      inner join sysforeignkeys sf
     on so.id=sf.fkeyid 
     where so.xtype='U'
    and (sc.name like 'SSN'
     OR sc.name LIKE 'ssn%'         
     OR sc.name LIKE 'ssn%'         
      OR sc.name LIKE '%_ssn%'          
    OR sc.name LIKE '_ocsecno'          
    OR sc.name LIKE 'Ssn%');

Upvotes: 1

Views: 108

Answers (3)

Stephan
Stephan

Reputation: 6018

I don't think your subquery is correct as it has no way of referencing your sysobjects aliased "so". Try this instead. Also I don't think you need such a long where clause.

select so.name      [Table Name]
        ,so.id      [Table ID]
        ,so.xtype
        ,sc.name    [Column Name]
        ,sc.id      [Column ID]
        ,sf.constid
        ,sf.fkeyid  [Object ID of the table with FOREIGN KEY]
        ,sf.rkeyid  [Referenced Table ID]
        ,zz.name    [Reference Table]
from sysobjects so
inner join syscolumns sc     on so.id = sc.id
inner join sysforeignkeys sf on so.id = sf.fkeyid 

--Use a join here for the reference table column
inner join sysobjects zz     on zz.id = sf.rkeyid

where   so.xtype='U'
        AND(
            sc.name LIKE '%ssn%'
            OR sc.name LIKE '_ocsecno'
            )

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

select so.name      [Table Name]
        ,so.id      [Table ID]
        ,so.xtype
        ,sc.name    [Column Name]
        ,sc.id      [Column ID]
        ,sf.constid
        ,sf.fkeyid  [Object ID of the table with FOREIGN KEY]
        ,sf.rkeyid  [Referenced Table ID]
        ,(select TOP 1 o.name 
          from sysforeignkeys f
          inner join sysobjects o on o.id=f.rkeyid
          where o.xtype='U') AS [Referenced Table]
from sysobjects so
inner join syscolumns sc     on so.id = sc.id
inner join sysforeignkeys sf on so.id = sf.fkeyid 
where so.xtype='U'
  and (   sc.name like 'SSN'  --<-- Two operator together "LIKE" and  "="
       OR sc.name LIKE 'ssn%'         
       OR sc.name LIKE 'ssn%'         
       OR sc.name LIKE '%_ssn%'          
       OR sc.name LIKE '_ocsecno'          
       OR sc.name LIKE 'Ssn%');

Important Note

A sub-query in Select MUST return a scalar value, add TOP 1 to your sub-query and it should fix the error.

Also use Square Brackets [] for column names instead of literal strings.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

The problem is in your where clause

 .....
 where so.xtype='U'
    and (sc.name like 'SSN' -- Here you have a unwanted = or sc.name = 'SSN'
     OR sc.name LIKE 'ssn%'         
     OR sc.name LIKE 'ssn%'         
      OR sc.name LIKE '%_ssn%'          
    OR sc.name LIKE '_ocsecno'          
    OR sc.name LIKE 'Ssn%');

Upvotes: 1

Related Questions