filter data by row number

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).

SELECT [Patient Identifier], Date, [Operator Index], Time

FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Filter before export

Hi,
I'm in the process of automating the export of a report to a PDF file.
The number of files is enormous and to prevent the query from executing over
and over again I was wondering whether the following scenario is possible.
- Get all records for a given report
- Filter by a specific value and export, repeat process for all necessary
values of that field.
Thanks for any input
Kind regardsYou could definitely do it programmatically from code that calls the RS Web
service...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Thanks for the answer.
Do you have any idea in which Namespace I can find the Filter property?
Thanks
"Wayne Snyder" schreef:
> You could definitely do it programmatically from code that calls the RS Web
> service...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>|||I would use a parameter, passed in via URL. THen use the parameter to do the
grouping...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Wouldn't this trigger the query every time?
I'll try to give some more information.
We have about 1500 offices which all need a bunch of pre generated PDF
reports.
I want a report that returns only a couple rows per office to come back as 1
big Dataset that can be filtered clientside and then exported. So every
'filter' operation extracts the information for a specific office and then
exports it to a PDF. I do not want these queries to execute 1500 times
creating all the connection overhead.
eg.
Report returns 10 records per office
Returns 15000 records in the RS Dataset
The C# program (using Reporting Services WebService) filters out the 10
records and exports these to a PDF for every office
Kind regards,
Wesley
"Wayne Snyder" wrote:
> I would use a parameter, passed in via URL. THen use the parameter to do the
> grouping...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>

Filter and Aggregat function

