December 14, 2004 at 2:30 am
Hi Noel,
Looks very interesting (if a little complex for my head). I'll have a go and report back.
Many thanks
Robert
December 14, 2004 at 3:20 am
I'm a little curious to the flow of assignment of invoices..
Step 3 -- Assign invoice number to each line of result set (multiple lines per invoice)
Step 4 -- Export CSV File for Accounts package (subset of select query)
Step 5 -- Make Select query available to Crystal to produce invoices (subset of select query)
Step 6 -- Post invoices to internal transaction table (subset of select query)
What happens if something breaks between step 5 and step 6? How do you keep invoice id assignements consistent if step 5 produces a report of yet nonexisting invoices, and they never make it into the transaction table? Is there some mechanism in place that then removes the CSV file from step 4 and report from step 5 so as it never has happened?
If not, it looks like a recipe for disaster in my eyes. The entire 'flow of operations' seems backwards.
Normally, what is in the database is the truth. You never produce any output (at least not outside the bounds of a transaction) before that truth is safely written and committed into the system. In general it's much much harder to ensure an ATOMIC operation if you put intermediate results on the filesystem in the middle of the operation. (step 4 and 5 in this case)
So my question is: are all steps 'waterproof'..?
/Kenneth
December 14, 2004 at 8:29 am
I believe NoelD is on the right track with a good way to assign sequential numbers that doesn't use a cursor.
Here's a way to assign sequential numbers to any distinct set of rows. This example assigns sequence numbers (and invoice numbers starting with 99101000) to the distinct names in syscolumns. The same approach will work for your billable project numbers.
SELECT this.name
, count(other.name) AS rank
, 99101000+count(other.name) as invoice_num
FROM (SELECT distinct name FROM syscolumns) AS this
, (SELECT DISTINCT name FROM syscolumns) AS other
WHERE other.name <= this.name
GROUP BY this.name
ORDER BY this.name
The first 6 lines of my result set are:
----------------------------
@Aim_Data_File 1 99101001
@chObjectType 2 99101002
@DC_File 3 99101003
@Fin_iid 4 99101004
@iActionFlag 5 99101005
@id 6 99101006
You can insert the result into a temprorary "current_run_invoice_table"; then use them as you need to.
Bob Monahon
December 14, 2004 at 11:21 am
This sample may help you to play around.
CREATE PROCEDURE dbo.PROV_SRCH
--from the search screen:
@LastName varchar(40),
@FirstName varchar(25),
@spec varchar(40),
@City varchar(30),
@Zip varchar(10),
@CompanyID varchar(10),
@ProvID varchar(20),
@SearchType varchar(10),
@SortBy varchar(50),
@TopRecsStr varchar(30)
AS
Declare @user-id int
Declare @AccessLevel varchar(10)
Declare @DisplayTermProviders char(1)
Declare @TermProvMonths int
Declare @Company_ID varchar(10)
Declare @sql varchar(8000)
Declare @OrderBy varchar(1000)
Declare @APGActive char(1)
Declare @cnt int
set nocount on
CREATE TABLE #PROV_DATA (
company_id varchar(10),
provid varchar(20),
prov_keyid varchar(36),
providername varchar(110),
specdesc varchar(30),
vendorname varchar(50),
phone varchar(20),
fax varchar(20),
city varchar(30),
state varchar(2),
zip varchar(10),
lastname varchar(40),
firstname varchar(25),
mi varchar(25)
)
If LEN(@CompanyID) > 0
begin
Declare Company_Users_Cursor Cursor for
select s.company_id, s.disptermprov, s.termprovmnth, u.accesslvl, u.userid, s.enableapg
from usrprivm u
inner join user_names un on u.userid = un.userno and un.username = current_user
inner join _sysparm s on u.company_id = s.company_id and s.active = 1
where u.company_id = @CompanyID
end
Else
begin
Declare Company_Users_Cursor Cursor for
select s.company_id, s.disptermprov, s.termprovmnth, u.accesslvl, u.userID, s.enableapg
from usrprivm u
inner join user_names un on u.userid = un.userno and un.username = current_user
inner join _sysparm s on u.company_id = s.company_id and s.active = 1
end
Open Company_Users_Cursor
Fetch next from Company_Users_Cursor into @Company_ID, @DisplayTermProviders, @TermProvMonths, @AccessLevel, @user-id, @APGActive
-- Build data for each company:
While (@@FETCH_STATUS = 0)
begin
-- Build the select statement
exec W_BUILD_PROV_SRCH_SQL @DisplayTermProviders, @TermProvMonths, @AccessLevel,
@LastName, @FirstName, @spec, @City, @Zip, @Company_ID, @ProvID, @user-id, @APGActive, @SearchType, @sql output
-- Add the insert clause
Set @sql = 'Insert into #PROV_DATA (company_id, provid, prov_keyid, providername, specdesc, vendorname, phone, fax, city, state, zip, lastname, firstname, mi ) ' + @sql
--select @sql
-- execute the query
EXEC(@Sql)
-- get the next company
Fetch next from Company_Users_Cursor into @Company_ID, @DisplayTermProviders, @TermProvMonths, @AccessLevel, @user-id, @APGActive
end
Close Company_Users_Cursor
Deallocate Company_Users_Cursor
select @topRecsStr = case @topRecsStr when '' then NULL else @topRecsStr end
Select @sql = 'Select ' + isnull('top ' + @TopRecsStr,'') + ' company_id, provid, prov_keyid, providername, specdesc, vendorname, phone, fax, city, state from #PROV_DATA '
If 'SPECIALTY' = @SortBy
Select @OrderBy = 'Order by specdesc asc, lastname asc, firstname asc, mi asc'
Else If 'CITY' = @SortBy
Select @OrderBy = 'Order by city asc, lastname asc, firstname, mi asc '
Else IF 'CITY, SPECIALTY' = @SortBy
Select @OrderBy = 'Order by city asc, specdesc asc, lastname asc, firstname asc, mi asc '
Else IF 'ZIP' = @SortBy
Select @OrderBy = 'Order by zip asc, lastname asc, firstname asc, mi asc'
Else -- NAME or nothing
Select @OrderBy = 'Order by lastname asc, firstname asc, mi asc, specdesc asc'
exec w_log_appevent 6
exec(@Sql)
set nocount off
select @cnt = count(*) from #PROV_DATA
return @cnt
GO
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply