Binding dataset to a crystal report

Hi all,

The code I used is as follows.

dim dbconnection
as new oledb.oledbconnection(....conn string...)

Dim SQL As String

SQL = "SELECT * FROM <TABLENAME> "

dbConnection.Open()
Dim objAdapter As New OleDb.OleDbDataAdapter(SQL, dbConnection)
Dim objDataSet As New DataSet
objAdapter.Fill(objDataSet)
Dim oReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim sRptPath As String = Server.MapPath("CRlMT.rpt")
oReport.Load(sRptPath)
oReport.SetDataSource(objDataSet)
CrystalReportViewer1.ReportSource = oReport

when i ran it I got the following error.

Logon failed. Details: ADO Error Code: 0x Source: Provider Description: Authentication failed. Native Error: Error in File <path>.rpt: Unable to connect: incorrect log on parameters.

what I am doing wrong here. Help is greatly appreciated.

note: asp.net web app.Make sure the file exists at the application path. Also make sure if you have permission to access the databasesql

bind the asp:SqlDataSource to a label control

how can i display the result of an asp:SqlDataSource into a lable control.
the sqldatasource returns the count for some thing ie "select count(*) as total from tbl"

please help

There are two general approaches you can use:
1. Use a data bound control, such as a FormView or DetailsView. Bind the data bound control to the SqlDataSource, and then place the Label inside that control and databind it as you would any other control in a template.
2. Manually call SqlDataSource's Select() method and extract the result value:
DataSet result = (DataSet)SqlDataSource.Select(DataSourceSelectArguments.Empty);
Label1.Text = result.Tables[0].Rows[0][0].ToString();
Thanks,
Eilon

Bind many tables from a single sp on many tables on a single repor

Hi,
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?

Bind a Table to a StoredProcedure

