Can I use Crystal to call a view that calls an SQL function  
Author Message
ThomasDUBALE





PostPosted: Top

SQL Server >> Can I use Crystal to call a view that calls an SQL function

Here is the View - Our ERP cannot get their queries right. We are trying to
get commissions while they come up with a fix. In order to get Crystal to
open the table, I created it inside a function. I was hoping to avoid a
##temptable. Crystal is doing something weird though and gives warning
messages about multiple starting points. I am clueless. Here are the
queries: (This is from a MACOLA ES SQL DB if you have familiarity with this
product.) I think it may be a Crystal problem and not an SQL problem. Any
ideas?

VIEW CALLED FROM CRYSTAL IS:
SELECT CommissionInvoices.*
FROM dbo.CommissionInvoices('08-01-2004', '08-31-2004')
CommissionInvoices

FUNCTION IS AS FOLLOWS:



(
inv_no varchar(8) NULL,
cus_no varchar(20) NULL,
DebtorName varchar(50) NULL
)
AS
--DROP FUNCTION CommissionInvoices
--THIS FUNCTION PULLS ANY INVOICE WHICH BECOMES FULLY PAID DURING THE

-- Sample call:
--SELECT * FROM dbo.CommissionInvoices ('08-01-2004','08-31-2004')
-- another sample call
-- SELECT * FROM dbo.CommissionInvoices ('08-01-2004','08-31-2004') ORDER BY
Debtor

BEGIN

DECLARE
@Invoice varchar(30), -- stores the invoice number which is only 8 chars
in size... large field size used to handleother items in this column
@Debtor varchar(20),
@SQL nvarchar(400),
@DepositDate datetime,
@matchID varchar(9), -- stores an integer
@MaxID int,
@MinID int,
@sMin varchar(9), -- stores an integer
@sMax varchar(9), -- stores an integer
@AlreadyInvoiced varchar(30),
@AlreadyDebtor varchar(6),
@DebtorName varchar(50),
@MaxMatch int

--Find the highest and lowest ID entered during the time between the two
given dates:


null and not invoicenumber is null


null and not invoicenumber is null

-- convert to string



-- declare a cursor to grab all the invoices
-- (group by debtor number in case of duplicate invoice numbers from diff
debtor)
DECLARE INVOICES CURSOR FOR
SELECT InvoiceNumber, DebtorNumber from BankTransactions
WHERE Type='W' AND TransactionType='K'
AND NOT InvoiceNumber is null and not DebtorNumber is null
GROUP BY InvoiceNumber,Debtornumber

-- open the cursor
OPEN INVOICES

FETCH NEXT FROM INVOICES



BEGIN


-- Loop through cursor for matchid that is not null - if one is null, toss
the whole group out
-- Similarly only select matchid that is between high and low id above

-- IF matchid is GT the high, throw out the entire group for this invoice
-- The remainder wins - RETURN THESE INVOICE AND DEBTOR VALUES
DECLARE MATCHID_CHECK CURSOR FOR


OPEN MATCHID_CHECK



BEGIN





check this !!!


END --Matchid_check


--Grab the name of the debtor for clarification - this line is
optional







Outahere:
CLOSE MATCHID_CHECK
DEALLOCATE MATCHID_CHECK

END --Invoices
CLOSE INVOICES
DEALLOCATE INVOICES
RETURN
END

--
Regards,
Jamie

SQL Server240