Solved

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

Posted on 2006-11-29
13
4,719 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

825 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