Avatar of Dee
DeeFlag for United States of America asked on

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))
Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Dee

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Dee

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
Scott Pletcher

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.
ASKER
Dee

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Dee

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?
Scott Pletcher

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.
ASKER
Dee

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

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

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
Scott Pletcher

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Dee

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
ASKER
Dee

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))
Scott Pletcher

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Dee

No Gui.

I am scripting it in SSMS.
ASKER
Dee

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