Hi All
I am an RS Newbie - Please be gentle :-))
I have a table on an RS report
When the table is bound to a simple Select statement EG select * from
Customers
I can then further bind each control on the report to a field in the
recordset.
EG I select the relevant text box - then from the property sheet I select
from the list of available fields
in the 'Value' combo box on the property sheet
EG txtCustomer value in the property sheet = Fields!CustomerName.Value
Cool
But when I change the recordset for the table to a Stored Proc
I see no available fields when I select a Text box and try to bind it to a
value in the propertyy sheet
Infact rather than a list of available fields, all I can see is <Expression>
Don't know where to go from here - any suggestions appreciated
Many thanks
DenzilSometimes when you switch to a stored procedure it does not detect the field
list. Try the following. Hopefully one of these two will do the trick.
1. in the dataset view click on the refresh fields button (it is to the
right of the ... and looks like the refresh button from IE
2. Make sure the command type is stored procedure and just put in the name
of the stored procedure like this: MyStoredProcName
Not like this: Exec MyStoredProcName
After doing this then try #1 again.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c3160a2829f0@.uwe...
> Hi All
> I am an RS Newbie - Please be gentle :-))
> I have a table on an RS report
> When the table is bound to a simple Select statement EG select * from
> Customers
> I can then further bind each control on the report to a field in the
> recordset.
> EG I select the relevant text box - then from the property sheet I select
> from the list of available fields
> in the 'Value' combo box on the property sheet
> EG txtCustomer value in the property sheet = Fields!CustomerName.Value
> Cool
> But when I change the recordset for the table to a Stored Proc
> I see no available fields when I select a Text box and try to bind it to a
> value in the propertyy sheet
> Infact rather than a list of available fields, all I can see is
> <Expression>
> Don't know where to go from here - any suggestions appreciated
> Many thanks
> Denzil|||Hi Bruce
Many thanks for the swift reply
When I read the bit about changing the command type I thought that would be
it
But...sorry none of this worked <sigh>
When I change the Command Type to StoredProc and remove the 'exec' at the
front of the
query string then try and run it - it errors out and does not give me the
expected popup box where I would manually enter any parameters
The error is
"An error occurred whilst trying to retrieve the parameters in the query
rsGetAccountList @.ActiveStatus does not exist"
before, I used to have "exec rsGetAccountList @.ActiveStatus" in the dataset
view and had the command type as Text. With that setup I could run the query
in DataSet view and get my parameter popup box
Any more suggestions? :-))
Many thanks
Denzil
Bruce L-C [MVP] wrote:
>Sometimes when you switch to a stored procedure it does not detect the field
>list. Try the following. Hopefully one of these two will do the trick.
>1. in the dataset view click on the refresh fields button (it is to the
>right of the ... and looks like the refresh button from IE
>2. Make sure the command type is stored procedure and just put in the name
>of the stored procedure like this: MyStoredProcName
>Not like this: Exec MyStoredProcName
>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>[quoted text clipped - 23 lines]
>> Denzil|||Did you put this? rsGetAccountList @.ActiveStatus
If so, remove th @.ActiveStatus. Just put the name of the stored procedure.
RS automatically retrieves the parameter list from the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c319c74a8f20@.uwe...
> Hi Bruce
> Many thanks for the swift reply
> When I read the bit about changing the command type I thought that would
> be
> it
> But...sorry none of this worked <sigh>
> When I change the Command Type to StoredProc and remove the 'exec' at the
> front of the
> query string then try and run it - it errors out and does not give me the
> expected popup box where I would manually enter any parameters
> The error is
> "An error occurred whilst trying to retrieve the parameters in the query
> rsGetAccountList @.ActiveStatus does not exist"
> before, I used to have "exec rsGetAccountList @.ActiveStatus" in the
> dataset
> view and had the command type as Text. With that setup I could run the
> query
> in DataSet view and get my parameter popup box
> Any more suggestions? :-))
> Many thanks
> Denzil
>
> Bruce L-C [MVP] wrote:
>>Sometimes when you switch to a stored procedure it does not detect the
>>field
>>list. Try the following. Hopefully one of these two will do the trick.
>>1. in the dataset view click on the refresh fields button (it is to the
>>right of the ... and looks like the refresh button from IE
>>2. Make sure the command type is stored procedure and just put in the name
>>of the stored procedure like this: MyStoredProcName
>>Not like this: Exec MyStoredProcName
>>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>>[quoted text clipped - 23 lines]
>> Denzil|||Thanks Bruce
yes that was the answer
Many many thanks for your assistance
I really appreciate it
have agreat day
Darren
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1|||No problem. Support is strong for stored procedures but some of what you
need to do is not intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil via SQLMonster.com" <u18940@.uwe> wrote in message
news:5c4128a5414d2@.uwe...
> Thanks Bruce
> yes that was the answer
> Many many thanks for your assistance
> I really appreciate it
> have agreat day
> Darren
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1sql

Binary Value Manipulation

I would like to drop the leading 0x on a binary value so I can do a
bitwise operation.
Here is simplified code:
select right(0x88186000,8)
I expected to get back 88186000, this was not the case. The command
returned some wierd characters.
Am I missing something?In this context 0x88186000 is a binary literal. There is no leading zero.
The "0x" is just part of the syntax for binary literals.
What bitwise operation are you trying to do? Lookup the bitwise operators in
Books Online. You probably won't get the answer you want by using string
operators such as RIGHT().
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 May 2007 12:01:27 -0700, tfeller wrote:
>I would like to drop the leading 0x on a binary value so I can do a
>bitwise operation.
(snip)
Hi tfeller,
I just replied to a copy of this message in comp.databases.ms-sqlserver.
In the future, please limit yoour questions to a single newsgroup - or
if you feel you really have to post to more than one group, use your
news program's crossposting function to post one copy to multiple groups
instead of posting independant copies. That prevents people from
spending time on a question that has already been answered in another
group.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Binary Column always returns 0 for ISNULL

Why don't i ever get return value of 1 when the following binary column (profSignature) is null?

RETURN SELECT ISNULL

(profSignature, profSignature)FROMmpProfilesWHEREApplicantID =CAST(@.CIDAS INT)ANDProfileID =CAST(@.PIDAS INT)

There is no conversion between GUID and INT that is what I think you are trying to do, try the link below for SQL Server data type conversion chart. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||I forgot to add that the column type is "Image" in SQL 2005|||

Dup post

|||How would I get it so a <NULL> column value makes the stored procedure return 1 or 0?|||

I have checked the conversion chart again that is not valid either so check the create table statement below from Microsoft AdventureWorks it pick the columns relevant to your situation and you can get the information you are looking for. Sorry about the duplicate post. Hope this helps.

CREATE TABLE [EmployeePhoto] (
[EmployeePhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_EmployeePhoto_EmployeePhotoID] PRIMARY KEY CLUSTERED
(
[EmployeePhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

If you need 1 on null try this:

RETURN SELECT ISNULL(profSignature,1)
FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)

or

RETURN (case
when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and profSignature is null) then 1 -- for null

when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and not profSignature is null) then 2 -- for not nulls

else

0 -- does not exists in database

end)

|||Thanks ... the case statement did the trick.sql

bigint - Arithmetic overflow

Hi,

I am getting

Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value =
1152921504606847000.000000.

when I do

select power(2,60) in query analyzer

then I tried

declare @.b bigint
select @.b = power(2,60)

same error.

I tried

select convert(bigint,power(2,60)) no luck

Is there any way to tell sql server to use bigint when it
calculate power(2,60)

Ram.This worked:

declare @.b bigint
select @.b = power(cast(2 as bigint),60)
select @.b

-------
1152921504606846976

(1 row(s) affected)|||Originally posted by rdjabarov
This worked:

declare @.b bigint
select @.b = power(cast(2 as bigint),60)
select @.b

-------
1152921504606846976

(1 row(s) affected)

much thanks !!!!!

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...|||I suggest you walk through the list of issues posted in my blog--there is a
whitepaper there that discusses these
issues.http://betav.com/blog/billva/2006/0...com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:O8v9ZKOjHHA.4936@.TK2MSFTNGP03.phx.gbl...
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
> news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> I both did everything...enabled TCP/IP and Remote Connection, opened port
> 1431 default port...

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...
Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...
|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...
sql

BETWEEN syntax at SQL

Hi,

I would like to know how MSSQL handles dates for BETWEEN syntax at SQL statements.

e.g. SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

I find that the result rows do not include ones with [date]='2003/12/31'

So, now I'm using BETWEEN '2003/01/01' AND '2004/01/01' instead.

Is it a standard way to perform this kind of task?between ... and ... is equal >= and <=
So I think u can not get the data that date column value is bigger than 2003/12/31 00:00:00

SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

must change to ==>

SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND dateadd(day,1,'2003/12/31')|||enhydraboy is right with the date time of '2003/12/31 00:00:00'
This is where your between will have a problem when comparing
'2003/12/31' against lets say '2003/12/31 12:45:30'

some times I just force it to '2003/12/31 23:59:59'
when I use date and time : )

