Reformatting ResultSet

I'm using ADO.Net to return a set of records from a SQL Server, which I
then process to create a text stream ultimately used as the InnerHTML
property of a browser control in a C# project. Please don't ask why I'm
doing it that way. I know it's stupid, but customer requirements dictate
that it be done that way. Here's my question. Assume I get the following
return from the SQL query:
KeyType ILS KeyVal 1 <ILS F1>Somevalue</ILS F2><ILS F2>Somevalue</ILS
F2>...
KeyType PARTS KeyVal 2 <PARTS F1>Somevalue</PARTS F1><PARTS
F2>Somevalue</PARTS F2>...
KeyType ILS KeyVal 3 <ILS F1>Somevalue</ILS F2><ILS
F2>SomeSpecificvalue</ILS F2>...
After processing, this yields:
<ILS Records>
<ILS>KeyVal 1
<ILS F1>Somevalue</ILS F1>
<ILS F2>Somevalue</ILS F2>
</ILS>
<ILS match='true'>KeyVal 3
<ILS F1>Somevalue</ILS F1>
<ILS F2><b>SomeSpecificvalue</b></ILS F2>
</ILS>
</ILS Records>
<PARTS Records>
<PARTS>KeyVal 2
<PARTS F1>Somevalue</PARTS F1>
<PARTS F2>Somevalue</PARTS F2>
</PARTS>
</PARTS Records>
The result above is funneled through an XML stylesheet, which renders
exactly the way the customer wants it to. There are a whole lot of ifs,
ands, and buts associated with how each record is formatted, its weight, its
type, its priority, query parameters, etc.. The SQL Server returns the
result set into the datareader object very quickly. Does it make sense to
put the application specific formatting logic into stored procedures, or
would it make more sense to leave the record level formatting in the C#
code?> Does it make sense to
> put the application specific formatting logic into stored procedures, or
> would it make more sense to leave the record level formatting in the C#
> code?
I'd leave it in c# code. TSQL isn't the best language in the world for these
types of things. I
think that your code will be more readable, manageable and efficient as c# c
ode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"bigbob" <big@.bob.com> wrote in message news:eAn$3a9AFHA.1400@.TK2MSFTNGP11.phx.gbl...[color
=darkred]
> I'm using ADO.Net to return a set of records from a SQL Server, which I
> then process to create a text stream ultimately used as the InnerHTML
> property of a browser control in a C# project. Please don't ask why I'm
> doing it that way. I know it's stupid, but customer requirements dictate
> that it be done that way. Here's my question. Assume I get the following
> return from the SQL query:
> KeyType ILS KeyVal 1 <ILS F1>Somevalue</ILS F2><ILS F2>Somevalue</ILS
> F2>...
> KeyType PARTS KeyVal 2 <PARTS F1>Somevalue</PARTS F1><PARTS
> F2>Somevalue</PARTS F2>...
> KeyType ILS KeyVal 3 <ILS F1>Somevalue</ILS F2><ILS
> F2>SomeSpecificvalue</ILS F2>...
> After processing, this yields:
> <ILS Records>
> <ILS>KeyVal 1
> <ILS F1>Somevalue</ILS F1>
> <ILS F2>Somevalue</ILS F2>
> </ILS>
> <ILS match='true'>KeyVal 3
> <ILS F1>Somevalue</ILS F1>
> <ILS F2><b>SomeSpecificvalue</b></ILS F2>
> </ILS>
> </ILS Records>
> <PARTS Records>
> <PARTS>KeyVal 2
> <PARTS F1>Somevalue</PARTS F1>
> <PARTS F2>Somevalue</PARTS F2>
> </PARTS>
> </PARTS Records>
> The result above is funneled through an XML stylesheet, which renders
> exactly the way the customer wants it to. There are a whole lot of ifs,
> ands, and buts associated with how each record is formatted, its weight, i
ts
> type, its priority, query parameters, etc.. The SQL Server returns the
> result set into the datareader object very quickly. Does it make sense to
> put the application specific formatting logic into stored procedures, or
> would it make more sense to leave the record level formatting in the C#
> code?
>[/color]|||Thanks, Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%232FLMK%23AFHA.1188@.tk2msftngp13.phx.gbl...
> I'd leave it in c# code. TSQL isn't the best language in the world for
these types of things. I
> think that your code will be more readable, manageable and efficient as c#
code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "bigbob" <big@.bob.com> wrote in message
news:eAn$3a9AFHA.1400@.TK2MSFTNGP11.phx.gbl...
its
to
>

Reformatting Parameters before using them in Sql query

