Sql-server – SQL Server Query column from Select

sql servert-sql

Can anyone please guide me what's wrong in this SQL Server query:

SELECT 
    A.*,
    (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) Name 
FROM A;

It seems that A.Name is inaccessible in the query.

I want to populate a column in select query with data from another table. I can't use INNER JOIN since there isn't any FK relationship between tables.

Actually I want to insert data from table A into another table C, but for insert I need to populate a NOT NULL Column in table C, that I am trying to populate with this query (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) as column.

Any help would be really appreciated.

Best Answer

The problem is just as the error message explains. The second argument of CONTAINS must be a literal string or a variable. It’s not the case that everywhere a string is allowed in T-SQL that you can also use a column name.

You could do something kludgy like this, but performance would likely be abominable.

CREATE FUNCTION DoesContain(@arg VARCHAR(30))
RETURNS tinyint AS BEGIN
  RETURN
    CASE WHEN EXISTS (
      SELECT * FROM B
      WHERE CONTAINS([Name],@arg)
    ) THEN 1 ELSE 0 END
END;
GO

SELECT
  A.*,
  (SELECT TOP 1 B.Name FROM B WHERE dbo.DoesContain(A.Name)=1) as [Name]
FROM A;

Related Solutions

SQL Server – Group By with Multiple Rows of Same Value

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id 
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id, 
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b 
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;

Decrypt Data in SQL Server Column Before Loading into Destination

When calls to decrypt methods return NULL, either the column is NULL or the account that is using the methods doesn't have access to the key.

From BOL

Permissions

Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.

Look at at the account that is executing the SSIS package and verify it has the required permissions.

You can also launch SSMS as the account that runs the SSIS packages and try stepping through the underlying code of DecryptByKeyAutoCert. Something approximately like

OPEN SYMMETRIC KEY MyKeyNameHere
   DECRYPTION BY CERTIFICATE Usercert;

SELECT 
    city_id
,   CONVERT(nvarchar(50), DecryptByKey(city_name)) AS city_name
FROM city;

CLOSE SYMMETRIC KEY MyKeyNameHere;

Best Answer

Related Solutions

SQL Server – Group By with Multiple Rows of Same Value

Decrypt Data in SQL Server Column Before Loading into Destination

Related Question