Hope this helps|||Originally posted by TrueCodePoet
enhydraboy is right with the date time of '2003/12/31 00:00:00'
This is where your between will have a problem when comparing
'2003/12/31' against lets say '2003/12/31 12:45:30'

some times I just force it to '2003/12/31 23:59:59'
when I use date and time : )

Hope this helps

Well ...
The standard i would use is

SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'|||Originally posted by Enigma
Well ...
The standard i would use is

SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'

That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

And if he wants to include all of the last day of the month, it's

BETWEEN '1 Jan 2004' AND '1 Feb 2004'|||Originally posted by HanafiH
That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

And if he wants to include all of the last day of the month, it's

BETWEEN '1 Jan 2004' AND '1 Feb 2004'

Never did I think of it that way ... and believe me .. I have been using it like this for past two years ... thanks for correcting me ... you have been a great help :)

Well .. you learn at least one new thing on this site everyday|||Writing International Transact-SQL Statements (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp)
A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). Using the ISO format is more "international," and is independent of the default language. For more information, see the CONVERT function in the SQL Server Books Online.|||It makes no difference what format the datetime value is sent in. It does make a difference whether the datetime value includes a time portion or not. Many applications cannot restrict themselves to whole date values, and thus some sort of conversion or adjustment is necessary to include all events occuring on the last day.|||Originally posted by buser
A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). excellent advice

one minor point: ISO standard does include separators

see Numeric representation of Dates and Time (http://www.iso.ch/iso/en/prods-services/popstds/datesandtime.html)|||Sorry.. I might have been confusing .. I will just post what I usualy do.

SELECT * FROM [sysobjects] WHERE [crdate]
BETWEEN
convert(datetime,'2003/01/01 00:00:00',120)
AND
convert(datetime,'2003/12/31 23:59:59',120)

the two functions should only be run once durring the compile
: )

I have not noticed much difference with this query on 1 mill plus records
Only reasonI don't use the 121 format is the Milleseconds is not needed for me Most of the Time I use the 101 format.

hope this helps|||SELECT * FROM [sysobjects] WHERE [crdate]
BETWEEN
convert(datetime,'2003/01/01 00:00:00',120)
AND
convert(datetime,'2003/12/31 23:59:59',120)

actually you don't have to convert them

when you put the year first like that, the database will assume that the next field is the month, etc.

SELECT * FROM [sysobjects]
WHERE [crdate]
BETWEEN '2003/01/01 00:00:00'
AND '2003/12/31 23:59:59'

Between Question

What am I doing wrong in the statement:

q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
Box1 & " and " & Box2 & ")"

It gives me the following error message: Incorrect syntax near the
keyword 'and'

the following code is what i am working with but it is not working as
it should. I am trying to pull data selected in the inputbox into
ectlog_holding table and run a report off there but keep getting the
errors. Stated above.

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=ETC;Data
Source=PHNOTES01"
conn.Open

conn.Execute "drop table etclog_holding"
Box1 = Trim(InputBox("Enter starting box#:"))
Box2 = Trim(InputBox("Enter ending box#:"))
q = "Select * Into etclog_holding from etclog where box# Between (" &
Box1 & " and " & Box2 & ")"
rs.Open q, conn, adOpenDynamic, adLockOptimistic

Dim rs1 As New ADODB.Recordset
q = "select * from etclog_holding"
rs1.Open q, conn, adOpenDynamic, adLockOptimistic

