Weird SQL Question
 

Ok, so my boss handed me a spreadsheet that contains about 1,000 records as
retrieved by a SQL query.

My boss now wants me to run a query on ONLY the records given to me in the
spreadsheet. He wants me to grab two more fields of data for each of the
records in the sheet.

Naturally, my first question to my boss is, "Why don't you just give me the
SQL code that was used for that query, and I'll just add a string or two to
grab those additional fields? ...And then we'll run the new SQL?"

My boss says, "Isn't there a way to run a query on just the records I've
given you? Someone in another department has been able to do that before."

I've never heard of this. Can anyone provide insight into how I might
accomplish what my boss wants before I tell him he's full of crap?

Weird SQL Question
 

I don't know for sure whether he's "full of crap", but my initial impression
is yes, he is.

For sure he's trying to do things the hard way. There'e no way that I know of
to determine with absolute accuracy what the criteria were for the original
query by looking at the results, then create a new query using the same specs.
Even if there were, it's a waste of your time.

Of course you could run one new query for each record in the existing 1000,
specifying the data you have as the criteria, but that's 1000 queries. Hardly
efficient.


On Wed, 10 Nov 2004 12:54:07 -0800, simsjr
wrote:

Ok, so my boss handed me a spreadsheet that contains about 1,000 records as
retrieved by a SQL query.

My boss now wants me to run a query on ONLY the records given to me in the
spreadsheet. He wants me to grab two more fields of data for each of the
records in the sheet.

Naturally, my first question to my boss is, "Why don't you just give me the
SQL code that was used for that query, and I'll just add a string or two to
grab those additional fields? ...And then we'll run the new SQL?"

My boss says, "Isn't there a way to run a query on just the records I've
given you? Someone in another department has been able to do that before."

I've never heard of this. Can anyone provide insight into how I might
accomplish what my boss wants before I tell him he's full of crap?

Weird SQL Question
 

You could build a query based upon that data, but it would be inefficient,
and the development time is just wasted.

Go back and argue the case for adapting the existing code, logically and
firm.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"simsjr" wrote in message
...
Ok, so my boss handed me a spreadsheet that contains about 1,000 records
as
retrieved by a SQL query.

My boss now wants me to run a query on ONLY the records given to me in the
spreadsheet. He wants me to grab two more fields of data for each of the
records in the sheet.

Naturally, my first question to my boss is, "Why don't you just give me
the
SQL code that was used for that query, and I'll just add a string or two
to
grab those additional fields? ...And then we'll run the new SQL?"

My boss says, "Isn't there a way to run a query on just the records I've
given you? Someone in another department has been able to do that before."

I've never heard of this. Can anyone provide insight into how I might
accomplish what my boss wants before I tell him he's full of crap?

Weird SQL Question
 

Hi simsr,

simsjr wrote:
Ok, so my boss handed me a spreadsheet that contains about 1,000
records as retrieved by a SQL query.

My boss now wants me to run a query on ONLY the records given to me
in the spreadsheet. He wants me to grab two more fields of data for
each of the records in the sheet.

Naturally, my first question to my boss is, "Why don't you just give
me the SQL code that was used for that query, and I'll just add a
string or two to grab those additional fields? ...And then we'll run
the new SQL?"

My boss says, "Isn't there a way to run a query on just the records
I've given you? Someone in another department has been able to do
that before."

I've never heard of this. Can anyone provide insight into how I might
accomplish what my boss wants before I tell him he's full of crap?

As others have noted, I would definitely tell your boss you need the query
in order to make efficient use of your time. If that doesn't work, you may
be able to do this. Assuming the data he gave you has a column that is
unique. If so, you could select the necessary fields from the necessary
table(s) and put this in the WHERE clause:

WHERE <fieldname IN (<value1, <value2, <value3, ..., <valueN)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Weird SQL Question
 

simsjr wrote ...

my boss handed me a spreadsheet that contains about 1,000 records as
retrieved by a SQL query.

My boss now wants me to run a query on ONLY the records given to me in the
spreadsheet. He wants me to grab two more fields of data for each of the
records in the sheet.
My boss says, "Someone in another department has been able to do that before."

Can anyone provide insight into how I might
accomplish what my boss wants before I tell him he's full of crap?

One of Homer's tips: 'Ooooh! Good idea, boss!'

You can use a Jet (MS Access) pass-through query to create a JOIN
between Excel data and an odbc data source using a common key. Things
your boss will need to provide include a logon to the database, user
account with appropriate permissions, location of the database, schema
details, play time... erm, I mean, project time. In other words, you
get the keys to the database, additional knowledge and skills, kudos,
pay raise, ...

