function to return "T" or "F "if rows returned

Thanks.   I'm getting a syntax error to do with parens "select top (1) " I think.

  I removed the two dates in your input parms.  I had the related line of code to thos commented out.  Do not need date params.

Here's error following by your solution that I edited out the unneeded params.

------------
Error:
Msg 170, Level 15, State 1, Procedure ABSFormerBorrower, Line 11
Line 11: Incorrect syntax near '('.
Msg 156, Level 15, State 1, Procedure ABSFormerBorrower, Line 32
Incorrect syntax near the keyword 'and'.
Msg 170, Level 15, State 1, Procedure ABSFormerBorrower, Line 35
Line 35: Incorrect syntax near ')'.
-------------------


Updated your function as follows:
-----



CREATE FUNCTION dbo.ABSFormerBorrower (
    @branchid int,
    @namesid int
   
)
RETURNS char(1)
AS
BEGIN
RETURN (
   CASE WHEN EXISTS(
       select top (1) 1
      from dbo.contracts c
     
      inner join dbo.names n
      on c.branchid = n.branchid
      and c.namesid = n.namesid

      inner join dbo.vwBcontrol v
      on v.branchid = c.branchid

      where (c.namesid = @namesid
                  and c.branchid = @branchid
                  and c.processstatus = 50 --former borrower
                  and c.loanamt > 0)
                         
                  and not exists
                        (select contrnumber from dbo.contracts c2
                        where processstatus not in (3, 45, 47) --open, PandL, NonFile
                        and c2.namesid = c.namesid
                        and c2.branchid = c.branchid
                        and c2.payoutdate = c.loandate)
                  and not exists
                        (select contrid from dbo.transactions t
                        where transtype >= '3000' and transtype < '3999' and c.branchid = t.branchid and c.contrid = t.contrid
                        and t.namesid = c.namesid))
    THEN 'T' ELSE 'F' END
)
END --FUNCTION

Interesting -- I don't get any errors when I parse that code under SQL 2005.

Try changing:

select top (1) 1

to:

select 1

Since we've specified "EXISTS", SQL "knows" to check for only a single row anyway -- the TOP (1) was more as a type of documentation for a person reading it later.

Well, I tried something else on impulse before you got back to me, and I have created a problem of a different sort...

I changed it to select count(*) to see if it would run...

... it "completed successfully", but I can't find the function.

If I run the script again, I get "already exists" so I know it is somewhere..

Ok, so, I am only working in 1 database on 1 server.

If I on "Home" server in "Loan database"

and I run a script that starts with "create FUNCTION dbo.fnFormerBorrower" and it executes successfully, where the heck is the function that it generated?

I can't find under "Programmability/Functions" in the database and if I run:

select dbo.fnFormerBorrower(1,2)

I get return message:

1 row(s) affected

Re: last post, I did refresh as well as close SSMS and go back in..

Also
---------------
When I run

select dbo.fnFormerBorrower(1,2)

I am also getting invalid object name from referencing a valid object in the function:

-------------
Msg 208, Level 16, State 1, Procedure fnFormerBorrower, Line 10
Invalid object name 'dbo.transactions'.

(1 row(s) affected)

What the heck?

The schema might not be 'dbo' -- I used that because most people use it.

You could try removing all the 'dbo.' from the table names, ALTERing the function, and try again.

I took out the dbo and got the same:

Msg 2714, Level 16, State 5, Procedure fnFormerBorrower, Line 10
There is already an object named 'fnFormerBorrower' in the database.

To correct that error, change "CREATE FUNCTION" to "ALTER FUNCTION".

I figured out the issue that I created.  I did create the function on the wrong server. (via selecting in SSMS "File / Create New query with existing connection")

Found it, deleted it.

So now I am back on track, sort of.

Now .... It is not recognizing an alias for some reason..

Error is:
-------------------------
Msg 107, Level 16, State 2, Procedure fnFormerBorrower, Line 10
The column prefix 'c' does not match with a table name or alias name used in the query.


--------------------------------the code again
USE [thedatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[fnFormerBorrower]    Script Date: 09/03/2013 16:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnFormerBorrower]
(
    @branchid int,
    @namesid int
   
)
RETURNS char(1)
AS
BEGIN
RETURN (
   CASE WHEN EXISTS(
       select count (*) --top (1) 1
      from dbo.contracts c
     
      inner join dbo.names n
      on c.branchid = n.branchid
      and c.namesid = n.namesid

      inner join dbo.vwBcontrol v
      on v.branchid = c.branchid

      where (c.namesid = @namesid
                  and c.branchid = @branchid
                  and c.processstatus = 50 --former borrower
                  and c.loanamt > 0)
                         
                  and not exists
                        (select contrnumber from dbo.contracts c2
                        where processstatus not in (3, 45, 47) --open, PandL, NonFile
                        and c2.namesid = c.namesid
                        and c2.branchid = c.branchid
                        and c2.payoutdate = c.loandate)
                  and not exists
                        (select contrid from dbo.transactions t
                        where transtype >= '3000' and transtype < '3999' and c.branchid = t.branchid and c.contrid = t.contrid
                        and t.namesid = c.namesid))
    THEN 'T' ELSE 'F' END
)
END --FUNCTION

