?
Solved

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

Posted on 2006-11-29
13
Medium Priority
?
4,767 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 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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
Suggested Courses

771 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