Any ideas??On 16 Feb 2005 14:22:39 -0800, pkruti@.hotmail.com wrote:

>What am I doing wrong in the statement:
>q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
>Box1 & " and " & Box2 & ")"

Hi pkruti,

Try this instead:

q = "Select * Into etclog_holding from etclog where box# BETWEEN " &
Box1 & " and " & Box2

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(pkruti@.hotmail.com) writes:
> What am I doing wrong in the statement:
> q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
> Box1 & " and " & Box2 & ")"
> It gives me the following error message: Incorrect syntax near the
> keyword 'and'

Have you tried to work out the syntax from reading the topic on
BETWEEN in Books Online? Hint: it's simpler than that you are trying
to make it to be.

Generally, syntax issues are best investigated by using Books Online,
since there are both syntax graphs and examples to work from. It may
take some time first, but it pays back in the long run.

> conn.Execute "drop table etclog_holding"
> Box1 = Trim(InputBox("Enter starting box#:"))
> Box2 = Trim(InputBox("Enter ending box#:"))
> q = "Select * Into etclog_holding from etclog where box# Between (" &
> Box1 & " and " & Box2 & ")"
> rs.Open q, conn, adOpenDynamic, adLockOptimistic
> Dim rs1 As New ADODB.Recordset
> q = "select * from etclog_holding"
> rs1.Open q, conn, adOpenDynamic, adLockOptimistic

There are several things in this snippet I don't like:

1) Creating a table is pointless. Just get the recordset from the
underlying table.
2) Don't use dynamic cursors unless you absolutely have to. And for a
report you don't. Best is to use client-side cursors, which you
by specifying .CursorLocation on the connection object.
3) Do you have validation on InputBox, so that you only get numeric
data from it? Else the user can specify evil things to get your
bacth do other things that you intended. Use parameterized statements
instead. For examples, see
http://authors.aspalliance.com/stev...cles/sprocs.asp

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Hugo that worked =)

Between operator does not bring the 2 extremes in the result set?

Hi,
Between operator does not bring the 2 extremes in the result set?
As in:
Select * from HT_DBAU001_Agenda where AgDtHora between '1/1/2003' and
'31/12/2003' order by AgDtHora
At this case here does not bring me the 2 extremes in the result set
'1/1/2003' and '31/12/2003', only
brings me what is between '1/1/2003' and '31/12/2003'.
Is it there any solution in Between operator without use >= and <= '?
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SPThis is a good example of why it is important to include ddl as well as
sample data. Assuming that AgDtHora columns is either datetime or small
datetime. The use of "between" will include the appropriate data. However,
you have failed to properly consider the time portion of the values stored
in the column as well as the "arguments" used for "between". Using
"between" will return all rows that have a value that falls at any time on
the lower date boundary. It will only include rows with values that fall at
precisely the beginning of time on the upper boundary (i.e., Dec 31 2003
00:00:00.000) since you only specified the date portion of the appropriate
datatype. So if you really want to use "between", you need to fully qualify
the upper argument as '20031231 23:59:59.997' (for datetime) or '20031231
23:59:59' (for smalldatetime).
create table #test (id int not null, tdate datetime not null)
insert #test (id, tdate) values (2, '20031225 01:00:00.003')
insert #test (id, tdate) values (1, '20031225')
insert #test (id, tdate) values (3, '20031226 12:24:01.997')
insert #test (id, tdate) values (4, '20031226')
select * from #test
select * from #test where tdate between '20031225' and '20031226'
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:%23xegAj%23yDHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Between operator does not bring the 2 extremes in the result set?
> As in:
> Select * from HT_DBAU001_Agenda where AgDtHora between '1/1/2003' and
> '31/12/2003' order by AgDtHora
> At this case here does not bring me the 2 extremes in the result set
> '1/1/2003' and '31/12/2003', only
> brings me what is between '1/1/2003' and '31/12/2003'.
> Is it there any solution in Between operator without use >= and <= '?
> Thanks,
> VILMAR
> BRAZIL
> PRAIA GRANDE/SP
>|||OK,
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SP
"Scott Morris" <bogus@.bogus.com> escreveu na mensagem
news:#L7lv7#yDHA.560@.TK2MSFTNGP11.phx.gbl...
> This is a good example of why it is important to include ddl as well as
> sample data. Assuming that AgDtHora columns is either datetime or small
> datetime. The use of "between" will include the appropriate data.
However,
> you have failed to properly consider the time portion of the values stored
> in the column as well as the "arguments" used for "between". Using
> "between" will return all rows that have a value that falls at any time on
> the lower date boundary. It will only include rows with values that fall
at
> precisely the beginning of time on the upper boundary (i.e., Dec 31 2003
> 00:00:00.000) since you only specified the date portion of the appropriate
> datatype. So if you really want to use "between", you need to fully
qualify
> the upper argument as '20031231 23:59:59.997' (for datetime) or '20031231
> 23:59:59' (for smalldatetime).
>
> create table #test (id int not null, tdate datetime not null)
> insert #test (id, tdate) values (2, '20031225 01:00:00.003')
> insert #test (id, tdate) values (1, '20031225')
> insert #test (id, tdate) values (3, '20031226 12:24:01.997')
> insert #test (id, tdate) values (4, '20031226')
> select * from #test
> select * from #test where tdate between '20031225' and '20031226'
>
> "Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
> news:%23xegAj%23yDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > Between operator does not bring the 2 extremes in the result set?
> > As in:
> > Select * from HT_DBAU001_Agenda where AgDtHora between '1/1/2003' and
> > '31/12/2003' order by AgDtHora
> > At this case here does not bring me the 2 extremes in the result set
> > '1/1/2003' and '31/12/2003', only
> > brings me what is between '1/1/2003' and '31/12/2003'.
> > Is it there any solution in Between operator without use >= and <= '?
> > Thanks,
> >
> > VILMAR
> > BRAZIL
> > PRAIA GRANDE/SP
> >
> >
>

