Help coverting a stored procedure to a view

Can someone help me convert this stored procedure to a view? It is using two UDFs.

I appreciate this very much!

@.Startdatetime, @.End datetime ASSELECTC.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,dbo.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel, dbo.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description FROMtblClients CINNERJOIN tblPMTs PON C.Client_ID = P.Client_IDINNERJOIN tblPMTReceipts PRON P.PMT_ID = PR.PMT_IDINNERJOINtblClientAddresses AON C.Client_ID = A.Client_IDLEFTOUTER JOINtblSoftCreditsPMTS SCON C.Client_ID = SC.SC_Client_IDWHERE(PR.PaymentDateBETWEEN @.StartAND @.End)GROUPBY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.TitleORDERBY pmtLevelRETURN

Hi,

That stored procedure cannot be converted into a View because of the the parameters in the WHERE clause and the fact that there is a Group By that hides the PaymentDate from the results.

SQL Views do not support parameters. Because the PaymentDate is not part of the output the users of your View would not be able to provide filter by PaymentDate.

The alternative is to convert it to a SQL function that returns a table:

CREATE FUNCTION dbo.MyFunction(@.Startdatetime,
@.End datetime)

RETURNS TABLE
AS
RETURN

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description
FROM

tblClients CINNERJOIN
tblPMTs P

ON C.Client_ID = P.Client_IDINNERJOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_IDINNERJOIN
tblClientAddresses A

ON C.Client_ID = A.Client_IDLEFTOUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDateBETWEEN @.StartAND @.End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

This would allow you to pass parameters to the function from a SELECT statement a get a result set without the need to run a stored procedure.

Example of how you would use this function:

SELECT * FROM dbo.MyFunction('1/1/2007','6/1/2007')

Hope this helps!

David

|||

Thank you for sharing with me. I don't think I will be able to use the Table Valued Function with the reporting tool I have. It only works with tables and views.

|||

What is your reporting tool?

Help - With Script

I am a sql novice and would appreciate any help with the following problem.

In a table I have property addresses stored in 6 fields. Field6 always hold
the Post Code. However, fields 4 and 5 are sometime NULL. Using the
desktop integration package we have which interfaces with MS Word when
printing an address in a letter the end results often end up looking like
this.

1 Any Street
AnyTown
AnyCounty
"Null"
"Null"
PostCode

It is not a normal Mail merge so it is not possible to use the functionality
available within MS Word to not print empty fields. Therefore I need to do
a check within SQL on the null field so that when I pass the values which
are printed as fields within MS Word the variables created by the SELECT
statement are passed over like this

1 Any Street
AnyTown
Anycounty
PostCode
"Null"
"Null"

So in brief I guess what I am after is a script which as it passes the
values in fields 1-6 to variable 1-6 it always ensures that the field
containing values end up in the first variables and the remaining variable
are left as Null.

I hope this explanation is not too confusing.

Thanks

David
--

David M Loraine

life is a holiday from eternity - eternity is a long time - so enjoy your
life !!

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004Do you have access to the SQL that generates your return results?

If so you could use the ISNULL() function (not sure if this is DB specific,
I know it works with MS SQL).

So you could do something like this:

SELECT
ISNULL ( Street, '' ),
ISNULL ( Town, '' ),
ISNULL ( County, ''),
ISNULL ( PostalCode, '' ),
ISNULL ( FieldX, '' )
ISNULL ( FieldY, '' )
FROM User_Addresses

Basically the server checks each value as it comes out of the database to
see if its Null, if it is it replaces the null value with whatever is in the
quotes. In my example the null value is simply replaced with an empty
string.

