Showing posts with label select. Show all posts
Showing posts with label select. Show all posts

Friday, March 30, 2012

newbie question on select

Hi All,
I need to get records vayring from 1 to 100, What is the best way without g
etting one record at a time.
Is the only other way by generating a select command with In Statment each t
ime,
But this will make the string very long.
Any help will appreicated
Thank You.Sound like you need a server side cursor / paging solution for your
problem:
http://www.google.com/search?hl=de&...ql+server&meta=
There are tons of hits on the internet, perhaps you take a deeper look
in the examples to decide for one.
HTH, jens Suessmeyer.|||Please post your DDL plus sample data and expected results. Do you want the
rows where a particular column is in the range 1 - 100? If so, try:
select
*
from
MyTable
where
MyCol between 1 and 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
Hi All,
I need to get records vayring from 1 to 100, What is the best way
without getting one record at a time.
Is the only other way by generating a select command with In Statment each
time,
But this will make the string very long.
Any help will appreicated
Thank You.|||What version are you using ?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
score INT NOT NULL,
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 1)
INSERT INTO SpeakerStats VALUES('Ron', 2)
INSERT INTO SpeakerStats VALUES('Kathy', 3)
INSERT INTO SpeakerStats VALUES('Suzanne', 4)
INSERT INTO SpeakerStats VALUES('Joe', 5)
INSERT INTO SpeakerStats VALUES('Robert', 6)
INSERT INTO SpeakerStats VALUES('Mike', 7)
WITH myCTE (rownum,speaker,score)
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, score
FROM SpeakerStats
)
SELECT * FROM myCTE WHERE rownum BETWEEN 5 AND 7
ORDER BY rownum DESC
SQL Server 2000
SELECT * FROM
(
SELECT * ,(SELECT COUNT(*) FROM SpeakerStats S
WHERE S.speaker<=SpeakerStats.speaker)rownum
FROM SpeakerStats
) AS Der WHERE rownum >=5 AND rownum <8
ORDER BY rownum
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||We need more information on what you're trying to do. An example that we
could work with would be more helpful.
Regards
Colin Dawson
www.cjdawson.com
<CobraStrikes@.al.com> wrote in message
news:1140960857.29276.0@.ersa.uk.clara.net...
> Hi All,
> I need to get records vayring from 1 to 100, What is the best way
> without getting one record at a time.
> Is the only other way by generating a select command with In Statment each
> time,
> But this will make the string very long.
> Any help will appreicated
> Thank You.
>
>|||Sorry, I have posted this in the wrong group, it should have posted it to th
e Access group.
I have table with 500 employee details depending on the user selection it c
an be between
1 and 100 emp records of the 500 records not necessarily consecutive record
s.
I will google with link provided.
Thank you all for the quick replies.

Newbie question on identically named fields in a dataset

This is my first stab at this. I have a simple report that returns a few
columns from a database no sp no expressions just a select statement. There
are two tables that have the same field name and the dataset canâ't seem to
distinguish between them it returns the first field into both columns. When
I run it in Query Analyzer it looks fine. What am I missing here?Query Analyzer seems to be a bit more forgiving about identical names. In
your query, just use Aliases, and you should be ok.
A query like
"Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
Table2 (...)"
should give you fields named Table1Col1 and Table2Col1. A bit more trouble
to write, but you eliminate possible errors.
Kaisa M. Lindahl
"RYF" <RYF@.discussions.microsoft.com> wrote in message
news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> This is my first stab at this. I have a simple report that returns a few
> columns from a database no sp no expressions just a select statement.
> There
> are two tables that have the same field name and the dataset can't seem to
> distinguish between them it returns the first field into both columns.
> When
> I run it in Query Analyzer it looks fine. What am I missing here?|||THX that did the trick!
"Kaisa M. Lindahl" wrote:
> Query Analyzer seems to be a bit more forgiving about identical names. In
> your query, just use Aliases, and you should be ok.
> A query like
> "Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
> Table2 (...)"
> should give you fields named Table1Col1 and Table2Col1. A bit more trouble
> to write, but you eliminate possible errors.
> Kaisa M. Lindahl
> "RYF" <RYF@.discussions.microsoft.com> wrote in message
> news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> > This is my first stab at this. I have a simple report that returns a few
> > columns from a database no sp no expressions just a select statement.
> > There
> > are two tables that have the same field name and the dataset can't seem to
> > distinguish between them it returns the first field into both columns.
> > When
> > I run it in Query Analyzer it looks fine. What am I missing here?
>
>

newbie question on accessing count(*) results

hello,
this is my sql query with access:
Dim SQLstr2 As String = "Select count(*) as total, oDate from Order_Details where oNo = " + Request("oNo") + " group by oDate"

However, problem is, I am clueless about how to access the data that is derived from my count(*).

I tried this: Response.Write(reader2("total"))

All I get is that No data exists for the row/column.

I tried not to select oDate and even took away the WHERE, but the same problem persists. Any ideas please?here's a template code that might help you.


Dim ConnectionString As String = "Your_Connection_String"
Dim CommandText As String = "select Col1, Col2, Col3 from TableName"
Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)
Dim myCommand As New System.Data.SqlClient.SqlCommand(CommandText, myConnection)
myConnection.Open()

Dim DataReader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()
If DataReader.HasRows Then
Do While DataReader.Read()
Response.Write(DataReader.Item("Col1"))
Response.Write(DataReader.Item("Col2"))
Response.Write("<BR>")
Loop
End If
DataReader.Close()
myconnection.close()

hth

Wednesday, March 28, 2012

Newbie question about indexes

Hi Smile,

I have following statement :

SELECT * FROM Table WHERE Col1=@.Var1 AND Col2=@.Var2 ... AND ColN=@.VarN

How should I design indexes for best performance ?
(
Add one index on columns Col1 till ColN
or add N indexes, first for column Col1, second for Col2, ...
)

Thanks, for your suggestions

How many rows do you expect it to return?
Do you prefer retrieval speed over update speed?
Do you have other queries that might benefit from individual indexes?

|||Strictly from your query perspective, you will only need one index with the key (col1...colN). However, this index will be useless if say col1 is missing from your where clause. So you need to evaluate full set of queries that you plan to run on this table. Also, as Eric points out, you will need to evaluate the cost of updating indexes if you have lots of updates/inserts/deletes
Thanks|||

Hi,

I expect to return c. 100 rows. (Would indexing strategy differs if I have return whole table ?)
Retrieval speed is priority.
I have no other queries for this table.

Thank you Smile

|||Here's what the optimizer guys have to say:

One index should be fine if he always has every column in the WHERE condition and he is doing equality matching.

This will give two main plan options:

1. index lookup + fetch
2. table scan

It will be a cost-based decision.

Friday, March 23, 2012

Newbie Question

Can i use >= and LIKE together in a SELECT...WHERE.
SELECT * FROM X WHERE Y >= LIKE '1234%'Hi
You can not do this. What are you trying to achive?
It would help if you posted example DDL and data such as
http://www.aspfaq.com/etiquett___e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.___htm#inserts
along with queries and what you are trying to achieve.
John
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:eUCImzqMFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Can i use >= and LIKE together in a SELECT...WHERE.
> SELECT * FROM X WHERE Y >= LIKE '1234%'
>|||EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE = 20050322124227)
I want to select members after a specific M_DATE. But i don't want to write
hour section, so i wanted to use
LIKE. I2m newbie as i said, maybe think wrong. =)|||Well. =)
EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE = 20050322000000)|||Hi
You didn't post DDL, therefore I assume M_Date is a datetime. Datetime data
will assume midnight (00:00:00.000) if the time portion is specified
therefore
SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= '20050322'
AND M_DATE < '20050323'
are all occurences on the given date.
You could convert to string aswell although the first method should be
quicker.
SELECT FORUM_MEMBERS.M_DATE WHERE CONVERT(CHAR(8),M_DATE,112) = '20050322'
John
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:%23M0wJDrMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE = 20050322124227)
> I want to select members after a specific M_DATE. But i don't want to
> write hour section, so i wanted to use
> LIKE. I2m newbie as i said, maybe think wrong. =)
>|||Sorry. M_DATE value type is nvarchar in database.|||Hi
You can thererfore use the second example without the need to convert it to
char, although you may have to use LEFT to get the first 8 characters. This
assumes that you make sure that the format YYYYMMDD is always entered in
full.
SELECT FORUM_MEMBERS.M_DATE WHERE LEFT(M_DATE,8) = '20050322'
As you are using this format the following will also work for anything on or
after the given date.
SELECT FORUM_MEMBERS.M_DATE WHERE LEFT(M_DATE,8) >= '20050322'
John
John
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:%2328ZqyrMFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Sorry. M_DATE value type is nvarchar in database.
>|||Thank you John!..sql

Newbie Question

How do I get the unique fields from a select query without iterating. Is
this possible? My current statement is below. Thanks in advance.
"SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"Hi
SELECT DISTINCT DealNumber, [User] FROM DealingFinal
"xfd" <xfd@.xfd.com> wrote in message
news:u9rL%23udkFHA.3756@.TK2MSFTNGP15.phx.gbl...
> How do I get the unique fields from a select query without iterating. Is
> this possible? My current statement is below. Thanks in advance.
> "SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"
>

Newbie question

I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?I don't really use Visual Studio that much. Use Query Analyzer (SQL 2000)
or SQL Server Management Studio (SQL 2005).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe" <,> wrote in message
news:4401a01f$0$6979$ed2619ec@.ptn-nntp-reader02.plus.net...
I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?

Wednesday, March 21, 2012

newbie q: "contacting server to create web project" runs forever

hi, I'm using vstudio 2002, trying to create an C# asp web project, when I select these for a new project it says contacting server to create web project and never quits even after 1.5 hours. I saw in a similar post elsewhere they said the solution is to disable the firewalls - and I did that - and I have norton only, so that is not the issue. IIS is installed as well as SQL server, I can see it running. I have tested successfully a sql connection not long ago using access. Any thoughts? Thanks.
Dell 9300 1.6 GHZ
XP pro 2004 w/service pack2

Hi,

It seems the problem is that the VS does not find your local server.
(Reason my be, you have changed your machin IP somtime after Installing the VS)

Check the server name on new project dialog box and if needed change
it to localhost or the IP address of your system.

|||Did you ever get an answer to this? I have what looks like the identical problem.

newbie q: "contacting server to create web project" runs forever

hi, I'm using vstudio 2002, trying to create an C# asp web project, when I select these for a new project it says contacting server to create web project and never quits even after 1.5 hours. I saw in a similar post elsewhere they said the solution is to disable the firewalls - and I did that - and I have norton only, so that is not the issue. IIS is installed as well as SQL server, I can see it running. I have tested successfully a sql connection not long ago using access. Any thoughts? Thanks.
Dell 9300 1.6 GHZ
XP pro 2004 w/service pack2

Hi,

It seems the problem is that the VS does not find your local server.
(Reason my be, you have changed your machin IP somtime after Installing the VS)

Check the server name on new project dialog box and if needed change
it to localhost or the IP address of your system.

|||Did you ever get an answer to this? I have what looks like the identical problem.

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>> Anyone know what I'm doing wrong?
>>
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> > Hi
> >
> > Have you checked
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> >
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>
> >
> >
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
--
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
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

newbie pl/sql outputs select results

Hi,
I want to write a PL/SQL search engine that does some complex checking of various tables for a client's website. I could write it all in a PHP $sql = "SELECT ..." but I want to put it the sql into PL/SQL and am having trouble figuring out how PL/SQL outputs results.

Basically, I want to call the procedure with some keywords and have it return the results. Something like this:

create package jonsearch is

procedure getrecords(kw IN varchar, results OUT ?) is
begin
--complex sql goes here
end;

end jonsearch;

jonsearch.getrecords("keywords") would return the results just like select * from table would return results.

My trouble is that every tutorial I have read relies on dbms_output.put_line to output data. I want to output the results as a set, with an output variable, but I can't find a tutorial that shows how to use output variables.

Any help, even pointing me to a tutorial, would be great.

thanks,

JonIt turns out what I was looking for is called a REF CURSOR. I needed to create a package that defines this reference cursor, and then use that as my output variable.

This is described here, if anyone is interested:

http://www.oracle-base.com/Articles/8i/UsingRefCursorsToReturnRecordsets.asp|||Not understanding...

How are you wanting to output the variables? If you want to simply return the values, you will use the DBMS_OUTPUT package. If you are returning to Apache through the modplsql module, you will use the HTP and HTF packages.

As to REF Cursors, here is a Reader's Digest version:

http://www.dbforums.com/t974133.html

JoeB

Monday, March 19, 2012

Newbie needs help with timestamp calculation

I'm a newbie, so please be gentle

I have a table that has a timestamp column that I can reference, e.g.,

SELECT *
FROM mytable
WHERE RECORDTIME < {TS '2006-05-01 00:00:00.000' }

This type of selection works.

What I want to do now is select the rows where the timestamp is less than 30 days prior to the current date instead of hardcoding a timestamp every time. I'm trying to select anything older than 30 days.

SELECT *
FROM myfile
WHERE RECORDTIME < [ current date's timestamp] - 30 days

I don't even know where to start so any help is greatly appreciated.

Thanks in advance,

Robert

I think I have it. This seems to work.

SELECT *
FROM myfile
WHERE RECORDTIME < ( cast( getdate() as datetime) - day(30) )

Please advise if you see anything that would be an issue.

Thanks!

|||

I don't think that it works exactly as the spec 'today less 30 days'.

Consider these two different results:

select getdate() - day(30), getdate()
select dateadd(day, -30, getdate()), getdate()

-- -
2006-04-30 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)


-- --
2006-05-01 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)

/Kenneth

Newbie mystery

Try running the code below in QA

USE Northwind
--The quesy below produces the correct numbers.
SELECT CategoryID,(100*((COUNT(*)+.0)/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--The query below produces 0 values and are wrong.
SELECT CategoryID,(100*((COUNT(*))/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--This is the total
SELECT COUNT(*) AS TotalCount FROM Products
--The totals of the groupings
SELECT CategoryID,(COUNT(*)) AS Category_Total FROM Products GROUP BY CategoryID

I think I understand what happens with the above, but what I really want to know is there a good coding habit to prevent it. Some of our reports are very complex and an error could be missed.Dear,

In MSSQL, this is normal behaviour. When you divide an integer (count) by another integer (count), the result is an integer.

Example :

select 1/3 returns 0

select 1/cast(3 as numeric) returns .33333

That is just the way it is, and it is documented in BOL.

Regards,

CVM.

Monday, March 12, 2012

Newbie HELP (ON SQL)

Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lostohhh found it

SELECT Whatever, WhateverElse FROM TableName WHERE DateField >= DATEADD(d, -7, GETDATE())

Originally posted by webstep
Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lost|||If you put this at the top of your script:

declare @.date1 datetime, @.date2 datetime
set @.date2 = convert(varchar(11), getdate(), 111)
set @.date1 = convert(varchar(11), getdate()-7, 111)

...then you can just use @.date1 as your from date and @.date2 as your to date. You won't have to put in any dates.

Steve

Friday, March 9, 2012

Newbie Datetime Parameter problem

Hi, I have setup start date and end date parameters, however when my select gets the results from between the dates, if the dates are both set to today, no results are produced. I think its because I need to add a day to the end date parameter. How can I do this?

Thanks, Dan

Dan,

What you'll need to do is add one to your end date, like you said. You can do that by updating your SQL coding to something like this:

where ....
and DateField between @.StartDate and dateadd(day, 1, @.EndDate)
...

Hope this helps.

Jarret

|||

Thats perfect.

Thank you.

Saturday, February 25, 2012

NEWBIE - Parameter @EmployeeName to select a particular employee OR all employees

I have an operational parameter in my SQL select statement, @.EmployeeName,
that will filter timecard data for a particular employee. When I am running
the query and it prompts me for @.EmployeeName, I would like the option of
putting in * or [ALL] or something of that nature to return all the timecard
data.
Is there a wildcard that I can put in my parameter prompt to return all the
records?
I will greatly appreciate any help you can offer on the subject. Thank you,
-Dave> that will filter timecard data for a particular employee. When I am
running
> the query and it prompts me for @.EmployeeName, I would like the option of
> putting in * or [ALL] or something of that nature to return all the
timecard
> data.
What prompts you for this? Can you not leave the parameter empty? How is
the stored procedure coded?
Typically, you can implement optional parameters, and when you call the
procedure, you can either include that parameter or not.
http://www.aspfaq.com/2348
I think you are being slowed down by the GUI tool you are using, not the
nature of parameters. Also, keep in mind that * is only a wildcard in DOS,
Microsoft Access and a few other places. SQL Server uses % and _ ...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I am using the Query Builder in VS.NET 2003, not a stored procedure. Do I
need to use a stored procedure to achieve this result?
When I leave the parameter empty, I get no results for my query.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RLQapIMFHA.1308@.TK2MSFTNGP15.phx.gbl...
> running
> timecard
> What prompts you for this? Can you not leave the parameter empty? How is
> the stored procedure coded?
> Typically, you can implement optional parameters, and when you call the
> procedure, you can either include that parameter or not.
> http://www.aspfaq.com/2348
> I think you are being slowed down by the GUI tool you are using, not the
> nature of parameters. Also, keep in mind that * is only a wildcard in
> DOS,
> Microsoft Access and a few other places. SQL Server uses % and _ ...
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>

Newbie - How to update multiple rows with select statment

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

PamI am disappointed that all the experts here have no time for my basic question ...|||you need to write a query like

update a
set a.col = b.col
from yourtbl a, (your select query) b
where a.primarykey = b.primarykey|||Thanks for the reply.

Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0|||I'd suggest:DROP TABLE Pam24
GO

CREATE TABLE Pam24 (
Pam24id INT IDENTITY
CONSTRAINT XPKPam24
PRIMARY KEY (Pam24id)
, thingie VARCHAR(50) NULL
)

INSERT INTO Pam24 (thingie)
SELECT '123 Main Street' UNION ALL
SELECT NULL UNION ALL
SELECT '456Any Road' UNION ALL
SELECT '' UNION ALL
SELECT '789 My Place'

UPDATE Pam24
SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
WHERE thingie LIKE '[0-9]%'

SELECT *
FROM Pam24Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.

-PatP|||pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0

:)|||actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0

Thanks r937. It does answers my question.

Cheers

Monday, February 20, 2012

newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.