between operator

Hi,

I'm having problems using the between operator in a query statement.

Select *
from <mytable>
where date between @.date1 and @.date2

The date values with a hour specified, aren't returned. What is the approach you would recommend here?

Thx

EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @.fromDate + '00:00:00' and @.toDate + '23:59:59'

this seem to work, but I'm not sure if this is correct

Select *
from <mytable>
where date >= @.date1 and date < dateadd(d,1,@.date2)

or

Select *
from <mytable>
where date >= @.date1 and date < @.date2 +1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i had a similar problem which i did through the application side:

string weekdate =txtFrom.Text;

string daydate = System.Convert.ToDateTime(txtTo.Text).Add(System.TimeSpan.FromDays(1)).ToString();

and later...

TechnicianViewInboxDataAdapter.SelectCommand.CommandText = tempselecttechnician + " where status = 1 and TechnicianID = " + Session["UserID"].ToString() + "and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' and DepartmentID = " + Session["DepartmentID"].ToString();

this works as well.

the code ends up like this:

SELECT FirstName, LastName, Email, CallNumber, DepartmentName, UserName, Status, TechnicianID, CallLoggedDT, DepartmentID, CallOpenedDT, CallActionedDT, CallClosedDT FROM dbo.TechnicianInboxView where status = 1 and TechnicianID = 2and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' and DepartmentID = 2

|||

You can also use the datediff function and the statement would look like this.

Select * from <mytable> where

datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

@.Date can be varchar too and it can be in the format of "mm/dd/yy" also. Need not have the time factor.

|||

where datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

Would not recommend doing so in the terms of performace. You will never get index seek over the index for datefield column: it will always index scan at the best.

between dates

in sql2000
"select * from tbl where tbl.date between date1 and date2" - results with
nothing
"select * from tbl where tbl.date >= date1 " - gives result
"select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
with nothing
how to select between 2 dates?
thanksHow about providing us with real specs, e.g. are the columns really
datetime/smalldatetime? What data is in them? What values are you using
for date1 and date2? What format are they in?
Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
only get vague answers.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||column date is datetime type
the values are simple:
"select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
gives NO RESULT
"select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
"select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives NO
RESULT
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
> How about providing us with real specs, e.g. are the columns really
> datetime/smalldatetime? What data is in them? What values are you using
> for date1 and date2? What format are they in?
> Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
> only get vague answers.
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||See point number 3 in the first link.
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
AMB
"Sam" wrote:

> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
NO
> RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>
>|||If u r using date1 and date2 as parameters, in ur case date2 must be null.
Even if one of the dates is null, BETWEEN or simple comparison will fail as
happening in ur cases.
pls chk and let me know in case of any dbts
Rakesh
"Sam" wrote:

> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||(a) you need to delimit datetime values with '
(b) never use ambiguous and silly formats like m/d/y or d/m/y. Is
01/05/2005 January 5th or May 1st? Who knows?
Try:
WHERE date >= '20020101' AND date <= '20050105'
"Sam" <focus10@.zahav.net.il> wrote in message
news:ehA02sirFHA.2592@.TK2MSFTNGP09.phx.gbl...
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
> NO RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>|||
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
What are the date values for these results? Are they all later than
"01/05/2005"?
Perayu|||i found the solution:
@.date1 nvarchar(12), @.date2 nvarchar(12),
'BETWEEN CONVERT(DATETIME, ' + char(39) + @.date1 + char(39) + ', 102) AND
CONVERT(DATETIME, '+ char(39) + @.date2 + char(39) +', 102)'
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:790C5553-6B88-45F8-A1D6-4F32CF001010@.microsoft.com...
> If u r using date1 and date2 as parameters, in ur case date2 must be null.
> Even if one of the dates is null, BETWEEN or simple comparison will fail
> as
> happening in ur cases.
> pls chk and let me know in case of any dbts
> Rakesh
> "Sam" wrote:
>

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>