Hi all,
I am creating a report in BI Dev Studio and use the grouping
functionality to show the number of leads for different sales
representatives. I also use "drill down" to show the leads in detail.
The report also uses a filter to filter out certain time period.
On the sales persons level I use "Rowcount" to show the number of
leads. this works fine as long as I do not use any filter. If I use the
filter, the "Rowcount" function still shows the number of leads for the
whole table, although the sub-group shows the right number of entries.
Where is my mistake?You could place your filter in the WHERE statement under the data tab.
<leebm@.sms.at> wrote in message
news:1156789789.405721.156960@.h48g2000cwc.googlegroups.com...
> Hi all,
> I am creating a report in BI Dev Studio and use the grouping
> functionality to show the number of leads for different sales
> representatives. I also use "drill down" to show the leads in detail.
> The report also uses a filter to filter out certain time period.
> On the sales persons level I use "Rowcount" to show the number of
> leads. this works fine as long as I do not use any filter. If I use the
> filter, the "Rowcount" function still shows the number of leads for the
> whole table, although the sub-group shows the right number of entries.
> Where is my mistake?
>|||Ben Watts schrieb:
> You could place your filter in the WHERE statement under the data tab.
> <leebm@.sms.at> wrote in message
> news:1156789789.405721.156960@.h48g2000cwc.googlegroups.com...
> > Hi all,
> > I am creating a report in BI Dev Studio and use the grouping
> > functionality to show the number of leads for different sales
> > representatives. I also use "drill down" to show the leads in detail.
> > The report also uses a filter to filter out certain time period.
> > On the sales persons level I use "Rowcount" to show the number of
> > leads. this works fine as long as I do not use any filter. If I use the
> > filter, the "Rowcount" function still shows the number of leads for the
> > whole table, although the sub-group shows the right number of entries.
> >
> > Where is my mistake?
> >
Thanks for the answer, but how do I place the filter in the sql
statment exactly? I tried something like: select * from
Adressenherkunft where insertdate = 'Parameters!von.Value' but this
does not work?|||Hi,
The syntax is like
insertdate = @.von
where "von" is the name of the parameter exactly as it shows in the report
parameters dialogbox.
HTH,
Jordi Rambla
MVP SQL Server (Reporting Services)
Solid Quality Learning (http://www.solidqualitylearning.com)
"Markus" <leebm@.sms.at> escribió en el mensaje
news:1156850392.248071.158990@.75g2000cwc.googlegroups.com...
> Ben Watts schrieb:
>> You could place your filter in the WHERE statement under the data tab.
>> <leebm@.sms.at> wrote in message
>> news:1156789789.405721.156960@.h48g2000cwc.googlegroups.com...
>> > Hi all,
>> > I am creating a report in BI Dev Studio and use the grouping
>> > functionality to show the number of leads for different sales
>> > representatives. I also use "drill down" to show the leads in detail.
>> > The report also uses a filter to filter out certain time period.
>> > On the sales persons level I use "Rowcount" to show the number of
>> > leads. this works fine as long as I do not use any filter. If I use the
>> > filter, the "Rowcount" function still shows the number of leads for the
>> > whole table, although the sub-group shows the right number of entries.
>> >
>> > Where is my mistake?
>> >
> Thanks for the answer, but how do I place the filter in the sql
> statment exactly? I tried something like: select * from
> Adressenherkunft where insertdate = 'Parameters!von.Value' but this
> does not work?
>

filling in missing values

I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9To have all the dealers needed:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
RIGHT OUTER join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

To have all the dates, that is something else ...
SQL Server can only base itself on what it finds in the database ...
If there is notking in tblstats (s) for that moment and you get your months from that table it will not work ...
Create an extra table (or an temp table) that will store all months and create an outer join on that table

Filling in missing data for a continous range, e.g. x-axis ..

Hi,

I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.

The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.

I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?

Cheers
james
`I dont think there is such a inbuilt function available in sql server. But if you could post the ddl ,some sample data and the expected result, i m sure someone here would definately be able to help you.|||One way to do this is to enumerate the values that you need, something like:SELECT d
FROM (SELECT Convert(DATETIME, '2006-03-01') AS d
UNION SELECT '2006-03-02'
UNION SELECT '2006-03-03'
UNION SELECT '2006-03-04'
UNION SELECT '2006-03-05'
UNION SELECT '2006-03-06'
UNION SELECT '2006-03-07'
UNION SELECT '2006-03-08') AS z-PatP|||Or more generically, create a table of sequential numbers in your database starting with zero, from which you can dynamically create any sequence of dates. Like this:select XValues.XValue,
YourData.YValue
from --XValues
(select dateadd(day, SeqValue, '2006-01-01') as XValue
from SequentialNumbers) XValues
left outer join YourData on XValues.XValue = YourData.XValue|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott

Thank you all
I think blindman and htmscott have described pretty much the same method.
I will give these a try and see how it goes.

Cheers
James|||Thanks again guys, the method suggested by you guys works great.

Cheers
Jamessql

fill down formulas without selecting the sheet

At the moment I have a bunch of formulas along the top row of a sheet and I
wish to copy them down a predfined number of rows. At the moment I activate
the sheet and then select the rows I want to copy down with the row
containing the formulas at the top. It would be much er if I could
accomplish this in the background as it were without having to select the
sheet. Is there a way of effectively filling down the formula without having
to activate the sheet?
Any help much appreciated, kind regards, MarkAre you sure you posted in the right NG ? That should go for the excel ng as
I hear from your problem ?!
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mark Stephens" wrote:

> At the moment I have a bunch of formulas along the top row of a sheet and
I
> wish to copy them down a predfined number of rows. At the moment I activat
e
> the sheet and then select the rows I want to copy down with the row
> containing the formulas at the top. It would be much er if I could
> accomplish this in the background as it were without having to select the
> sheet. Is there a way of effectively filling down the formula without havi
ng
> to activate the sheet?
> Any help much appreciated, kind regards, Mark
>
>

FileTime

Is there a SQL function to get FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
You can use DATEDIFF(ms,startdate, GETDATE())
where ms stands for milliseconds, startdate is a your own starting date, and
getdate is the system function that returns the current date, up to
milliseconds.
Francesco Anti
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...[vbcol=seagreen]
> Is there a SQL function to get FileTime?
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
filetime
>
|||One thing to remember is that since SQL Server DateTime has less reolution,
you can do a direct comparison.
"Francesco Anti" <fanti @. sicosbt.it> wrote in message
news:ed2YskSBFHA.608@.TK2MSFTNGP15.phx.gbl...
> You can use DATEDIFF(ms,startdate, GETDATE())
> where ms stands for milliseconds, startdate is a your own starting date,
and[vbcol=seagreen]
> getdate is the system function that returns the current date, up to
> milliseconds.
> Francesco Anti
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
time
> filetime
>

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
No, not built-in; I guess the only way would be to create your own UDF.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...[vbcol=seagreen]
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
filetime
>

FileTime

Is there a SQL function to get FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the fileti
me
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>You can use DATEDIFF(ms,startdate, GETDATE())
where ms stands for milliseconds, startdate is a your own starting date, and
getdate is the system function that returns the current date, up to
milliseconds.
Francesco Anti
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
> Is there a SQL function to get FileTime?
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
>
filetime[vbcol=seagreen]
>|||One thing to remember is that since SQL Server DateTime has less reolution,
you can do a direct comparison.
"Francesco Anti" <fanti @. sicosbt.it> wrote in message
news:ed2YskSBFHA.608@.TK2MSFTNGP15.phx.gbl...
> You can use DATEDIFF(ms,startdate, GETDATE())
> where ms stands for milliseconds, startdate is a your own starting date,
and
> getdate is the system function that returns the current date, up to
> milliseconds.
> Francesco Anti
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
time[vbcol=seagreen]
> filetime
>

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the fileti
me
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>No, not built-in; I guess the only way would be to create your own UDF.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
>
filetime[vbcol=seagreen]
>sql

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:
>
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > How can I convert filetime to datetime in sql server inside a sored
> > procedure.
> >
> > Thanks
> > BVR
>
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>No, not built-in; I guess the only way would be to create your own UDF.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
> >
> > "uhway" <uhway@.discussions.microsoft.com> wrote in message
> > news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > > How can I convert filetime to datetime in sql server inside a sored
> > > procedure.
> > >
> > > Thanks
> > > BVR
> >
> >
> > What do you mean by filetime? Can you give an example of what the
filetime
> > data looks like?
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
>

File I/O in PL/SQL

Hi,

Please help!
I need to write a stored procedure that will replace a word in a number of files in a directory.

I am new to PL/SQL and will really appreciate the help. I have just learned about UTL_FILE.FOPEN thing and is not able to write it properly.

Thanks in advance for your help.

Regards,
ArunHi arun1581,

First of all: using PL/SQL for manipulating texts in an plain text-file is not the tool I would use. Much better (and easier to handle) form my expierinece is unsing Perl.

But if you prefere to use PL/SQL you must first make sure sure, that your have set the init.ora-parameter 'utl_file_dir' to a directory, which you can use. This is not dynamic, so you have to reboot the Instance after changing it.

furthermore read the documtenation of how to use the built-in package utl_file: http://tahiti.oracle.com/pls/db901/db901.tabbed?section=33316

basically you need to take care for:

1. havind a utl_file_dir defined and acces-rights to it
2. a file-handle
3. the open/close functions of utl_file package
4. the read/write operations from that package

hope it helps LaoDe|||Hi,

Example :
----

set echo on
!mkdir /tmp/public_access

connect sys/change_on_install as sysdba;
drop user tcopy01 cascade;
grant connect, resource to tcopy01 identified by tcopy01;
grant select_catalog_role to tcopy01;

create or replace directory public_access as '/tmp/public_access';
grant read on directory public_access to public;

connect tcopy01/tcopy01
create table tcopy01_out (line varchar2(500), i number);
create procedure tcopy01_p as errbuf varchar2(50);
dir varchar2(512) := 'PUBLIC_ACCESS';
f1 utl_file.file_type;
type t_files is table of utl_file.file_type index by binary_integer;
files t_files;
i number := 0;
ok boolean := TRUE;
pos number;
len number;
blk number;

procedure insertoutput (line varchar2) is
begin
insert into tcopy01_out values (line, i);
i := i+1;
end insertoutput;

begin
f1 := utl_file.fopen('PUBLIC_ACCESS', 'tcopy01.dat', 'w'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 1.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 2.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 3.'); utl_file.fclose(f1);