Hope this help.
</Muhd
"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> I am a sql novice and would appreciate any help with the following
problem.
> In a table I have property addresses stored in 6 fields. Field6 always
hold
> the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> desktop integration package we have which interfaces with MS Word when
> printing an address in a letter the end results often end up looking like
> this.
> 1 Any Street
> AnyTown
> AnyCounty
> "Null"
> "Null"
> PostCode
> It is not a normal Mail merge so it is not possible to use the
functionality
> available within MS Word to not print empty fields. Therefore I need to
do
> a check within SQL on the null field so that when I pass the values which
> are printed as fields within MS Word the variables created by the SELECT
> statement are passed over like this
>
> 1 Any Street
> AnyTown
> Anycounty
> PostCode
> "Null"
> "Null"
> So in brief I guess what I am after is a script which as it passes the
> values in fields 1-6 to variable 1-6 it always ensures that the field
> containing values end up in the first variables and the remaining variable
> are left as Null.
> I hope this explanation is not too confusing.
> Thanks
> David
> --
> David M Loraine
> life is a holiday from eternity - eternity is a long time - so enjoy your
> life !!
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Hi David

You can use something like the following. I had created a table called
'Address' with fields 'Address1', 'Address2', 'City', 'Postcode'. You can
remove the PRINTs. I let them stay in, in case you want to run it in Query
Analyzer for debugging.

DECLARE
@.Address1 varchar(50),
@.Address2 varchar(50),
@.City varchar(50),
@.Postcode varchar(50)

DECLARE Address_Cursor CURSOR
FOR SELECT Address1, Address2, City, Postcode FROM Address

OPEN Address_Cursor
FETCH NEXT FROM Address_Cursor
INTO @.Address1, @.Address2, @.City, @.Postcode

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.Address1 IS NULL) OR (@.Address1 = '')
BEGIN
SET @.Address1 = @.Address2
SET @.Address2 = @.City
SET @.City = @.Postcode
SET @.Postcode = ''
END

IF (@.Address2 IS NULL) OR (@.Address2 = '')
BEGIN
SET @.Address2 = @.City
SET @.City = @.Postcode
SET @.Postcode = ''
END

IF (@.City IS NULL) OR (@.City = '')
BEGIN
SET @.City = @.Postcode
SET @.Postcode = ''
END

PRINT @.Address1
PRINT @.Address2
PRINT @.City
PRINT @.Postcode
PRINT '----------'

FETCH NEXT FROM Address_Cursor
INTO @.Address1, @.Address2, @.City, @.Postcode
END

CLOSE Address_Cursor
DEALLOCATE Address_Cursor

"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> I am a sql novice and would appreciate any help with the following
problem.
> In a table I have property addresses stored in 6 fields. Field6 always
hold
> the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> desktop integration package we have which interfaces with MS Word when
> printing an address in a letter the end results often end up looking like
> this.
> 1 Any Street
> AnyTown
> AnyCounty
> "Null"
> "Null"
> PostCode
> It is not a normal Mail merge so it is not possible to use the
functionality
> available within MS Word to not print empty fields. Therefore I need to
do
> a check within SQL on the null field so that when I pass the values which
> are printed as fields within MS Word the variables created by the SELECT
> statement are passed over like this
>
> 1 Any Street
> AnyTown
> Anycounty
> PostCode
> "Null"
> "Null"
> So in brief I guess what I am after is a script which as it passes the
> values in fields 1-6 to variable 1-6 it always ensures that the field
> containing values end up in the first variables and the remaining variable
> are left as Null.
> I hope this explanation is not too confusing.
> Thanks
> David
> --
> David M Loraine
> life is a holiday from eternity - eternity is a long time - so enjoy your
> life !!
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Here is script in question, although it is really just the select part that
needs the work on it I believe.

The variables par_adr_line1 etc are passed to MS Word to form the address
which is printed in the letters, field 6 always holds the the post code and
as you can see it is always formatted to be in uppercase.

Frequently though fields 4 and 5 are null and consequently when the address
is printed it looks a little untidy as there is a large gap between the last
address line and the post code. What I need to happen is that when a blank
field is found in the dbase the next value down is moved up so that for
example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends up
being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
value in ad.adr_line_6 ends up in par_adr_line5.

I hope this clarifies my enquiry

Select initcap(ad.adr_line_1) par_adr_line1,