between

SQL Server2000 SP3
Field date is smalldatetime
I have 8 rows with data='06-Aug-2003'
SELECT * FROM TEST
where data between '06-Aug-2003' and '06-Aug-2003'
Results: 0 Rows
SELECT * FROM TEST
where data>='06-Aug-2003' and data<='06-Aug-2003'
Results: 0 Rows
What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')What is the time portion of the field ?
What does this give you
SELECT * FROM TEST
where data between '20030806' and '20030807'
OR
SELECT * FROM TEST
where CONVERT(char(8),data,112) = '20030806'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dragos Hilbert" <dragos_hilbert@.yahoo.co.uk> wrote in message
news:eMh$KHAXDHA.652@.TK2MSFTNGP10.phx.gbl...
> SQL Server2000 SP3
> Field date is smalldatetime
> I have 8 rows with data='06-Aug-2003'
> SELECT * FROM TEST
> where data between '06-Aug-2003' and '06-Aug-2003'
> Results: 0 Rows
>
> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
> Results: 0 Rows
> What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')
>
>|||> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
SELECT * FROM test
WHERE data>='20030806' and data<'20030807'
(a) between can be ambiguous, I tend to avoid it for datetime queries.
(b) since datetime columns have time also, the above is converted to
midnight, so you won't likely have rows where "data" is between a day at
midnight and the same day at midnight.
(c) never use SELECT * in production code.
(d) consider changing the column name. "data" is not very descriptive at
all; I would never guess that it is a datetime column...

better way to update then select

since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)

if I dont include the updatethen a text file can be created. If I include the update, the column is updated but the text file isnt created.

i'm not sure what to do... any suggestions?

a WHEN-BUTTON-PRESSEd trigger calls this procedure:

PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS

CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'


rProcess dateProcess%ROWTYPE;

cOut VARCHAR2(2000);
myfile VARCHAR2(255);
mypath VARCHAR2(255);

N_FILE text_io.file_type;

BEGIN


UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;


OPEN dateProcess;

myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
--myfile := sysdate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';

N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');

LOOP
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';'
|| rProcess.SIR_UPD_USERID || ';'
|| rProcess.SIR_UPD_DATE;

TEXT_IO.PUT_LINE(N_FILE, cOut);

END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;

EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;

END;I'm no expert on Oracle Forms but have you tried to create file before commit not after. Do the update, select data and create file, commit. You are in the same transaction, so you will be able to see your newly update data.

Hope it helps.

Originally posted by alram
since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)

if I dont include the updatethen a text file can be created. If I include the update, the column is updated but the text file isnt created.

i'm not sure what to do... any suggestions?

a WHEN-BUTTON-PRESSEd trigger calls this procedure:

PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS

CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'


rProcess dateProcess%ROWTYPE;

cOut VARCHAR2(2000);
myfile VARCHAR2(255);
mypath VARCHAR2(255);

N_FILE text_io.file_type;

BEGIN


UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;


OPEN dateProcess;

myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
--myfile := sysdate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';

N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');

LOOP
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';'
|| rProcess.SIR_UPD_USERID || ';'
|| rProcess.SIR_UPD_DATE;

TEXT_IO.PUT_LINE(N_FILE, cOut);

END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;

EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;

END;|||Some alternatives:

1) You could create the file BEFORE you do the update. The problem you have is that if someone else inserts and commits another record into the table between your opening the cursor and doing the update, then you could end up updating some records that you didn't actually put in the file. This can be overcome using "Alter session set isolation_level=serializable" before opening the cursor.

2) You could do the update first as you do now, but in the update also set a new column e.g. batch_id to identify the records you updated. Then use this batch_id in the cursor:

DECLARE
v_batch_id INTEGER;
CURSOR dateProcess( p_batch_id INTEGER ) IS
SELECT *
FROM SIR
WHERE batch_id = p_batch_id;
BEGIN
SELECT batch_seq.NEXTVAL INTO v_batch_id FROM DUAL;

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y',
BATCH_ID = v_batch_id
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;

FOR rec IN dateProcess( v_batch_id )
LOOP
-- Output record to file
...
END LOOP;
...

3) You could read and update them at the same time:

PROCEDURE ... IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
FOR UPDATE OF SIR_REQUEST_SENT;
...
BEGIN
FOR rec IN dateProcess LOOP
-- Output record to file
...
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE CURRENT OF dateProcess;
END LOOP;
...
END;|||Thank you for the replies! Very helpful!

sir andrewst,
I was just wondering, is there a way to update first without creating the batch i.d. column?|||Originally posted by alram
Thank you for the replies! Very helpful!

sir andrewst,
I was just wondering, is there a way to update first without creating the batch i.d. column?
Yes, if you really want to you can do this:

declare
cursor c is select * from t where status='N';
r c%ROWTYPE;
begin
open c;
update t set status='Y';
loop
fetch c into r;
exit when c%NOTFOUND;
dbms_output.put_line('=='||r.id);
end loop;
close c;
end;

The key point is to OPEN the cursor BEFORE doing the UPDATE.

You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.|||sir andrewst, thanks again for your help!!|||Originally posted by andrewst

You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.

sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:

nls_language
nls_territory
nls_date_format
nls_date_language
nls_numeric_characters
nls_iso_currency
nls_iso_currency
nls_sort
nls_calendar

i've been looking for other commands that i can use to ensure serializability but no such luck so far.|||If you put spaces arounf equal sign this command will be recognised:

alter session set isolation_level = serializable;

Originally posted by alram
sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:

nls_language
nls_territory
nls_date_format
nls_date_language
nls_numeric_characters
nls_iso_currency
nls_iso_currency
nls_sort
nls_calendar

i've been looking for other commands that i can use to ensure serializability but no such luck so far.|||Originally posted by sjacek
If you put spaces arounf equal sign this command will be recognised:

alter session set isolation_level = serializable;

i gave that a try too, before opening the cursor but the error that comes out during compilation is this:

Encountered the symbol 'alter' when expecting one of the following:

begin declare end exception exit for goto if....(etc.)|||You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:

execute immediate 'alter session set isolation_level = serializable';

This should work.

Originally posted by alram
i gave that a try too, before opening the cursor but the error that comes out during compilation is this:

Encountered the symbol 'alter' when expecting one of the following:

begin declare end exception exit for goto if....(etc.)|||Originally posted by sjacek
You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:

execute immediate 'alter session set isolation_level = serializable';

This should work.

sir, i tried just that. but i still encountered an error:

Encountered the symbol "IMMEDIATE" when expecting one of the ff:

:= . [ @. % ;

The symbol ".= was inserted before "IMMEDIATE" to continue.

what am i doing wrong?|||i have this problem: i need to check if the SIR_REQUEST_SENT flag is 'Y'. if it is, then the text/dat file should not be created anymore.

my question is, is this statement possible and where should i put it?

IF rProcess.SIR_REQUEST_SENT = 'N' THEN
-- retrieve the records
-- create the file

ELSE
CLOSE dateProcess;

here is my code:
-------

PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS

CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
ORDER BY SIR_TRANS_NO;

rProcess dateProcess%ROWTYPE;

cOut LONG;
myfile VARCHAR2(255);
mypath VARCHAR2(255);

N_FILE text_io.file_type;

sir_columns VARCHAR2(2000) := 'SIR_TRANS_NO,SIR_COMPANY,SIR_PROJECT,SIR_APPL,SIR _BUS_FUN,SIR_REPORTED_BY,
SIR_TYPE,SIR_CLASSIFICATION,SIR_DSCRIPTION,SIR_REA SON,
SIR_REQUEST_DATE,SIR_ATTACHMENT,SIR_REQUEST_SENT,
SIR_CRE_USERID,SIR_CRE_DATE';


BEGIN

OPEN dateProcess;

myfile := sysdate||'_'||'from'||'_'||fromdate||'_'||'to'||'_ '||todate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';

N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');

LOOP

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
:SYSTEM.MESSAGE_LEVEL := 5;
COMMIT;
:SYSTEM.MESSAGE_LEVEL := 0;

FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';';

TEXT_IO.PUT_LINE(N_FILE, cOut);

END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;

-- CREATE A CONTROL FILE
n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.ctl','W');

-- INSERT THE TEXT ENTRY TO THE CONTROL FILE
TEXT_IO.PUT_LINE( N_FILE ,'LOAD DATA' );
TEXT_IO.PUT_LINE( N_FILE ,'INFILE '||''''||myfile||'''');
TEXT_IO.PUT_LINE( N_FILE ,'APPEND'||' INTO TABLE '||'SIR');
TEXT_IO.PUT_LINE( N_FILE ,'FIELDS TERMINATED BY '';'' OPTIONALLY ENCLOSED BY ''"''
TRAILING NULLCOLS
('||sir_columns||' )
');

if text_io.is_open( n_file ) then
TEXT_IO.FCLOSE(N_FILE);
end if;

EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;

END;
------

i have tried putting the IF statement right after i open the cursor, but even if there are records with SIR_REQUEST_SENT = 'N', the file is not created anymore even though it should. i figure that it is only checking the first record.

any help would be appreciated! thank you in advance!|||I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?

Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:

FOR rProcess IN dateProcess LOOP
IF NOT TEXT_IO.ISOPEN(n_file) THEN
-- Open the file
n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');
END IF;
-- Output record to file
...
END LOOP;

-- If file was opened, then complete process
IF v_file_opened THEN
TEXT_IO.FCLOSE(n_file);

-- Update records
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N';

-- Create control file
...
--
END IF;|||Originally posted by andrewst
I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?

Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:



sir andrewst, sorry about that..

to make things clearer, these are my steps:

- Input a from_date and to_date.

- check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.

- If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.

again, thank you for your help! i'm really appreciate it!|||Originally posted by alram
sir andrewst, sorry about that..

to make things clearer, these are my steps:

- Input a from_date and to_date.

- check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.

- If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.

again, thank you for your help! i'm really appreciate it!
OK, in that case the logic I showed should work for you.

Better way to do this query

Is there a better way to do this query? I was hoping to do it with a CASE
and IF...
SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate, DocType,
AgingBucket,
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 1 THEN
Amount
ELSE 0
END AS 'IsCurrent',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 2 THEN Amount
ELSE 0
END AS '30 to 60',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 3 THEN Amount
ELSE 0
END AS '60 to 90',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 4 THEN Amount
ELSE 0
END AS '90 to 120',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 5 THEN CURTRXAM
ELSE 0
END AS '120 to 150',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 6 THEN Amount
ELSE 0
END AS '150 to 180',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 7 THEN Amount
ELSE 0
END AS 'Over 180'
FROM OpenInvoicesWhy are you using SELECT TOP 100 Percent in this query? You don't need to
specify that when you want all results returned.
Using a "WHEN" is similar to using an "IF" statement. You can specify
several when's in the same column case statement to cover various scenarios,
much like using an IF statement for several scenarios.
Are you trying to create an aggrigated report for your buckets, or do you
want a detailed list of each customer and their respective buckets?
You can sum, or use any other aggrigate function on these case columns to
return a report type query, but I'm still by the "top 100 percent"
statement.
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is there a better way to do this query? I was hoping to do it with a CASE
> and IF...
> SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate,
DocType,
> AgingBucket,
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 1 THEN
> Amount
> ELSE 0
> END AS 'IsCurrent',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 2 THEN Amount
> ELSE 0
> END AS '30 to 60',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 3 THEN Amount
> ELSE 0
> END AS '60 to 90',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 4 THEN Amount
> ELSE 0
> END AS '90 to 120',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 5 THEN CURTRXAM
> ELSE 0
> END AS '120 to 150',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 6 THEN Amount
> ELSE 0
> END AS '150 to 180',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 7 THEN Amount
> ELSE 0
> END AS 'Over 180'
> FROM OpenInvoices
>|||I'm using the TOP because query analyzer told me to, when I tried to create
a view based on the query. Originally I didn't use it.
I'm trying to get document level detail from this.
I have 7 CASE statements since I need 7 columns. Is there a way I can pivot
the data?
"SW" <simon.worth@.gmail.com> wrote in message
news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using SELECT TOP 100 Percent in this query? You don't need to
> specify that when you want all results returned.
> Using a "WHEN" is similar to using an "IF" statement. You can specify
> several when's in the same column case statement to cover various
scenarios,
> much like using an IF statement for several scenarios.
> Are you trying to create an aggrigated report for your buckets, or do you
> want a detailed list of each customer and their respective buckets?
> You can sum, or use any other aggrigate function on these case columns to
> return a report type query, but I'm still by the "top 100
percent"
> statement.
> "JP" <jperlowin@.firstfleet.com> wrote in message
> news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
CASE
> DocType,
>|||Oh OK, I understand then, if you're doing it in a view with an order by,
then you need the top in there. That makes sense.
To pivot the results you can use the GROUP BY with CUBE and GROUP BY with
ROLLUP functions, but the data may not be displayed exactly as you want it.
There is a document on Books Online that goes into detail as well on
crosstab queries, but you would probably have to run it from a stored
procedure instead in order to accommodate the temp tables required. Here's
the link
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_14_04j7.htm
or here on MSDN
http://msdn.microsoft.com/library/d.../>
sz_0kro.asp
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OsdDxtcHFHA.3648@.TK2MSFTNGP09.phx.gbl...
> I'm using the TOP because query analyzer told me to, when I tried to
create
> a view based on the query. Originally I didn't use it.
> I'm trying to get document level detail from this.
> I have 7 CASE statements since I need 7 columns. Is there a way I can
pivot
> the data?
> "SW" <simon.worth@.gmail.com> wrote in message
> news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
to
> scenarios,
you
to
> percent"
> CASE
>