Hello,
We use Reporting Services to do reports from a (rather old iSeries /
AS400) database. Date values are stored as numeric data in the format
YYYYMMDD, rather than with a proper "Date" type. This is quite common in
older host-based databases.
In one of our reports, the user should be able to select data by
entering date-from and date-to into parameters, before starting the report.
All that we are able to do right now is to let the user enter the data
in the original YYYYMMDD format, in order to be able to use it in a
parametrized query. What I want to do, is:
a) Let the user enter a date as a parameter in a proper Date format
b) Before executing the query, convert this parameter to YYYYMMDD number
format
I guess this conversion should be done in a custom assembly. However,
there doesn't seem to be a "plug" where I could attach this conversion
code. The only thing I found is to use Generic Query - but this would
really be last resort, because I think it is very ugly and not really
RAD, to lose all of the graphic helpers just because of the date
conversions.
Any help would be greatly appreciated.
Ursuse the following custom code:
Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
End Function 'ConvertToYYYYMMDD
Then in your query you would use for your parameter portion:
code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
"Urs Eichmann" <xx@.yy.ch> wrote in message
news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> We use Reporting Services to do reports from a (rather old iSeries /
> AS400) database. Date values are stored as numeric data in the format
> YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> older host-based databases.
> In one of our reports, the user should be able to select data by
> entering date-from and date-to into parameters, before starting the
report.
> All that we are able to do right now is to let the user enter the data
> in the original YYYYMMDD format, in order to be able to use it in a
> parametrized query. What I want to do, is:
> a) Let the user enter a date as a parameter in a proper Date format
> b) Before executing the query, convert this parameter to YYYYMMDD number
> format
> I guess this conversion should be done in a custom assembly. However,
> there doesn't seem to be a "plug" where I could attach this conversion
> code. The only thing I found is to use Generic Query - but this would
> really be last resort, because I think it is very ugly and not really
> RAD, to lose all of the graphic helpers just because of the date
> conversions.
> Any help would be greatly appreciated.
> Urs|||Thanks mike, but AFAIK I can only insert "code.convertto..." into my
query if I don't use the Graphic Query designer and instead use the
Generic Query Designer, which I don't want to (see my first message).
Urs
mike wrote:
> use the following custom code:
> Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> End Function 'ConvertToYYYYMMDD
> Then in your query you would use for your parameter portion:
> code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
>
> "Urs Eichmann" <xx@.yy.ch> wrote in message
> news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
>>Hello,
>>We use Reporting Services to do reports from a (rather old iSeries /
>>AS400) database. Date values are stored as numeric data in the format
>>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
>>older host-based databases.
>>In one of our reports, the user should be able to select data by
>>entering date-from and date-to into parameters, before starting the
> report.
>>All that we are able to do right now is to let the user enter the data
>>in the original YYYYMMDD format, in order to be able to use it in a
>>parametrized query. What I want to do, is:
>>a) Let the user enter a date as a parameter in a proper Date format
>>b) Before executing the query, convert this parameter to YYYYMMDD number
>>format
>>I guess this conversion should be done in a custom assembly. However,
>>there doesn't seem to be a "plug" where I could attach this conversion
>>code. The only thing I found is to use Generic Query - but this would
>>really be last resort, because I think it is very ugly and not really
>>RAD, to lose all of the graphic helpers just because of the date
>>conversions.
>>Any help would be greatly appreciated.
>>Urs
>
>|||Urs,
Did you ever find a work around. I am in the same boat.
Thanks, Eric
"Urs Eichmann" wrote:
> Thanks mike, but AFAIK I can only insert "code.convertto..." into my
> query if I don't use the Graphic Query designer and instead use the
> Generic Query Designer, which I don't want to (see my first message).
> Urs
>
> mike wrote:
> > use the following custom code:
> >
> > Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> > ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> > End Function 'ConvertToYYYYMMDD
> >
> > Then in your query you would use for your parameter portion:
> > code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
> >
> >
> >
> > "Urs Eichmann" <xx@.yy.ch> wrote in message
> > news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> >
> >>Hello,
> >>We use Reporting Services to do reports from a (rather old iSeries /
> >>AS400) database. Date values are stored as numeric data in the format
> >>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> >>older host-based databases.
> >>
> >>In one of our reports, the user should be able to select data by
> >>entering date-from and date-to into parameters, before starting the
> >
> > report.
> >
> >>All that we are able to do right now is to let the user enter the data
> >>in the original YYYYMMDD format, in order to be able to use it in a
> >>parametrized query. What I want to do, is:
> >>
> >>a) Let the user enter a date as a parameter in a proper Date format
> >>b) Before executing the query, convert this parameter to YYYYMMDD number
> >>format
> >>
> >>I guess this conversion should be done in a custom assembly. However,
> >>there doesn't seem to be a "plug" where I could attach this conversion
> >>code. The only thing I found is to use Generic Query - but this would
> >>really be last resort, because I think it is very ugly and not really
> >>RAD, to lose all of the graphic helpers just because of the date
> >>conversions.
> >>
> >>Any help would be greatly appreciated.
> >>
> >>Urs
> >
> >
> >
>sql

Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug

reformatting DATE

I currently have a date column that draws from the now() function in my
asp script. The date looks like this:

2/5/2004 10:09:52 AM

As you can see, there is the day and the time data all in the same
string.

What I would like to do is run a group by clause that groups
all the dates that are on the same day togeather. But how do I seperate
this data in SQL? I know there are VB functions that can do this, but in
order for the group function to work when running a sql statement, I
have to do it in SQL itself. Does anyone know how this is done?

Thank you!

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:4023cc79$0$196$75868355@.news.frii.net...
> I currently have a date column that draws from the now() function in my
> asp script. The date looks like this:
> 2/5/2004 10:09:52 AM
> As you can see, there is the day and the time data all in the same
> string.
> What I would like to do is run a group by clause that groups
> all the dates that are on the same day togeather. But how do I seperate
> this data in SQL? I know there are VB functions that can do this, but in
> order for the group function to work when running a sql statement, I
> have to do it in SQL itself. Does anyone know how this is done?
> Thank you!
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I'm not completely sure what you're looking for, but perhaps something like
this?

select convert(char(8), DateColumn, 112) as 'Day', sum(SomeColumn) as
'Total'
from dbo.MyTable
group by convert(char(8), DateColumn, 112)

Simon