No, on second thoughts, go with the 'full of crap' angle.

Jamie.

--

Weird SQL Question
 

Hi simsjr:

This stuff isn't rocket science, but you have to think about it from time to
time.
Run the Schema on the DB, from that it should be pretty easy to see what
tables the SQL query used and the fields included and those not included. The
Schema can/will also show the stored Procedure (Server) stored Queries
(access). Most or at least my preference is to store the queries/procedures
on/with the DB and call it.
So, with a little dab of code you could call the query and in minutes
reproduce the spreadsheet your boss gave you. A sound like your boss is
testing you and your failing and the guy in the other department is passing.

If you don't have the code handy to run the schema post back and I'll post
you an example

Good Luck
TK

"simsjr" wrote:

Ok, so my boss handed me a spreadsheet that contains about 1,000 records as
retrieved by a SQL query.

My boss now wants me to run a query on ONLY the records given to me in the
spreadsheet. He wants me to grab two more fields of data for each of the
records in the sheet.

Ther are not two more fields of data per record a record id the record


Naturally, my first question to my boss is, "Why don't you just give me the
SQL code that was used for that query, and I'll just add a string or two to
grab those additional fields? ...And then we'll run the new SQL?"

My boss says, "Isn't there a way to run a query on just the records I've
given you? Someone in another department has been able to do that before."

I've never heard of this. Can anyone provide insight into how I might
accomplish what my boss wants before I tell him he's full of crap?

Weird SQL Question
 

"TK" wrote ...

The Schema can/will also show the stored Procedure (Server) stored
Queries (access).

As you are an ADO advocate (plus I haven't had any caffeine since my
espresso breakfast <g) I won't post the VBA, but try the following
against a ADO OLEDB connection to a Jet 4.0 .mdb (MS Access) database:

CREATE TABLE MyTestTable (
MyKeyCol INTEGER NOT NULL PRIMARY KEY,
MyDataCol VARCHAR(255) NOT NULL
)
;
CREATE VIEW TestResults AS
SELECT MyKeyCol, MyDataCol
FROM MyTestTable
;
CREATE PROCEDURE TestProc (
KeyValue INTEGER
) AS
SELECT MyKeyCol, MyDataCol
FROM MyTestTable
WHERE MyKeyCol = KeyValue
ORDER BY MyKeyCol
;

Then use the Connection object's OpenSchema object using adSchemaViews
and you'll get a row for TestResults, then using adSchemaProcedures
and you'll get a row for TestProc.

This demonstrates that Jet (MS Access) also can/will distinguish
stored procedures from other stored Query objects.

Jamie.

--

Weird SQL Question
 

Is the spreadsheet that you were given just filled with values or is th
query still embedded there? To check, Right-click one of the rows fro
one of the columns of your dataset and see if "Edit Query" is a
option... if its there, then you're set.

If its not, then I would say the spreadsheet that your boss had give
the previous person DID still have the query embedded and was not jus
data values. This seems to be the most likely way the guy/gal coul
have done what your boss says he/she did

--
ob3ron0
-----------------------------------------------------------------------
ob3ron02's Profile: http://www.excelforum.com/member.php...fo&userid=1545
View this thread: http://www.excelforum.com/showthread.php?threadid=27718

Weird SQL Question
 

Hi Jamie:

Jamie wrote:
This demonstrates that Jet (MS Access) also can/will distinguish
stored procedures from other stored Query objects.

You have miss read my post:

I refered to an Access Query.
I refer to an Access Query as a stored object that returns a rs or
performs some action on the DB, a user defined procedure.
I refer to that same Server object as a store Procedure.
These terms are specific.
ADO returns a recordset
ADOMD returns a Cellset
ADO.Net returns a Dataset
and bla bla bla
You seem to use these terms interchangeable.

If you think they are try to enter:
..CommandType = adCmdStoredProc
to call an excess Query
or conversely
..CommandType = adCmdUnknown
to call a Server Store Procedure

ADO will promptly inform you of your error.

Further:

This demonstrates that Jet (MS Access) also can/will distinguish
stored procedures from other stored Query objects.

I agree.

The last article I read (ADO) OpenSchema actually had 31 QueryType
Methods however, providers are only required to support adSchemaTables
adSchemaColumns and adSchemaType by OLE DB specifications.

And yes I know there is something missing in a persons life
that would think this stuff is note worthy or interesting.

Good Luck
TK


"Jamie Collins" wrote:

"TK" wrote ...

The Schema can/will also show the stored Procedure (Server) stored
Queries (access).

