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 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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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,...
|
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...
|
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...
|
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
...
|
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
--------------------------------------------------------------------------------
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |