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

I need to covert the following script to a function as follows:

    1) change hard coded branchid and nameid to input parms
    2) return "T" (char) if rows returned, "F" if no rows returned


******
---former borrower function
--********************************************************************************
declare
@branchid                  int,
@namesid                  int
set                              @branchid = 3208
set                              @namesid = 7619

 (select  
      count(*)


      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 >= @startdate and loandate <= @enddate)
                         where (c.namesid = @namesid
                  and c.branchid = @branchid
                  and c.processstatus = 50 --former borrower
                  and c.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 (c2.opendate < @enddate or (c2.opendate > c2.loandate and c2.loandate < @startdate)))
                  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))
Delta7428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
CREATE FUNCTION dbo.function_name (
    @branchid int,
    @namesid int,
    @startdate datetime,
    @enddate datetime
)
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 (c2.opendate < @enddate or (c2.opendate > c2.loandate and c2.loandate < @startdate)))
                  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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Delta7428Author Commented:
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
0
Scott PletcherSenior DBACommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Delta7428Author Commented:
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
0
Delta7428Author Commented:
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?
0
Scott PletcherSenior DBACommented:
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.
0
Delta7428Author Commented:
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.
0
Scott PletcherSenior DBACommented:
To correct that error, change "CREATE FUNCTION" to "ALTER FUNCTION".
0
Delta7428Author Commented:
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
0
Scott PletcherSenior DBACommented:
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.
0
Delta7428Author Commented:
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
0
Delta7428Author Commented:
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))
0
Scott PletcherSenior DBACommented:
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.
0
Delta7428Author Commented:
No Gui.

I am scripting it in SSMS.
0
Delta7428Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.