March 31, 2015 at 3:26 pm
'/ error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
explanation
trying to merge 2 queries.
I usually will run the first query
SELECT * FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'
Result row will look like this with column ID, policy and EFFDTE
id ----------------- POLICY --------------- EFFDTE
9361343 ----- CMPNE08929 -------- 20091101
I am now trimming some of the result using them as a variable to run the second query on another table .
Column 1 value result=policy( divided between N#1 and 2 ; first 6 char to policyprefix and last 8 to policyID)
1.SET @PolicyPrefix = SELECT SUBSTRING(Policy, 1, 5)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'; --'CMPMO' ------notices it only the policy letter part from the search result
2.SET @PolicyID = SELECT SUBSTRING(Policy,6,len(Policy)-5)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'; ---'08929' ------notices it only the policy number part from the search result
Column 2 value result= EFFDTE (only convert it to date type yyyy-mm-dd)
3.SET @PolicyEffDate = SELECT cast(cast(EFFDTE as varchar)as date)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' ----'2009-11-01' ------notices it has dashes from the search result still neet o change this to work
/'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DECLARE@ResultsTable TABLE (ColumnName nvarchar(200), ColumnValue nvarchar(100))
DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110),@TableName nvarchar(256), @TableName2 nvarchar(256)
DECLARE @PolicyPrefix nvarchar(5), @PolicyID nvarchar(10), @PolicyEffDate date
SET @TableName = ' '
--- this is how I will manually input the data before the second query
--SET @PolicyPrefix = 'CMPMO'
--SET @PolicyID = '21842'
--SET @PolicyEffDate = '2009-04-01'
----------the only modification should be on this couple line I supposed--
SET @PolicyPrefix = (SELECT SUBSTRING(Policy, 1, 5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%') --'CMPMO' ------notices it only the policy letter part from the search result
SET @PolicyID = (SELECT SUBSTRING(Policy,6,len(Policy)-5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%')---'08929' ------notices it only the policy number part from the search result
SET @PolicyEffDate = (SELECT cast(cast(EFFDTE as varchar)as date) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%') ----'2009-11-01' ------notices it has dashes from the search result
------------ This part below is part of the second query and should be fine meaning will not need a modification.----
SET @SearchStr2 = (SELECT [SystemAssignId] FROM PDCDBPRD.dbo.[CoPolicyPointer]
WHERE [PolicyPrefixCd] = @PolicyPrefix
AND [PolicyId] = @PolicyID
AND [PolicyEffDt] = @PolicyEffDate)
WHILE (@TableName IS NOT NULL) BEGIN
SET @ColumnName = QUOTENAME('SystemAssignId')
SET @TableName = (
SELECT MIN(QUOTENAME(TABLE_NAME))
FROM PDCDBPRD.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_NAME) > @TableName
)
SET @TableName2 = 'PDCDBPRD.dbo.' + @TableName
IF @ColumnName IS NOT NULL BEGIN
INSERT INTO @ResultsTable
EXEC(
--
'SELECT ''' + @TableName + ''', LEFT(' + @ColumnName + ', 100)
FROM ' + @TableName2 + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + ''''
)
END
--END
END
SELECT DISTINCT ColumnName, ColumnValue FROM @ResultsTable
END
GO
March 31, 2015 at 3:35 pm
What's the error? What are you trying to do?
March 31, 2015 at 3:39 pm
Linking Twitter convo HERE.
As I said on Twitter, your use of LIKE is returning too many result sets. You need to switch that to a solid value (as in = 'MySpecificString') or pull all the results into a temp table (SELECT ... INTO #MyTemp FROM... WHERE .... LIKE ) and then use that to loop through using a WHILE statement, or the third option is to find a better set based way of doing what you need to do.
Also, if your LIKE statement is returning multiple copies of the same value, use SELECT DISTINCT when setting your variables so that it returns only one row.
What happens when you run this by itself?
SELECT SUBSTRING(Policy, 1, 5)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'
And when you change it to?
SELECT DISTINCT SUBSTRING(Policy, 1, 5)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'
Lastly, remember that your variables MUST use one of the following two syntaxes:
SET @PolicyPrefix = (SELECT SUBSTRING(Policy, 1, 5)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%');
--Notice above parens around the SELECT query.
SELECT @PolicyPrefix = SUBSTRING(Policy, 1, 5)
FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%';
--Notice the move of the SELECT statement
I am curious. Why do you have to search for that specific substring? Why can't you spell out what you're looking for specifically?
March 31, 2015 at 3:40 pm
Luis Cazares (3/31/2015)
What's the error? What are you trying to do?
Luis, see Twitter convo link for a few more details. I made him post here because Twitter wasn't the place for a long discussion.
March 31, 2015 at 3:45 pm
the error now is Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
April 2, 2015 at 5:56 am
According to a Twitter post I saw from the OP, this has been resolved.
April 2, 2015 at 7:31 am
'/this are the changes I have made and this is the answer that has work for me. Thanks Everyone./'
BEGIN
DECLARE @ResultsTable TABLE (ColumnName nvarchar(200), ColumnValue nvarchar(100))
DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110),@TableName nvarchar(256), @TableName2 nvarchar(256)
DECLARE @PolicyPrefix nvarchar(5), @PolicyID nvarchar(10), @PolicyEffDate date, @Policy nvarchar(13)
--SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @TableName = ' '
--These values are used to search
SET @PolicyPrefix = (SELECT TOP 1 SUBSTRING(Policy, 1, 5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%') --'CMPMO' ------notices it only the policy letter part from the search result
SET @PolicyID = (SELECT TOP 1 SUBSTRING(Policy,6,len(Policy)-5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%')---'08929' ------notices it only the policy number part from the search result
SET @PolicyEffDate = (SELECT TOP 1 cast(cast(EFFDTE as varchar)as date) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%') ----'2009-11-01' ------notices it has dashes from the search result
--SELECT @PolicyPrefix = SUBSTRING(Policy, 1, 5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' --'CMPMO' ------notices it only the policy letter part from the search result
--SELECT @PolicyID = SUBSTRING(Policy,6,len(Policy)-5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'---'08929' ------notices it only the policy number part from the search result
--SELECT @PolicyEffDate = cast(cast(EFFDTE as varchar)as date) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' ----'2009-11-01' ------notices it has dashes from the search result
SET @SearchStr2 = (SELECT [SystemAssignId] FROM dbo.[CoPolicyPointer]
WHERE [PolicyPrefixCd] = @PolicyPrefix
AND [PolicyId] = @PolicyID
AND [PolicyEffDt] = @PolicyEffDate)
WHILE (@TableName IS NOT NULL) BEGIN
SET @ColumnName = QUOTENAME('SystemAssignId')
SET @TableName = (
SELECT MIN(QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_NAME) > @TableName
)
SET @TableName2 = 'dbo.' + @TableName
IF @ColumnName IS NOT NULL BEGIN
INSERT INTO @ResultsTable
EXEC(
'SELECT ''' + @TableName + ''', LEFT(' + @ColumnName + ', 100)
FROM ' + @TableName2 + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + ''''
)
END
END
SELECT DISTINCT ColumnName, ColumnValue FROM @ResultsTable
END
April 2, 2015 at 7:34 am
Thank you for posting your solution.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply