473,320 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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/2004
Jul 20 '05 #1
5 2460
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" <da**********@hotmail.com> wrote in message
news:hW*************@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

Jul 20 '05 #2
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" <da**********@hotmail.com> wrote in message
news:hW*************@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

Jul 20 '05 #3
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" <mu**@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" <da**********@hotmail.com> wrote in message
news:hW*************@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
Jul 20 '05 #4
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" <mu**@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" <da**********@hotmail.com> wrote in message
news:hW*************@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

Jul 20 '05 #5
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" <da**********@hotmail.com> wrote in message
news:7H*************@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" <mu**@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" <da**********@hotmail.com> wrote in message
news:hW*************@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

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dpackwood | last post by:
Hello, I have two different scripts that do pretty much the same thing. The main perl script is on Windows. It runs and in the middle of it, it then calls out another perl script that then...
6
by: mike | last post by:
Hello, After trying to validate this page for a couple of days now I was wondering if someone might be able to help me out. Below is a list of snippets where I am having the errors. 1. Line 334,...
4
by: Derek | last post by:
I have the following script in a page and it gets an error in IE 6. Says something about an invalid argument but the line number doesn't help since I can't see the javascript code when viewing...
7
by: mike | last post by:
Hello, I am kind of new to this javascript stuff and I am constantly having problems trying to get my webpage validated. I have the following <script>printdate();</script> and when I validate it...
5
by: Craig Keightley | last post by:
Please help, i have attached my page which worksin IE but i cannnot get the drop down menu to fucntion in firefox. Any one have any ideas why? Many Thanks Craig ...
8
by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- ...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
5
by: althafexcel | last post by:
hi everyone Im trying to include an external js in my aspx page under the head tag, it doesn't load or it displays an object expected error whenver the function from the .js is called. Actually...
4
by: mattehz | last post by:
Hey there, I am trying to upload old source files and came across these errors: Warning: Invalid argument supplied for foreach() in /home/mattehz/public_html/acssr/trunk/inc_html.php on line 59...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.