Problem with mailing labels and line feed

I am trying to print mailing labels and suppress optional address lines to eliminate white space while maintaining label alignment. This is what I am trying but it does not work.

I create a function that determines the length of a field (i.e. street) and increments a counter if the length is zero. It also takes a second parameter that detremines whether to reset the counter. I then use the function in the visibility each row of the label table. Example iif(Code.LineLen(Fields!Street.Value)=0,True,False). I place this on the visibility of each row except the last as that is City,State,Zip and is required. On this last row the expession I use is

=Fields!city.Value & ", " & Fields!state.Value & " " & Fields!zip.Value & iif(Code.LineCount<5,StrDup(5-Code.LineCount,vbCRLF),""). What this is intended to do is print a carraige return and line feed for every row that did not print. Instead no line feeds occur. I have verified that the Code.LineCount is indeed calculating correctly (I printed the value). I've removed the conditional to make sure it has no issues but again nothing. For clarification the code block I am using is this.

Public LineCount as Integer=0

Function LineLen(byval LineIn as string,byval IsFirst as boolean) as Integer
if IsFirst=True then

LineCount=1
end if
If len(LineIn)>0 then
LineCount=LineCount+1
end if
LineLen=Len(LineIn)
end Function

So if anyone either knows why the line feeds do not occur or a better way to handle this please let me know. Thanks.

Well I partially figured out this issue. Apparently the StrDup function in .Net does not like carraige returns and line feeds. I wrote my own string dup function and now I get the line feeds.

I would still be interesting in a better way of doing this if anyone knows.

problem with line break from NVARCHAR field

I have data saved in nvarchar field in SQL Server and I am displaying it using <= dbReader["my_field"] but even the three or four paragraphs comes as one paragragh and I don't know know where are my line breaks going?

In SQL Server if you want line breaks and well formatted text you must save your text as word in an image column so you can just get the word file back. There could be other ways but this is easy so I use it you could get other answers. Hope this helps.

|||

but this will kill the database size!!

in ASP 3.0 I was doing the following so what is the simillar command in .NET?

<%= Replace(rs("full_story"), vbCrlf, "<br>") %>

|||You were using that against Access or SQL Server?|||SQL Server|||

I found three solutions all long but may do what you want but not with one line of code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/odc_VSTWrdSql.asp

http://codebetter.com/blogs/john.papa/archive/2005/08/09/130476.aspx

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=151&rl=1

sql

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that type
of case. For example:
__________________________________________
1st Degree Murder 1 0
3rd Degree Murder 1 2
___________________________________________
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID =
c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other case
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and the
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...
Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:

> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that type
> of case. For example:
> __________________________________________
> 1st Degree Murder 1 0
> 3rd Degree Murder 1 2
> ___________________________________________
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is.
> I am doing that as a subquery that looks something like this:
> ----
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER JOIN
> [Case] c ON f.CaseID =
> c.CaseID INNER JOIN
> zPASService ON c.ServiceId
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JOIN
> ----
>
> (ServiceDescription is the code description for the case type. Right now I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part comes
> out to something like this:
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [Case] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
>
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other case
> type. If so, I have found examples on how to do a FULL JOIN for two or more
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>
|||Will do, but first let me ask a more basic question. Can you use a FULL JOIN
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to do
a FULL JOIN with the results of a GROUP BY so I have counts for my case types
with another GROUP BY that will have counts of cases assigned. I just wanted
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Steve,
> It will be good if you also post some DDL, sample data and expected result.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname
|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:

>Will do, but first let me ask a more basic question. Can you use a FULL JOIN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks to all of you for helping me on this one. It is working now. Here is
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =
zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =
RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:

> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that typ
e
of case. For example:
________________________________________
__
1st degree Murder 1 0
3rd degree Murder 1 2
________________________________________
___
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
----
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID =
c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
----
-
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
---
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
---
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other cas
e
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and th
e
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:

> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that t
ype
> of case. For example:
> ________________________________________
__
> 1st degree Murder 1 0
> 3rd degree Murder 1 2
> ________________________________________
___
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is
.
> I am doing that as a subquery that looks something like this:
> ----
--
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER J
OIN
> [Case] c ON f.CaseID
=
> c.CaseID INNER JOIN
> zPASService ON c.Service
Id
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JO
IN
> ----
--
>
> (ServiceDescription is the code description for the case type. Right now
I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part come
s
> out to something like this:
> ---
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [C
ase] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
> ---
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other c
ase
> type. If so, I have found examples on how to do a FULL JOIN for two or mo
re
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked a
t
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and
the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>|||Will do, but first let me ask a more basic question. Can you use a FULL JOI
N
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to d
o
a FULL JOIN with the results of a GROUP BY so I have counts for my case type
s
with another GROUP BY that will have counts of cases assigned. I just wante
d
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Steve,
> It will be good if you also post some DDL, sample data and expected result
.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
>|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:

>Will do, but first let me ask a more basic question. Can you use a FULL JO
IN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all of you for helping me on this one. It is working now. Here i
s
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =
zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =
RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:

> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
>
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that type
of case. For example:
__________________________________________
1st Degree Murder 1 0
3rd Degree Murder 1 2
___________________________________________
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
----
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID = c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
----
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
---
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
---
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other case
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and the
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:
> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that type
> of case. For example:
> __________________________________________
> 1st Degree Murder 1 0
> 3rd Degree Murder 1 2
> ___________________________________________
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is.
> I am doing that as a subquery that looks something like this:
> ----
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER JOIN
> [Case] c ON f.CaseID => c.CaseID INNER JOIN
> zPASService ON c.ServiceId
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JOIN
> ----
>
> (ServiceDescription is the code description for the case type. Right now I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part comes
> out to something like this:
> ---
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [Case] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
> ---
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other case
> type. If so, I have found examples on how to do a FULL JOIN for two or more
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>|||Will do, but first let me ask a more basic question. Can you use a FULL JOIN
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to do
a FULL JOIN with the results of a GROUP BY so I have counts for my case types
with another GROUP BY that will have counts of cases assigned. I just wanted
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
> Steve,
> It will be good if you also post some DDL, sample data and expected result.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
> > I need to write a stored proc for a report. Each line of the report will
> > have a description of the type of case and then the number of cases opened
> > during the time period for that type of case followed by the third column
> > which will be the number of cases closed during the time period for that type
> > of case. For example:
> > __________________________________________
> > 1st Degree Murder 1 0
> > 3rd Degree Murder 1 2
> > ___________________________________________
> >
> > To get at these data I need to got into our assignment table to find the
> > first date the case was assigned and then find out the type of cases it is.
> > I am doing that as a subquery that looks something like this:
> > ----
> > SELECT zPASService.ServiceDescription,
> > COUNT(zPASService.ServiceDescription) AS OpenedCases
> > FROM [#FirstAssignedData] f INNER JOIN
> > [Case] c ON f.CaseID => > c.CaseID INNER JOIN
> > zPASService ON c.ServiceId
> > = zPASService.ServiceId
> > WHERE FirstAssignedDt > '5 / 1 / 04'
> > GROUP BY zPASService.ServiceDescription
> > ORDER BY zPASService.ServiceDescription FULL JOIN
> > ----
> >
> >
> > (ServiceDescription is the code description for the case type. Right now I
> > have hardcoded the search for assignments to be any greater that 5/1/04).
> >
> > Next I have to look to see if there are any cases with disposition dates
> > within the time period. Again I have hardcoded that test. That part comes
> > out to something like this:
> > ---
> > SELECT
> > zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> > DispositionedCases, OpenedCases
> > FROM [Case] c
> > INNER JOIN
> >
> > zPASService ON c.ServiceId = zPASService.ServiceId
> > WHERE
> > DispositionDt > '8 / 1 / 05'
> > GROUP BY
> > zPASService.ServiceDescription
> >
> > ---
> >
> > My question is how to bring these two results together? I am thinking I
> > want to do a FULL JOIN since I can't be sure that the case type that is in
> > either the assigned results or the dispositioned results is in the other case
> > type. If so, I have found examples on how to do a FULL JOIN for two or more
> > tables, but can't see how to do it when dealing with results from two
> > queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> > UNION but since my columns are not the same (the first query returns the
> > number of cases assigned the second the number of cases dispositioned and the
> > report needs to get those two numbers seperately) I thougth I needed
> > something else.
> >
> > Thanks...
> > - Steve
> >
> > Thanks...
> >|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
>Will do, but first let me ask a more basic question. Can you use a FULL JOIN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all of you for helping me on this one. It is working now. Here is
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:
> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
> >Will do, but first let me ask a more basic question. Can you use a FULL JOIN
> >with the result of a query or does the subject of the JOIN have to be a
> >table?
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with formatting with CR and LF in a string

Hi,

I'm trying to use carriage return (CR) and line feed (LF) to format a string for use with the msdb.dbo.sp_send_dbmail stored procedure. My goal is to have several lines of text delineated with CR LF. However, it appears that SQL Server 2005 is replacing the CR LF with 2 spaces. The snipped below demonstrates this.

declare @.msg nVarChar(100)
set @.msg = 'this is before the cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'and this is after the cr'
select @.msg

If I copy the text returned by the select into a hex editor what I see for the portion of the string "cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'a" is:

63 72 20 20 0B 0C 20 0E 0F 61

I was expecting:

63 72 20 0A 0B 0C 0D 0E 20 61

This is what leads me to believe the CR and LF are being replaced with spaces as it shows hex 20 (SPACE) instead of hex 0A (LF) and hex 0D (CR).

Can someone explain to me how to make this do what I want it to?

Thanks
John

Given this simple test:

CREATE TABLE dbo.CRLF(id int,crlf nvarchar(100))

DECLARE @.msg nVarChar(100)

SET @.msg = 'this is before the cr ' + char(10)+ char(13) +'and this is after the cr'

INSERT INTO dbo.CRLF(id,crlf)VALUES(1, @.msg )

SELECT * FROM dbo.CRLF

I get this if I copy the results from Query Editor:

idcrlf

1this is before the crand this is after the cr

And this ouput put if I Open Table in Object Explorer:

1this is before the cr

and this is after the cr

|||I'm unable to duplicate your results. I get a single line when opening the table in the Object Explorer.

If you add a DECLARE @.LongMsg and set @.LongMsg = @.Msg + @.Msg + @.Msg and then use @.LongMsg as the @.body parameter of the msdb.dbo.sp_send_dbmail your email will be a single long line with no CR LF showing in the text. This is basically what I'm trying to do.

Perhaps there is a server setting somewhere that needs to be tweaked on our server.

John|||

Ya it true but you see 2 box kind of symbol when you opened

the table trough Object explore, select the option “Result to Text” in the

Query panel and select the row then output shows like this.

(1 row(s)

affected)

idcrlf

--

1This is 1 msg

and this is

2 msg

this is 3

msg

(1 row(s)

affected)

|||Ok, after changing the option I can see the CR in the results. Thanks for showing me something I didn't know.

However, I'm still having problems with the CR in the email text. Here's another snippit from the stored procedure:

Declare @.Message varchar(200)
Declare @.MessageList varChar(max)

SET @.MessageList = 'Daily Summary Report' + @.CR
SET @.MessageList = @.MessageList+'New Items'+ @.CR
SET @.MessageList = @.MessageList+'--'+ @.CR
DECLARE c2 CURSOR FOR
select MSG from Email.Messages
where Event_ID = @.EventID
OPEN c2
FETCH NEXT FROM c2
INTO @.Message
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.MessageList = @.MessageList + @.Message + @.CR
FETCH NEXT FROM c2
INTO @.Message
END
CLOSE c2
DEALLOCATE c2

This builds the body text of the email in the variable @.MessageList. It always puts a CR at the end of the line for the SET @.MessageList lines that are outside of the loop.

Inside the loop, if the message type has messages like "Added UPC 123456123456" it works fine with a CR at the end of every line.

If the message type has messages like "FAILED Client1 20050122 Source TYPE 2: 12 COLUMNS SEMI COLON DELIMITED production" it does not put a CR at the end of each line. If I put CR twice at the end of the line I do get two CR's in the email.

I'm stumped. At this point I have put in an if statement to put 2 CR's at the end of the event types that are not gettting the single CR and that seems to work, but I'm one of those types that like to understand why things work or don't work.

Any more thoughts? And thanks for your help.

John|||

Hmmm, I tried deleting a post I just made in this thread, because it seemed no longer relevant after John had made a simultaneous post, and it appears to have deleted John's new post, too.

I think that is a bug in the FORUM software, since I chose only my post when I selected "Delete".

Sorry!

Dan