initcap(ad.adr_line_2) par_adr_line2,

initcap(ad.adr_line_3) par_adr_line3,

initcap(ad.adr_line_4) par_adr_line4,

initcap(ad.adr_line_5) par_adr_line5,

upper(ad.adr_line_6) par_adr_line6

from tenancy_instances ti,

household_persons ho,

address_usages au,

addresses ad

where ti.tin_tcy_refno = '$tenancy_ref'

and ad.adr_refno = au.aus_adr_refno

and au.aus_aut_fao_code = 'PAR'

and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)

from

address_usages au2

where

au2.aus_par_refno = au.aus_par_refno

and

au2.aus_aut_fao_code = 'PAR'

and sysdate

between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)

and

au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))

and ti.tin_main_tenant_ind = 'Y'

and ti.tin_hop_refno = ho.hop_refno

and ho.hop_par_refno = au.aus_par_refno

and sysdate between au.aus_start_date and nvl(au.aus_end_date, sysdate+1)

"Muhd" <muhd@.binarydemon.com> wrote in message
news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> Do you have access to the SQL that generates your return results?
> If so you could use the ISNULL() function (not sure if this is DB
specific,
> I know it works with MS SQL).
> So you could do something like this:
> SELECT
> ISNULL ( Street, '' ),
> ISNULL ( Town, '' ),
> ISNULL ( County, ''),
> ISNULL ( PostalCode, '' ),
> ISNULL ( FieldX, '' )
> ISNULL ( FieldY, '' )
> FROM User_Addresses
> Basically the server checks each value as it comes out of the database to
> see if its Null, if it is it replaces the null value with whatever is in
the
> quotes. In my example the null value is simply replaced with an empty
> string.
> Hope this help.
> </Muhd>
> "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > I am a sql novice and would appreciate any help with the following
> problem.
> > In a table I have property addresses stored in 6 fields. Field6 always
> hold
> > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > desktop integration package we have which interfaces with MS Word when
> > printing an address in a letter the end results often end up looking
like
> > this.
> > 1 Any Street
> > AnyTown
> > AnyCounty
> > "Null"
> > "Null"
> > PostCode
> > It is not a normal Mail merge so it is not possible to use the
> functionality
> > available within MS Word to not print empty fields. Therefore I need to
> do
> > a check within SQL on the null field so that when I pass the values
which
> > are printed as fields within MS Word the variables created by the SELECT
> > statement are passed over like this
> > 1 Any Street
> > AnyTown
> > Anycounty
> > PostCode
> > "Null"
> > "Null"
> > So in brief I guess what I am after is a script which as it passes the
> > values in fields 1-6 to variable 1-6 it always ensures that the field
> > containing values end up in the first variables and the remaining
variable
> > are left as Null.
> > I hope this explanation is not too confusing.
> > Thanks
> > David
> > --
> > David M Loraine
> > life is a holiday from eternity - eternity is a long time - so enjoy
your
> > life !!
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Here is script in question, although it is really just the select part that
needs the work on it I believe.

The variables par_adr_line1 etc are passed to MS Word to form the address
which is printed in the letters, field 6 always holds the the post code and
as you can see it is always formatted to be in uppercase.

Frequently though fields 4 and 5 are null and consequently when the address
is printed it looks a little untidy as there is a large gap between the last
address line and the post code. What I need to happen is that when a blank
field is found in the dbase the next value down is moved up so that for
example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends up
being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
value in ad.adr_line_6 ends up in par_adr_line5.

I hope this clarifies my enquiry

Select initcap(ad.adr_line_1) par_adr_line1,

initcap(ad.adr_line_2) par_adr_line2,

initcap(ad.adr_line_3) par_adr_line3,

initcap(ad.adr_line_4) par_adr_line4,

initcap(ad.adr_line_5) par_adr_line5,

upper(ad.adr_line_6) par_adr_line6

from tenancy_instances ti,

household_persons ho,

address_usages au,

addresses ad

where ti.tin_tcy_refno = '$tenancy_ref'