As you are an ADO advocate (plus I haven't had any caffeine since my
espresso breakfast <g) I won't post the VBA, but try the following
against a ADO OLEDB connection to a Jet 4.0 .mdb (MS Access) database:

CREATE TABLE MyTestTable (
MyKeyCol INTEGER NOT NULL PRIMARY KEY,
MyDataCol VARCHAR(255) NOT NULL
)
;
CREATE VIEW TestResults AS
SELECT MyKeyCol, MyDataCol
FROM MyTestTable
;
CREATE PROCEDURE TestProc (
KeyValue INTEGER
) AS
SELECT MyKeyCol, MyDataCol
FROM MyTestTable
WHERE MyKeyCol = KeyValue
ORDER BY MyKeyCol
;

Then use the Connection object's OpenSchema object using adSchemaViews
and you'll get a row for TestResults, then using adSchemaProcedures
and you'll get a row for TestProc.

This demonstrates that Jet (MS Access) also can/will distinguish
stored procedures from other stored Query objects.

Jamie.

--

Weird SQL Question
 

"TK" wrote

You have miss read my post
I refer to an Access Query as a stored object that returns a
rs or performs some action on the DB

My point was, the OLE DB provider for Jet 4.0 makes further
distinctions e.g. between Query objects that are exposed as VIEWs and
Query objects that are exposed as PROCEDUREs.

...a user defined procedure.

I see that as something different, something outside of the schema
e.g. a custom Function in a VBA module (MS Access), a User-Defined
Function or Extended Stored Procedure (SQL Server). Semantics, I know,
but that's what we are discussing, isn't it <g?

try to enter:
.CommandType = adCmdStoredProc
to call an excess Query
or conversely
.CommandType = adCmdUnknown
to call a Server Store Procedure

ADO will promptly inform you of your error.

I don't get what you mean. Using my earlier Jet (MS Access) stored
Query, TestProc, called using:

Set oRs = oConn.Execute("TestProc 1", , adCmdStoredProc)

And this simple SQL Server (pubs) procedu

CREATE PROCEDURE TestMSSqlProc (
@pubdate DATETIME
) AS
SELECT title FROM titles
WHERE pubdate @pubdate;

called using:

Set oRs = oConn.Execute("TestMSSqlProc '1991-06-30'", ,
adCmdUnknown)

Both work fine with no errors. I don't use a Command object (more
overhead, no gain) but if I did the results would be the same. Looks
like I missed the point again :-(

I know there is something missing in a persons life
that would think this stuff is note worthy or interesting

OK, treading carefully here... but me being one (as you know) who
*does* find such detail noteworthy, something you said in your post
has raised my interest and you may be able to help me out with the
answer. However, I detect *you* may not be interested. If you'd rather
go offline, please email me jamie spot collins at breathe spot com or
feel free to ignore.

Many thanks,
Jamie.

--

Weird SQL Question
 

"Jamie Collins" wrote:

My point was, the OLE DB provider for Jet 4.0 makes further
distinctions e.g. between Query objects that are exposed as VIEWs and
Query objects that are exposed as PROCEDUREs.

I agreed with that.

The last article I read (ADO) OpenSchema actually has 31
QueryType valus

I see that as something different, something outside of the schema
e.g. a custom Function in a VBA module (MS Access), a User-Defined
Function or Extended Stored Procedure (SQL Server). Semantics, I know,
but that's what we are discussing, isn't it <g?

My thoughts are if it is exposed by openSchema it is part of the Schema.

try to enter:
.CommandType = adCmdStoredProc
to call an excess Query
or conversely
.CommandType = adCmdUnknown
to call a Server Store Procedure

ADO will promptly inform you of your error.

I stand corrected; I reversed the two commands on
both the Command Object and the Connection Object
with no errors. I guess I should take the book "Mastering
DB Programming with VB6" back. Possible ADO may have
started to treat the two the same. I know I have read that
in more than one publication.
But either the author's are incorrect or it is not the preferred
usage or ADO has updated, but it does work.


I know there is something missing in a persons life
that would think this stuff is note worthy or interesting

OK, treading carefully here... but me being one (as you know) who
*does* find such detail noteworthy, something you said in your post
has raised my interest and you may be able to help me out with the
answer. However, I detect *you* may not be interested.

Obviously I think it is interesting but you have to realise there are
people that would put it on the same interest level as pain drying
but then they would not be reading this anyway.

something you said in your post
has raised my interest and you may be able to help me out with the
answer. However, I detect *you* may not be interested.

Of course if I can, as I'm confident you would assist me.

Good Luck
TK