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