and ad.adr_refno = au.aus_adr_refno

and au.aus_aut_fao_code = 'PAR'

and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)

from

address_usages au2

where

au2.aus_par_refno = au.aus_par_refno

and

au2.aus_aut_fao_code = 'PAR'

and sysdate

between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)

and

au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))

and ti.tin_main_tenant_ind = 'Y'

and ti.tin_hop_refno = ho.hop_refno

and ho.hop_par_refno = au.aus_par_refno

and sysdate between au.aus_start_date and nvl(au.aus_end_date, sysdate+1)

"Muhd" <muhd@.binarydemon.com> wrote in message
news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> Do you have access to the SQL that generates your return results?
> If so you could use the ISNULL() function (not sure if this is DB
specific,
> I know it works with MS SQL).
> So you could do something like this:
> SELECT
> ISNULL ( Street, '' ),
> ISNULL ( Town, '' ),
> ISNULL ( County, ''),
> ISNULL ( PostalCode, '' ),
> ISNULL ( FieldX, '' )
> ISNULL ( FieldY, '' )
> FROM User_Addresses
> Basically the server checks each value as it comes out of the database to
> see if its Null, if it is it replaces the null value with whatever is in
the
> quotes. In my example the null value is simply replaced with an empty
> string.
> Hope this help.
> </Muhd>
> "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > I am a sql novice and would appreciate any help with the following
> problem.
> > In a table I have property addresses stored in 6 fields. Field6 always
> hold
> > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > desktop integration package we have which interfaces with MS Word when
> > printing an address in a letter the end results often end up looking
like
> > this.
> > 1 Any Street
> > AnyTown
> > AnyCounty
> > "Null"
> > "Null"
> > PostCode
> > It is not a normal Mail merge so it is not possible to use the
> functionality
> > available within MS Word to not print empty fields. Therefore I need to
> do
> > a check within SQL on the null field so that when I pass the values
which
> > are printed as fields within MS Word the variables created by the SELECT
> > statement are passed over like this
> > 1 Any Street
> > AnyTown
> > Anycounty
> > PostCode
> > "Null"
> > "Null"
> > So in brief I guess what I am after is a script which as it passes the
> > values in fields 1-6 to variable 1-6 it always ensures that the field
> > containing values end up in the first variables and the remaining
variable
> > are left as Null.
> > I hope this explanation is not too confusing.
> > Thanks
> > David
> > --
> > David M Loraine
> > life is a holiday from eternity - eternity is a long time - so enjoy
your
> > life !!
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||By no means am i an expert and god i hope im not stearing you wrong but im
pretty sure you can simply do what i suggested above, to help you out i
changed the first six lines of your script to reflect what i was talking
about in my earlier post. You shouldn't need to change any other parts of
your script. Note that if the value is null in the database intead of
returning "null" your script should now just return blank data (i.e. an
empty string).

Select isnull(initcap(ad.adr_line_1),'')
par_adr_line1,
isnull(initcap(ad.adr_line_2),'') par_adr_line2,
isnull(initcap(ad.adr_line_3),'') par_adr_line3,
isnull(initcap(ad.adr_line_4),'') par_adr_line4,
isnull(initcap(ad.adr_line_5),'') par_adr_line5,
isnull(upper(ad.adr_line_6),'') par_adr_line6

It might not be the "best" way but its "a" way and it should work.
Best,
Muhd.