Sorry, I don't see any issue, unless there's a typo or a missing alias.

Btw, COUNT(*) is extremely inefficient here, and could force SQL to go thru every row.  Use "SELECT 1" instead, just to be sure SQL doesn't have to do that.

I meant to say I created fuction innitally in wrong database, not server.  Quick google of alias error, returns reults... Have not pinpointed yet. I need to take a fresh look in the morning.. EST

I got rid of the count (*) and replaced "with count top1 Field1".  That line does not generate error.

But I cannot get rid of the alias error:
--------------------------
Msg 107, Level 16, State 2, Procedure fnFormerBorrower, Line 10
The column prefix 'c' does not match with a table name or alias name used in the query.
--------------------------

Google turned up some suggestions, but none seem to apply, unless linked server or remote is issue as in the last 2 links.  I will have to check and see if either one of those could apply to my environment
----------------------------------
http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

http://www.forumtopics.com/busobj/viewtopic.php?t=45200

http://www.sql-server-helper.com/error-messages/msg-107.aspx

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1d23515a-9f5c-4eb8-a1d7-95e998305a37/server-msg-107-the-column-prefix-xx-does-not-match-with-a-table-name-or-alias-name-used-in-the

http://stackoverflow.com/questions/16070117/the-column-prefix-s-does-not-match-with-a-table-name-or-alias-name-used-in-th

http://support.microsoft.com/kb/936223

http://connect.microsoft.com/SQLServer/feedback/details/276254/sql2005-sp2-issue-with-linked-server-sql2000-and-correlated-subquery-in-where-clause

Odd to me that the same select will work when used in a select statement, not in a function.  Below is exact same select statement, including aliases, with parms hard coded.  It returns the top namesid field.  

Any ideas?

---------------script works in a query - not in function:
use mydatabase
declare
@branchid                  int,
@namesid                  int
set                              @branchid = 3208
set                              @namesid = 7619
 (  
      select top 1 c.namesid
      from contracts c
      
      inner join names n
      on c.branchid = n.branchid
      and c.namesid = n.namesid

      inner join vwBcontrol v
      on v.branchid = c.branchid
 
                         where (c.namesid = @namesid
                  and c.branchid = @branchid
                  and c.processstatus = 50 --former borrower
                  and c.loanamt > 0)
                         
                  and not exists
                        (select c2.contrnumber from contracts c2
                        where c2.processstatus not in (3, 45, 47) --open, PandL, NonFile
                        and c2.namesid = c.namesid
                        and c2.branchid = c.branchid
                        and c2.payoutdate = c.loandate)
                       
                  and not exists
                        (select t.contrid from transactions t
                        where t.transtype >= '3000' and t.transtype < '3999'
                                          and c.branchid = t.branchid and c.contrid = t.contrid
                        and t.namesid = c.namesid))

No.

Are you trying to use some type of GUI/"query builder" thing to create the function?  There are restrictions on what is available there.

But if you use a script with ALTER FUNCTION, I don't see what's wrong with the code.

No Gui.

I am scripting it in SSMS.

I have decided to come back to this and try to work your case statement into my main query and turn it into a stored procedure.

i.e,

select
F1,
F2,
Case  When Exists (select top 1 ....) as FormerBorrower,
F3

Can you help with this?

Below is my original query.  I need to add the Former Borrower field based on your case statement.  I have played around with it and cannot get it to work.  I am getting way too many records.

----------------------------
declare
@fromdate            smalldatetime,
@todate                  smalldatetime,
@daysToExclude      int
set                        @daysToExclude = 30
set                        @todate = convert(varchar, GetDate()-@daysToExclude, 110)
set                        @fromdate = convert(varchar, dateadd(year, -3, GETDATE()) + 1, 110) --- @daysToExclude -- today less 3 yrs + 1 day formated mm-dd-yyyy --'7/1/2013'  

 (select  
      c.BranchId,
    c.namesid,
      c.contrid,
 
      convert(varchar, c.LoanDate,110) as LoanDate,
      datename(mm, c.LoanDate) as LoanMonth,
      datepart(mm, c.LoanDate) as LoanMoNum,
      datepart(yyyy, c.LoanDate) as LoanYear,
      c.PayoutDate,
      c.Processstatus,
      c.TotalNote,
    v.state,
      v.br_name as branch,
    v.sup_name as [Supervisor],

     --Add formerborrower  field:
     Case  When Exists (select top 1 ....) as FormerBorrower,
   

      from contracts c
      
      inner join names n
      on c.branchid = n.branchid
      and c.namesid = n.namesid

      inner join vwBcontrol v
      on v.branchid = c.branchid

        where (loandate >= @fromdate and loandate <= @todate)
        and (processstatus = 50) --former borrower
        and (loanamt > 0)
        and not exists
                  (select contrnumber from contracts c2
                  where processstatus not in (3, 45, 47) --open, PandL, NonFile
                  and c2.namesid = c.namesid
                  and c2.branchid = c.branchid
                  and c2.payoutdate = c.loandate)
        and not exists
                  (select contrid from transactions t
                  where transtype >= '3000' and transtype < '3999' and c.branchid = t.branchid and c.contrid = t.contrid
                  and t.namesid = c.namesid))

order by c.branchid, n.namesid