Solved

An expression of non-boolean type specified in a context where a condition is expected, near ')'

Posted on 2006-11-29
13
4,749 Views
Last Modified: 2008-01-09
I am getting this error multiple times:

An expression of non-boolean type specified in a context where a condition is expected, near ')'

with this chunk of code:

select distinct a.city from  trans_link tl, custom_field_value cfv, person p, addresses a, custom_field_value cfv2,  pick_list pl
where  tl.transact_id = 8
            AND tl.translink_id = cfv.primary_id
            AND cfv.field_id in (750, 757, 769)
            and tl.primary_person_id = p.person_id
            and p.address_id = a.address_id            
            AND tl.inst_id = cfv2.primary_id
            AND cfv2.field_id = 322 AND ISNUMERIC(cfv2.value_data) = 1
            AND convert(decimal(16,4),'0' + Rtrim(Ltrim(Replace(Replace(cfv2.value_data,'$',''),',',''))))  in (@hv_lead@)
            AND convert(decimal(16,4),'0' + Rtrim(Ltrim(Replace(Replace(cfv2.value_data,'$',''),',',''))))   > 0
      AND a.country = 'United States' and a.state = pl.name and pl.category_id = 'State List' and  pl.picklist_id in (@your_state@)

any help would be greatly appreciated!!  Thanks in advance.
0
Comment
Question by:TimFred
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18037296
where does the trailing @ come from?

in tsql, variables are only starting with @

select distinct a.city from  trans_link tl, custom_field_value cfv, person p, addresses a, custom_field_value cfv2,  pick_list pl
where  tl.transact_id = 8
          AND tl.translink_id = cfv.primary_id
          AND cfv.field_id in (750, 757, 769)
          and tl.primary_person_id = p.person_id
          and p.address_id = a.address_id          
          AND tl.inst_id = cfv2.primary_id
          AND cfv2.field_id = 322 AND ISNUMERIC(cfv2.value_data) = 1
          AND convert(decimal(16,4),'0' + Rtrim(Ltrim(Replace(Replace(cfv2.value_data,'$',''),',',''))))  in (@hv_lead)
          AND convert(decimal(16,4),'0' + Rtrim(Ltrim(Replace(Replace(cfv2.value_data,'$',''),',',''))))   > 0
      AND a.country = 'United States' and a.state = pl.name and pl.category_id = 'State List' and  pl.picklist_id in (@your_state)

also, IN (@VARIABLE) will not work as you might expect it, it will not evaluate the string value into a list of values (if delimited by commas)...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18037317
to solve that second part, create the following function:



CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    



and use it in your query:

WHERE ...  IN ( SELECT value FROM dbo.ParmsToList (@hv_lead) )
  AND pl.picklist_id in ( SELECT value FROM dbo.ParmsToList(@your_state) )
0
 

Author Comment

by:TimFred
ID: 18037416
damn that was fast. thanks!  I am removing the second @ throughout the program.  the reason this was there is that we are converting a Sybase database to SQL server 2005.  I am still trying to understand your function.  I will get back to when I have worked through your answers ....
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18037471
run this sample query to understand what the function is doing:

SELECT value FROM dbo.ParmsToList ('1,2,3')
0
 

Author Comment

by:TimFred
ID: 18039853
angelIII,

The saga continues. I managed to get rid of the original error.  Now I am getting this error:
"Incorrect Syntax near the keyword 'in'  

I think the error is happening here:

select distinct pl.picklist_id, a.state from  trans_link tl, custom_field_value cfv, person p, addresses a, custom_field_value cfv2,  pick_list pl
where  tl.transact_id = 8
            AND tl.translink_id = cfv.primary_id
            AND cfv.field_id in (750, 757, 769)
            and tl.primary_person_id = p.person_id
            and p.address_id = a.address_id            
            AND tl.inst_id = cfv2.primary_id
            AND cfv2.field_id = 322 AND ISNUMERIC(cfv2.value_data) = 1
            AND convert(int, isnull(cfv2.value_data,0))  in (@hv_lead@)
            AND convert(int, isnull(cfv2.value_data,0))   > 0
      AND a.country = 'United States' and a.state = pl.name and pl.category_id = 'State List'

I kept both @@ because you need them.  @hv_lead@ isn't comma delimeted so I don't think the function is needed.
This is the query that creates @hv_lead@:

SELECT 0 as picklist_id, '(Blank)' as name UNION
SELECT DISTINCT p.picklist_id, LEFT(p.name, charindex(',', p.name) - 1) as name
FROM custom_field_value cfv, pick_list p
WHERE cfv.field_id = 322
AND CAST(cfv.value_data as INT) =  p.picklist_id
ORDER BY 2

Why isn't the "in" statement working?

I will award the points soon I think we are almost there ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18039874
>SELECT 0 as picklist_id, '(Blank)' as name

this will return 2 columns. IN() can only work with 1 column. you will have to remove the second field in the query.
0
 

Author Comment

by:TimFred
ID: 18039931
that's what I was worried about.  I need to have @hv_lead@ = the two columns.

can I do something like this?:

AND convert(int, isnull(cfv2.value_data,0))  in (@hv_lead@.picklist_id)

0
 

Author Comment

by:TimFred
ID: 18040021
nope can't do that.  In sybase it defaulted to the number field with the IN statement so it worked.  any ideas?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18040042
> I need to have @hv_lead@ = the two columns.
why?
0
 

Author Comment

by:TimFred
ID: 18040063
@hv_lead@ is called by our system.  it creates a parameter screen that a user picks the p.name(s) that are listed
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18040103
possibly this will work:
AND convert(int, isnull(cfv2.value_data,0))  in ( select picklist_id from ( @hv_lead@) as l )
0
 

Author Comment

by:TimFred
ID: 18040106
maybe we can write a function that returns the just the picklist_id's from @hv_lead@ ?  and incorporate the function into the in statement ?
0
 

Author Comment

by:TimFred
ID: 18040157
good idea:   ( select picklist_id from ( @hv_lead@) as l )

but it did not work.  hmmmmmmmmmmm .....
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question