"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:7HjNb.23$S01.22@.news-binary.blueyonder.co.uk...
> Here is script in question, although it is really just the select part
that
> needs the work on it I believe.
> The variables par_adr_line1 etc are passed to MS Word to form the address
> which is printed in the letters, field 6 always holds the the post code
and
> as you can see it is always formatted to be in uppercase.
> Frequently though fields 4 and 5 are null and consequently when the
address
> is printed it looks a little untidy as there is a large gap between the
last
> address line and the post code. What I need to happen is that when a
blank
> field is found in the dbase the next value down is moved up so that for
> example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends
up
> being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
> value in ad.adr_line_6 ends up in par_adr_line5.
> I hope this clarifies my enquiry
> Select initcap(ad.adr_line_1) par_adr_line1,
> initcap(ad.adr_line_2) par_adr_line2,
> initcap(ad.adr_line_3) par_adr_line3,
> initcap(ad.adr_line_4) par_adr_line4,
> initcap(ad.adr_line_5) par_adr_line5,
> upper(ad.adr_line_6) par_adr_line6
>
> from tenancy_instances ti,
> household_persons ho,
> address_usages au,
> addresses ad
>
> where ti.tin_tcy_refno = '$tenancy_ref'
> and ad.adr_refno = au.aus_adr_refno
> and au.aus_aut_fao_code = 'PAR'
> and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)
> from
> address_usages au2
> where
> au2.aus_par_refno = au.aus_par_refno
> and
> au2.aus_aut_fao_code = 'PAR'
> and sysdate
> between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)
> and
> au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))
> and ti.tin_main_tenant_ind = 'Y'
> and ti.tin_hop_refno = ho.hop_refno
> and ho.hop_par_refno = au.aus_par_refno
> and sysdate between au.aus_start_date and nvl(au.aus_end_date,
sysdate+1)
>
>
>
> "Muhd" <muhd@.binarydemon.com> wrote in message
> news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> > Do you have access to the SQL that generates your return results?
> > If so you could use the ISNULL() function (not sure if this is DB
> specific,
> > I know it works with MS SQL).
> > So you could do something like this:
> > SELECT
> > ISNULL ( Street, '' ),
> > ISNULL ( Town, '' ),
> > ISNULL ( County, ''),
> > ISNULL ( PostalCode, '' ),
> > ISNULL ( FieldX, '' )
> > ISNULL ( FieldY, '' )
> > FROM User_Addresses
> > Basically the server checks each value as it comes out of the database
to
> > see if its Null, if it is it replaces the null value with whatever is in
> the
> > quotes. In my example the null value is simply replaced with an empty
> > string.
> > Hope this help.
> > </Muhd>
> > "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> > news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > > I am a sql novice and would appreciate any help with the following
> > problem.
> > > > In a table I have property addresses stored in 6 fields. Field6
always
> > hold
> > > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > > desktop integration package we have which interfaces with MS Word when
> > > printing an address in a letter the end results often end up looking
> like
> > > this.
> > > > 1 Any Street
> > > AnyTown
> > > AnyCounty
> > > "Null"
> > > "Null"
> > > PostCode
> > > > It is not a normal Mail merge so it is not possible to use the
> > functionality
> > > available within MS Word to not print empty fields. Therefore I need
to
> > do
> > > a check within SQL on the null field so that when I pass the values
> which
> > > are printed as fields within MS Word the variables created by the
SELECT
> > > statement are passed over like this
> > > > > 1 Any Street
> > > AnyTown
> > > Anycounty
> > > PostCode
> > > "Null"
> > > "Null"
> > > > So in brief I guess what I am after is a script which as it passes the
> > > values in fields 1-6 to variable 1-6 it always ensures that the field
> > > containing values end up in the first variables and the remaining
> variable
> > > are left as Null.
> > > > I hope this explanation is not too confusing.
> > > > Thanks
> > > > David
> > > --
> > > > David M Loraine
> > > > life is a holiday from eternity - eternity is a long time - so enjoy
> your
> > > life !!
> > > > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
> > >
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

HELP - Selection of Development Software? Thoughts?

Folks, I have a quick question that I would very much appreciate some
feedback on. We are a not for profit charity organization that has decided
to develop a software in-house to manage our volunteers. We have SQL and
that makes the most sense from a database solution but we have some issues
surrounding the choice of the development language. Some have suggested
100% java while others say Visual Basic. The head of our team has suggested
we do it in Cold Fusion since this will be an internet based application and
I guess I would very much like some feedback on that choice. We have about 5
organizations that we will tie into this system with about 5000 users
logging in once per month.
Any suggestions or comments would be greatly appreciated.

