How can I make this query work.

Last Post 14 Feb 2006 03:18 PM by mwesch. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
werice
New Member
New Member

--
14 Feb 2006 03:07 PM
I have a function that returns a field 'contracttypecode' The returned data looks like this:

select * from udfGetContractCode('ctfr')

it returns
contracttypecode
---------------------
('ctr','ctf')

The Function name is dbo.udfGetContractCode


My query looks like:

query1
select
*
from
ct_contract
where
contracttype in (select * from udfGetContractCode('ctfr'))

I get no data back:

I know data is their because when I run:

query2
select
*
from
ct_contract
where
contracttype in ('ctr','ctf')

I get lots of data returned:

Does anyone know why query1 ,useing the function, does not return any data
mwesch
New Member
New Member

--
14 Feb 2006 03:18 PM
Because a table 1 row having a value ('ctr','ctf') is not the same thing as a table with 2 rows having values 'ctr' and 'ctf'.

Your Query1 is equivalent to this:

select *
from ct_contract
where contracttype = '(''ctr'',''ctf'')' --all single quotes

You need to revise you function to return 2 rows, and then re-write your query as:

select *
from ct_contract
where contracttype in (select contracttypecode from udfGetContractCode('ctfr'))

OR

select *
from ct_contract a
inner join udfGetContractCode('ctfr') b
on a.contracttype = b.contracttypecode

mwesch
New Member
New Member

--
14 Feb 2006 03:27 PM
Not sure what your function does, but here is a simplified version of what I mean.

--------------------------------------------------------------------

create function udfGetContractCode (@ContractTypeCode varchar(10))
returns @results table (ContractTypeCode char(3))
as

begin

if @ContractTypeCode = 'CTF'
begin
insert @results select 'CTF' as ContractTypeCode
end
else if @ContractTypeCode = 'CTR'
begin
insert @results select 'CTR' as ContractTypeCode
end
else if @ContractTypeCode = 'CTFR'
begin
insert @results select 'CTF' as ContractTypeCode union all select 'CTR' as ContractTypeCode
end

return

end

You are not authorized to post a reply.

Acceptable Use Policy