Solved

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

Posted on 2006-11-29
13
4,700 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
  • 7
  • 6
13 Comments
 
LVL 142

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 142

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
 
LVL 142

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 142

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 142

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 142

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now