Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Saturday, July 30, 2016 2:21 AM
Hello Folks,
There is a stored procedure code as below while executing the below code i am getting error as below.
error message:
Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a context where a condition is expected, near 'Name'.
can one any idea in which condition which i have to check.
Regards,
Venkat Sudheer K
Monday, August 1, 2016 2:27 AM ✅Answered | 3 votes
That's much too long and complex for it to be likely that one of us will find the error. You will probably need to find it yourself.
Since the error is a Boolean expression expected but not found, look for places in your code where boolean expressions are expected (WHERE clauses, ON clauses, WHEN clauses of CASE statements, etc).
Since you are executing dynamic SQL, it is likely the error is that the error is in the dynamic SQL.
You probably want to add debugging code to narrow down where the error is. I would add some print statements to help find where the error is. I would also print the dynamic SQL before you execute it so you can examine it, because there is a good chance that is where the error is.
Tom
PS When posting code please use the Insert Code Block. Don't just paste the code directly into the post. See https://social.msdn.microsoft.com/Forums/en-US/8e38b637-2a1e-4681-8937-337372b4d0fe/posting-tips-code-images-hyperlinks-details?forum=transactsql for instructions on how to do that.
Sunday, July 31, 2016 11:16 PM
Moving this thread the more appropriate Transact-SQL forum.
Please post the query that is causing the error so that we can better help you.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Monday, August 1, 2016 1:38 AM
CREATE PROCEDURE [dbo].[Derive_Product_Technology]
AS
BEGIN
SET NOCOUNT ON;
declare @RID int
declare @Product_Technology varchar(50)
declare @Search_Element varchar(256)
declare @Relationship varchar(10)
declare @Search_Value varchar(256)
declare @From_Table varchar(100)
declare @Join_Table varchar(100)
declare @Join_Xml_Name varchar(100)
declare @Chunk_Contains varchar(50)
declare @Chunk_DoesNot_Contain varchar(50)
declare @Product_Name_Contains_1 varchar(50)
declare @Product_Name_Contains_2 varchar(50)
declare @Product_Name_Contains_3 varchar(50)
declare @Product_Name_DoesNot_Contain_1 varchar(50)
declare @Product_Name_DoesNot_Contain_2 varchar(50)
declare @Product_Name_DoesNot_Contain_3 varchar(50)
declare @SQLSelectCMD as varchar(4096)
declare @SQLInsertCMD as varchar(4096)
declare @SQLUpdateClause as varchar(4096)
declare @SQLSelectClause as varchar(4096)
declare @SQLFromClause as varchar(4096)
declare @SQLJoinClause as varchar(4096)
declare @SQLWhereClause as varchar(4096)
declare @i int
declare @RowCount int
declare @RIDSQLTMP int
declare @REL_Chunk_Contains varchar(100)
declare @REL_Chunk_DoesNot_Contain varchar(100)
declare @REL_Product_Name_Contains_1 varchar(100)
declare @REL_Product_Name_Contains_2 varchar(100)
declare @REL_Product_Name_Contains_3 varchar(100)
declare @REL_Product_Name_DoesNot_Contain_1 varchar(100)
declare @REL_Product_Name_DoesNot_Contain_2 varchar(100)
declare @REL_Product_Name_DoesNot_Contain_3 varchar(100)
declare @REL_CON_Chunk_Contains varchar(100)
declare @REL_CON_Chunk_DoesNot_Contain varchar(100)
declare @REL_CON_Product_Name_Contains_1 varchar(100)
declare @REL_CON_Product_Name_Contains_2 varchar(100)
declare @REL_CON_Product_Name_Contains_3 varchar(100)
declare @REL_CON_Product_Name_DoesNot_Contain_1 varchar(100)
declare @REL_CON_Product_Name_DoesNot_Contain_2 varchar(100)
declare @REL_CON_Product_Name_DoesNot_Contain_3 varchar(100)
-- Set row counters.
set @i = 0
set @RowCount = (select COUNT(*) from CLiC_UI.dbo.REF_Product_Technology_Rules)
-- Reset IsProcessed flag.
update CLiC_UI.dbo.REF_Product_Technology_Rules set IsProcessed = 0
--
-- Empty the work table.
--
delete from CLiC.dbo.WRK_Product_Technology_SQL
while @i <> @RowCount
begin
select
@RID = RID,
@Product_Technology = Product_Technology,
@Search_Element = Search_Element,
@Relationship = Relationship,
@Search_Value = char(39) + Search_Value + char(39),
@From_Table = From_Table,
@Join_Table = Join_Table,
@Join_Xml_Name = char(39) + Join_Xml_Name + char(39),
@Chunk_Contains = char(39) + Chunk_Contains + char(39),
@Chunk_DoesNot_Contain = char(39) + Chunk_DoesNot_Contain + char(39),
@Product_Name_Contains_1 = char(39) + Product_Name_Contains_1 + char(39),
@Product_Name_Contains_2 = char(39) + Product_Name_Contains_2 + char(39),
@Product_Name_Contains_3 = char(39) + Product_Name_Contains_3 + char(39),
@Product_Name_DoesNot_Contain_1 = char(39) + Product_Name_DoesNot_Contain_1 + char(39),
@Product_Name_DoesNot_Contain_2 = char(39) + Product_Name_DoesNot_Contain_2 + char(39),
@Product_Name_DoesNot_Contain_3 = char(39) + Product_Name_DoesNot_Contain_3 + char(39)
from CLiC_UI.dbo.REF_Product_Technology_Rules
where RID = (select min(RID) from CLiC_UI.dbo.REF_Product_Technology_Rules where IsProcessed = 0)
if @Search_Element is null set @Search_Element = CHAR(32)
if @Search_Value is null set @Search_Value = CHAR(32)
if @From_Table is null set @From_Table = CHAR(32)
if @Join_Table is null set @Join_Table = CHAR(32)
if @Join_Xml_Name is null set @Join_Xml_Name = CHAR(32)
if @Chunk_Contains is null set @Chunk_Contains = CHAR(32)
if @Chunk_DoesNot_Contain is null set @Chunk_DoesNot_Contain = CHAR(32)
if @Product_Name_Contains_1 is null set @Product_Name_Contains_1 = CHAR(32)
if @Product_Name_Contains_2 is null set @Product_Name_Contains_2 = CHAR(32)
if @Product_Name_Contains_3 is null set @Product_Name_Contains_3 = CHAR(32)
if @Product_Name_DoesNot_Contain_1 is null set @Product_Name_DoesNot_Contain_1 = CHAR(32)
if @Product_Name_DoesNot_Contain_2 is null set @Product_Name_DoesNot_Contain_2 = CHAR(32)
if @Product_Name_DoesNot_Contain_3 is null set @Product_Name_DoesNot_Contain_3 = CHAR(32)
if @Join_Xml_Name <> CHAR(32) set @Join_Xml_Name = ' AND Xml_Name = ' + @Join_Xml_Name
--
set @REL_Chunk_Contains =
case
when @Chunk_Contains = CHAR(32) then CHAR(32)
when @Chunk_Contains <> CHAR(32) then ' Chunk_Value_En LIKE '
end
set @REL_CON_Chunk_Contains =
case
when @Chunk_Contains = CHAR(32) then CHAR(32)
when @Chunk_Contains <> CHAR(32) then ' AND '
end
--
set @REL_Chunk_DoesNot_Contain =
case
when @Chunk_DoesNot_Contain = CHAR(32) then CHAR(32)
when @Chunk_DoesNot_Contain <> CHAR(32) then ' Chunk_Value_En NOT LIKE '
end
set @REL_CON_Chunk_DoesNot_Contain =
case
when @Chunk_DoesNot_Contain = CHAR(32) then CHAR(32)
when @Chunk_DoesNot_Contain <> CHAR(32) then ' AND '
end
--
set @REL_Product_Name_Contains_1 =
case
when @Product_Name_Contains_1 = CHAR(32) then CHAR(32)
when @Product_Name_Contains_1 <> CHAR(32) then ' A.Product_Number_Name LIKE '
end
set @REL_CON_Product_Name_Contains_1 =
case
when @Product_Name_Contains_1 = CHAR(32) then CHAR(32)
when @Product_Name_Contains_1 <> CHAR(32) then ' AND '
end
--
set @REL_Product_Name_Contains_2 =
case
when @Product_Name_Contains_2 = CHAR(32) then CHAR(32)
when @Product_Name_Contains_2 <> CHAR(32) then ' A.Product_Number_Name LIKE '
end
set @REL_CON_Product_Name_Contains_2 =
case
when @Product_Name_Contains_2 = CHAR(32) then CHAR(32)
when @Product_Name_Contains_2 <> CHAR(32) then ' AND '
end
--
set @REL_Product_Name_Contains_3 =
case
when @Product_Name_Contains_3 = CHAR(32) then CHAR(32)
when @Product_Name_Contains_3 <> CHAR(32) then ' A.Product_Number_Name LIKE '
end
set @REL_CON_Product_Name_Contains_3 =
case
when @Product_Name_Contains_3 = CHAR(32) then CHAR(32)
when @Product_Name_Contains_3 <> CHAR(32) then ' AND '
end
--
set @REL_Product_Name_DoesNot_Contain_1 =
case
when @Product_Name_DoesNot_Contain_1 = CHAR(32) then CHAR(32)
when @Product_Name_DoesNot_Contain_1 <> CHAR(32) then ' A.Product_Number_Name NOT LIKE '
end
set @REL_CON_Product_Name_DoesNot_Contain_1 =
case
when @Product_Name_DoesNot_Contain_1 = CHAR(32) then CHAR(32)
when @Product_Name_DoesNot_Contain_1 <> CHAR(32) then ' AND '
end
--
set @REL_Product_Name_DoesNot_Contain_2 =
case
when @Product_Name_DoesNot_Contain_2 = CHAR(32) then CHAR(32)
when @Product_Name_DoesNot_Contain_2 <> CHAR(32) then ' A.Product_Number_Name NOT LIKE '
end
set @REL_CON_Product_Name_DoesNot_Contain_2 =
case
when @Product_Name_DoesNot_Contain_2 = CHAR(32) then CHAR(32)
when @Product_Name_DoesNot_Contain_2 <> CHAR(32) then ' AND '
end
--
set @REL_Product_Name_DoesNot_Contain_3 =
case
when @Product_Name_DoesNot_Contain_3 = CHAR(32) then CHAR(32)
when @Product_Name_DoesNot_Contain_3 <> CHAR(32) then ' A.Product_Number_Name NOT LIKE '
end
set @REL_CON_Product_Name_DoesNot_Contain_3 =
case
when @Product_Name_DoesNot_Contain_3 = CHAR(32) then CHAR(32)
when @Product_Name_DoesNot_Contain_3 <> CHAR(32) then ' AND '
end
--
-- Build SQL Clauses.
--
set @SQLUpdateClause = 'UPDATE CLiC.dbo.WRK_Product_Hierarchy SET Product_Technology = ' + CHAR(39) + @Product_Technology + CHAR(39) + ' WHERE Product_Number in ('
set @SQLSelectClause = 'SELECT A.Product_Number FROM CLiC.dbo.' + @From_Table + ' as A '
set @SQLJoinClause =
case
when @Join_Table = CHAR(32) then CHAR(32)
when @Join_Table <> CHAR(32) then ' JOIN CLiC.dbo.' + @Join_Table + ' as B ON A.Product_Number = B.Product_Number '
end
set @SQLWhereClause =
'WHERE ' +
@Search_Element + ' ' +
@Relationship + ' ' +
@Search_Value +
@Join_Xml_Name +
@REL_CON_Chunk_Contains +
@REL_Chunk_Contains +
@Chunk_Contains +
@REL_CON_Chunk_DoesNot_Contain +
@REL_Chunk_DoesNot_Contain +
@Chunk_DoesNot_Contain +
@REL_CON_Product_Name_Contains_1 +
@REL_Product_Name_Contains_1 +
@Product_Name_Contains_1 +
@REL_CON_Product_Name_Contains_2 +
@REL_Product_Name_Contains_2 +
@Product_Name_Contains_2 +
@REL_CON_Product_Name_Contains_3 +
@REL_Product_Name_Contains_3 +
@Product_Name_Contains_3 +
@REL_CON_Product_Name_DoesNot_Contain_1 +
@REL_Product_Name_DoesNot_Contain_1 +
@Product_Name_DoesNot_Contain_1 +
@REL_CON_Product_Name_DoesNot_Contain_2 +
@REL_Product_Name_DoesNot_Contain_2 +
@Product_Name_DoesNot_Contain_2 +
@REL_CON_Product_Name_DoesNot_Contain_3 +
@REL_Product_Name_DoesNot_Contain_3 +
@Product_Name_DoesNot_Contain_3
-- Build a SQL "select" query and insert it into the temp table.
set @SQLSelectCMD = @SQLSelectClause + @SQLJoinClause + rtrim(@SQLWhereClause) + ' GROUP BY A.Product_Number'
-- Build a SQL "insert" query and insert it into the temp table.
set @SQLInsertCMD = @SQLUpdateClause + @SQLSelectClause + @SQLJoinClause + rtrim(@SQLWhereClause) + ' GROUP BY A.Product_Number) AND Product_Technology IS NULL'
-- Insert querries into the table.
insert into CLiC.dbo.WRK_Product_Technology_SQL (REF_RID, Product_Technology, SQLSelectCMD, SQLInsertCMD, Load_Date) values (@RID, @Product_Technology, @SQLSelectCMD, @SQLInsertCMD, GETDATE())
-- Update the row counter.
set @i = @i + 1
-- Update the IsProcessed flag.
update CLiC_UI.dbo.REF_Product_Technology_Rules
set IsProcessed = 1
where RID = @RID
if @i = @RowCount
break
else
continue
end
--
-- Execute the dynamically generated SQL querries.
--
set @i = 0
set @RowCount = (select COUNT(*) from CLiC.dbo.WRK_Product_Technology_SQL)
while @i <> @RowCount
begin
set nocount on
set @RIDSQLTMP = (select MIN(RID) from CLiC.dbo.WRK_Product_Technology_SQL where IsProcessed = 0)
set @SQLInsertCMD = (select SQLInsertCMD from CLiC.dbo.WRK_Product_Technology_SQL where RID = @RIDSQLTMP)
BEGIN TRANSACTION
EXEC (@SQLInsertCMD)
update CLiC.dbo.WRK_Product_Technology_SQL
set Rows_Processed = @@ROWCOUNT
where RID = @RIDSQLTMP
if @@ERROR = 0
COMMIT TRANSACTION
else
ROLLBACK TRANSACTION
set @i = @i + 1
update CLiC.dbo.WRK_Product_Technology_SQL
set IsProcessed = 1
where RID = @RIDSQLTMP
if @i = @RowCount
break
else
continue
end
-- Identify all products with missing Specification data
BEGIN TRANSACTION
update WRK_Product_Hierarchy
set product_technology = '*Not.Found.Missing.Spec*'
where OID in
(
select
a.OID
from WRK_Product_Hierarchy as a
left join dbo.Product_Specification as b
on a.OID = b.OID
left join Product_PLC as c
on a.OID = c.OID
where a.Product_Technology is null
and b.Xml_Name is null
and c.Life_Cycle_Status is not null
)
AND Product_Technology is NULL
COMMIT TRANSACTION
-- Identify all products with missing PLC data
BEGIN TRANSACTION
update WRK_Product_Hierarchy
set product_technology = '*Not.Found.Missing.PLC*'
where OID in
(
select
a.OID
from WRK_Product_Hierarchy as a
left join dbo.Product_Specification as b
on a.OID = b.OID
left join Product_PLC as c
on a.OID = c.OID
where a.Product_Technology is null
and b.Xml_Name is not null
and c.Life_Cycle_Status is null
)
AND Product_Technology is NULL
COMMIT TRANSACTION
-- Identify all products with missing Specification and PLC data
BEGIN TRANSACTION
update WRK_Product_Hierarchy
set product_technology = '*Not.Found.Missing.Spec.PLC*'
where OID in
(
select
a.OID
from WRK_Product_Hierarchy as a
left join dbo.Product_Specification as b
on a.OID = b.OID
left join Product_PLC as c
on a.OID = c.OID
where a.Product_Technology is null
and b.Xml_Name is null
and c.Life_Cycle_Status is null
)
AND Product_Technology is NULL
COMMIT TRANSACTION
BEGIN TRANSACTION
update WRK_Product_Hierarchy
set product_technology = '*Not.Found.Missing.PT-Dependant-Spec*'
where Product_Number in
(
select
a.Product_Number
from WRK_Product_Hierarchy as a
left join dbo.Product_Specification as b
on a.OID = b.OID
where a.Product_Technology is null
and b.Xml_Name Not In
(
select Join_Xml_Name from CLiC_UI.dbo.REF_Product_Technology_Rules where Join_Xml_Name is not null group by Join_Xml_Name
)
)
AND product_technology is NULL
COMMIT TRANSACTION
BEGIN TRANSACTION
update WRK_Product_Hierarchy
set product_technology = '*Not.Found.Missing.PT-Dependant-Spec*'
where Product_Number in
(
select
a.Product_Number
from WRK_Product_Hierarchy as a
left join dbo.Product_Specification as b
on a.OID = b.OID
where a.Product_Technology is null
and b.Xml_Name Not In
(
select Search_Value from CLiC_UI.dbo.REF_Product_Technology_Rules where Search_Element = 'Xml_Name' group by Search_Value
)
)
AND product_technology is NULL
COMMIT TRANSACTION
END
Monday, August 1, 2016 3:00 AM
Thank you Tom
Monday, August 1, 2016 5:11 AM
Hi Venkat Sudheer K,
>> An expression of non-boolean type specified in a context where a condition is expected, near 'Name'.
According to the error message I suppose that you use a non-boolean type expression as a condition in your code. In the line 711, you execute the “@SQLInsertCMD” and in the line 633, you create the value of it. But, since the value of “@SQLUpdateClause” has a “in clause”, it seems that there’s no one “)” to map the “(” of the in clause. So, you should pay attention to it.
Best Regards,
Zhang Albert
Monday, August 1, 2016 5:43 AM
Hi Zhang Albert,
Thank you for the suggestion, if i would use as below query may be i will be good to avoid that error? if i wrong please let me know.
set @SQLInsertCMD =@SQLUpdateClause + @SQLSelectClause + @SQLJoinClause + rtrim(@SQLWhereClause) + ' GROUP BY A.Product_Number AND Product_Technology IS NULL'
Regards,
Venkat Sudheer K,
Monday, August 1, 2016 10:40 AM
The GROUP BY condition is wrong (you can not use AND in the GROUP BY). Tom is correct, you need to print your SQL before executing, this way the error will be much easier to find.
For every expert, there is an equal and opposite expert. - Becker's Law