Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,790 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

604 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