Cheers
WadeFor Web Development, my choice is Codecharge... http://www.codecharge.com/

For desktop applications, I choose Clarion, http://www.softvelocity.com/

Both are very capable.

Tim Morrison

"Wade Eyre" <weyre@.cogeco.ca> wrote in message
news:9vnGb.34812$mV5.1257@.read1.cgocable.net...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and
> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has
suggested
> we do it in Cold Fusion since this will be an internet based application
and
> I guess I would very much like some feedback on that choice. We have about
5
> organizations that we will tie into this system with about 5000 users
> logging in once per month.
> Any suggestions or comments would be greatly appreciated.
> Cheers
> Wade|||"Wade Eyre" <weyre@.cogeco.ca> wrote in message
news:9vnGb.34812$mV5.1257@.read1.cgocable.net...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and
> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has
suggested
> we do it in Cold Fusion since this will be an internet based application
and
> I guess I would very much like some feedback on that choice. We have about
5
> organizations that we will tie into this system with about 5000 users
> logging in once per month.
> Any suggestions or comments would be greatly appreciated.

What are your developers skilled in?

Personally I'd say ASP. It's free and in my experience works better than
CF.

> Cheers
> Wade|||In article <9vnGb.34812$mV5.1257@.read1.cgocable.net>, weyre@.cogeco.ca
says...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and

I hope you bought a CPU license for your SQL server as you can not use a
standard CAL for applications that provide data to internet based
applications. The only exception to this is a SBS 2003 install if I
remember correctly.

A CPU license retails for about $4,999.

> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has suggested
> we do it in Cold Fusion since this will be an internet based application and

Cold Fusion runs on top of IIS, Cold Fusion MX is another beast
entirely.

Since you didn't tell us what the site is for, no technical details, it
will be hard for anyone to tell you what is the best development
platform.

Here are some things to consider -

Have you considered an Off-The-Shelf product?

What languages do your current developers understand?

What is your timeframe for completion?

Did you build a list of requirements and template the user interfaces
and get signoff from the people that will be using the system on a daily
basis - you don't need to know what platform you will use to mock up
some templates to fit the requirements definitions.

What Server platforms are you currently running, supporting, have
experience with?

Are you hosting the application in your facility or external location?

If internal, did you purchase a firewall yet?

Answer all these and we can tell you what would be best.

Without the above, I can say the following:

If your people are MS types, go with .Net, it's as fast as other
platforms.

If you people are not MS types, and your servers are Linux based, go
with a java platform.

I've built large sites to manage people with my coding teams, we've done
every platform known and have no preference for any of them. It all
comes does to what the supporting organization is able to manage, what
the clients people know, and what the client wants to use.

..Net or Java, either one works great. Stay away from CF if you can.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||"Wade Eyre" <weyre@.cogeco.ca> wrote in message news:<9vnGb.34812$mV5.1257@.read1.cgocable.net>...
> Folks, I have a quick question that I would very much appreciate some
> feedback on. We are a not for profit charity organization that has decided
> to develop a software in-house to manage our volunteers. We have SQL and
> that makes the most sense from a database solution but we have some issues
> surrounding the choice of the development language. Some have suggested
> 100% java while others say Visual Basic. The head of our team has suggested
> we do it in Cold Fusion since this will be an internet based application and
> I guess I would very much like some feedback on that choice. We have about 5
> organizations that we will tie into this system with about 5000 users
> logging in once per month.
> Any suggestions or comments would be greatly appreciated.
> Cheers
> Wade

Hi Wade,
5000 users once a month is relatively speaking a small site. I second
Greg's opinion that your developer's skill set really should determine
what language to use. Java, asp, and cf are capable languages. It's
really up to the developer if he's going to make you a cadillac of a
site or an unmanageable mess. In my opinion web developement is still
more art than science. Because the developer needs to be
knowledgeable in sql, java/asp/cf, html, style sheets, javascript and
windows/unix.