f



SQL queries on AS400 database

Hi,

Currently I use a program called DataSelect for running queries on my AS400
database.
From Clarion 5.5. I open a DDE channel and have the outcom of the query
running into a
dBase IV file, from where I can access the data in my Clarion applications.

I am sure there must be a way to run SQL based queries directly on the
AS400, using the ODBC
connection I have setup with the database, but I don't know how to set this
up and how to
ensure that the data ends in a table (TopSpeed file).

Anybody who could help me?

Thanks for your reply,

Gerard


0
7/7/2003 3:51:17 PM
comp.lang.clarion 5469 articles. 4 followers. Post Follow

3 Replies
630 Views

Similar Articles

[PageSpeed] 13

Hi,

> I am sure there must be a way to run SQL based queries directly on the
> AS400, using the ODBC
> connection I have setup with the database, but I don't know how to set this
> up and how to
> ensure that the data ends in a table (TopSpeed file).

Import the AS400 filedefinitions into your dictionary using the ODBC 
filedriver. Then you can either access these files in your application as you 
would access topspeed files (kind of), or you can copy the contents to a 
topspeedfile (e/g using a Process type procedure)

HTH

-- 
Arie Rens (RADventure B.V.)
Maarssen, the Netherlands
http://www.radventure.nl
SoftVelocity Distributor for the Benelux

0
reader8 (2)
7/7/2003 5:25:15 PM
try winsql lite.
it free.
it allow you to browse tru any database via odbc.
from winsql then you can decide which table to import.


"Arie Rens (RADventure B.V.)" <reader8@radventure.nl> wrote in message
news:VA.00000107.43058c20@radventure.nl...
> Hi,
>
> > I am sure there must be a way to run SQL based queries directly on the
> > AS400, using the ODBC
> > connection I have setup with the database, but I don't know how to set
this
> > up and how to
> > ensure that the data ends in a table (TopSpeed file).
>
> Import the AS400 filedefinitions into your dictionary using the ODBC
> filedriver. Then you can either access these files in your application as
you
> would access topspeed files (kind of), or you can copy the contents to a
> topspeedfile (e/g using a Process type procedure)
>
> HTH
>
> -- 
> Arie Rens (RADventure B.V.)
> Maarssen, the Netherlands
> http://www.radventure.nl
> SoftVelocity Distributor for the Benelux
>


0
dorst622 (31)
7/8/2003 1:59:14 AM
Use the ODBC driver you get with Client Access Express.

-- 

Greetings,

Steven Spierenburg
Panta Relatiebeheer BV
stevenNOSPAM@NOSPAMjikade.com
Remove the obvious anti-spam word from the e-mail address for reply's


He spoke with the wisdom that can only come from experience, like a guy who
went blind because he looked at a solar eclipse without one of those boxes
with a pinhole in it and now goes around the country speaking at high
schools about the dangers of looking at a solar eclipse without one of those
boxes with a pinhole in it.
   -- Priceless "Metaphors" From GCSE English Exam Essays

"Gerard Bijsterbosch" <gm.bijsterbosch@hccnet.nl> wrote in message
news:bec4tq$hq1$1@news.hccnet.nl...
> Hi,
>
> Currently I use a program called DataSelect for running queries on my
AS400
> database.
> From Clarion 5.5. I open a DDE channel and have the outcom of the query
> running into a
> dBase IV file, from where I can access the data in my Clarion
applications.
>
> I am sure there must be a way to run SQL based queries directly on the
> AS400, using the ODBC
> connection I have setup with the database, but I don't know how to set
this
> up and how to
> ensure that the data ends in a table (TopSpeed file).
>
> Anybody who could help me?
>
> Thanks for your reply,
>
> Gerard
>
>


0
7/8/2003 1:32:06 PM
Reply:

Similar Artilces:

SQL re-parsing on query executed against a remote database using database links
Can anyone help to understand why re-parsing occurs on a remote database (using database links), even though we are using a prepared statement on the local database: Scenario: ======== We have an schema (s1) on an Oracle 9i database with database links pointing to a schema (s2) on another Oracle 9i database. We have a SQL statement which joins tables from schemas s1 to tables in schema s2 by the use of database links (tablename@linkname). The query is executed against schema s1. The SQL statement is prepared and re-executed over many iterations (using bind variables - Java/jdbc). The statement is closed only when we have completed the iterations. For both databases, the optimizer mode is CHOOSE and ALL the tables have been analyzed. Results: ======= We have found that when we monitor / analyse the database, the parsing rate is high, which is impacting the performance of the process. NOTE: We also ran the same tests using Oracle 8i (for both databases) instead of 9i. In these tests the parsing rate was very low (zero). This is what I would have regarded as the expected behaviour. The same high parsing rate is also seen when both schemas reside on the same database (Oracle 9i), and database links are used to join tables from the 2 schemas. If however, the SQL query is amended to refer to the remote tables in schema s2 by schema.tablename rather than tablename@linkname, then the parsing rate is again very low (as expected) and the process runs much more quick...

How do you create an Access SQL Query to list all Database Objects, or Queries only, with their Size, Date Created and Date Modified
.... or any good Access book which would cover this subject. Thank you for your help. J.P. Most of the information you mention is contained in the hidden system table MSysObjects - with the exception of size, and I don't know any way of getting that information. You can write a query - using the standard query builder, if that's your preference - to retrieve what you want. You'll probably need to do some googling to find what some of the values used in this table represent, but it's readily available (don't have any links to hand on the computer I'm using at the moment). The key thing is to use the Options dialog box (from the Tools - Options menu in A2003 and earlier; or by right-clicking the Navigate bar and choosing Navigation options in A2007 (and later, I assume)) to make both System and Hidden objects visible. Until you do so, you can't browse the system tables (there are several of them) or use them in a query. HTH, Rob <jpr.charron@gmail.com> wrote in message news:5e29660d-e08e-432f-bba4-f8c1f281a39f@googlegroups.com... > ... or any good Access book which would cover this subject. > Thank you for your help. > J.P. On 25/02/2014 00:57:53, wrote: > ... or any good Access book which would cover this subject. > Thank you for your help. > J.P. > This will get you started Function GetDescription(ObjName As String, ObjType As Long) As String ' Get descri...

How to import from external SQL database to local SQL database
Hello everyone. I have two machines that have SQL Express on them. I have developed a SQL database with two tables, and a lot of data, on my laptop. I want to copy that database and make it available to my local desktop machine. How can I export or save the database on the laptop and then import it so that I can use it on the desktop? ...

SQL QUERY IN QUERY
Hi, I'm looking for a way to use a query inside another query in the following way: First I make my first query(Q1) and I save it as Q1. Then I want to reuse the result of that query inside query 2(Q2) It would have to look like: SELECT * FROM SOMETABLE, Q2 I tried different things but it did not work, saving the data first and then running does work but data is not always up to date and with frequent changes it is not efficient to run the query first, save it and run again. So if anyone has an idea. ps: I know about the procedures but I'd rather not use that. Parentheses are your friends! Google on "SQL SUBQUERY". -Philip michael.goossens@gmail.com wrote: > Hi, > > I'm looking for a way to use a query inside another query in the > following way: > > First I make my first query(Q1) and I save it as Q1. > Then I want to reuse the result of that query inside query 2(Q2) > > It would have to look like: > > SELECT * > FROM SOMETABLE, > Q2 > > I tried different things but it did not work, saving the data first > and then running does work but data is not always up to date and with > frequent changes it is not efficient to run the query first, save it > and run again. > > So if anyone has an idea. > > ps: I know about the procedures but I'd rather not use that. "Philip Wright" <p...

Update SQL 2000 Query (converting an Old Access 2k query to SQL)
Hello, I have the following query in Access 2000 that I need to convert to SQL 2000: UPDATE tblShoes, tblBoxes SET tblShoes.Laces1 = Null WHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND ((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B")) WITH OWNERACCESS OPTION; The ShoesID in the tblShoes table is an autonumber, however the records in the tblBoxes have the ShoesID converted to text. This query runs ok in Access, but when I try to run it in the SQL Server 2000 Query Analizer I get errors because of the comma in the "UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoes field named Laces1 to NULL for every record matching the ones in the tblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8 field. Any help would be greatly appreciated. JR ILCSP@NETZERO.NET wrote: > Hello, I have the following query in Access 2000 that I need to convert > to SQL 2000: > > UPDATE tblShoes, tblBoxes > SET tblShoes.Laces1 = Null > WHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND > ((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B")) > WITH OWNERACCESS OPTION; > > > The ShoesID in the tblShoes table is an autonumber, however the records > in the tblBoxes have the ShoesID converted to text. > Try: UPDATE tblShoes SET laces1 = NULL WHERE EXISTS (SELECT * FROM tblboxes AS B WHERE CAST(B.shoesid AS INT) = tblShoes.shoesid AND B.code...

how to use sql to query from different database
hi, we use db2 udb v8.1 on windows, i am trying to use federated database objects to create wrapper, even though i have update dbm cfg using federated yes, i still get error "the instance for the db is not enable for the specified action", do i miss sth else? Did you stop and restart the database manager? The federated parameter is not configurable online (that is, it is not dynamic). xixi wrote: > hi, we use db2 udb v8.1 on windows, i am trying to use federated > database objects to create wrapper, even though i have update dbm cfg > using federated yes, i still get error "the instance for the db is not > enable for the specified action", do i miss sth else? Maybe you also need do enable the db for multi-site-update. (not sure though) Check the cc/gui, you should find it pretty quickly. PM "xixi" <dai_xi@yahoo.com> a �crit dans le message de news:c0f33a17.0405271443.5eb9a87f@posting.google.com... > hi, we use db2 udb v8.1 on windows, i am trying to use federated > database objects to create wrapper, even though i have update dbm cfg > using federated yes, i still get error "the instance for the db is not > enable for the specified action", do i miss sth else? i need to query from different database on the same server, so after i create db2 wrapper ( i created under one of the database), should i do the same thing to the other database? as you instructed , i need to...

AS400 Queries with out a existing query
Wondered if you could jog my memory a bit. Its been years since I've worked on an AS400. I recall there is a way to issue a QUERY command to have it list the data with in a file with out creating an entire query. I thought it was a QUERY *N .... but that doesn't seem to work. Thanks for any help. Try: runqry *n libname/filename A Switzer wrote: > Wondered if you could jog my memory a bit. > > Its been years since I've worked on an AS400. > > I recall there is a way to issue a QUERY command to have it list the > data with in a file with out creating an ent...

Turning a query of a query into sql.
HI all, I have solved a problem using a join between a query and a table, however i need to use the sql, so must convert the qlery in to sql. Heres the example, tbl_Doctors tbl_Clinics Link table tbl_Doctors_Clnics So doctors can be workig for many clinics, and Clinics can have many doctors. For each user, i need to display a full list of the clincs with an indication of which ones they are a member of. So I make a query, (psuedo code below) qrtOne SELECT ClinicId,DoctorId FROM tbl_Clinics_Doctors INNER JOIN tbl_Doctors ON DoctorId = DoctorId WHERE Doctor Id = 1 That gives me a list of the clinics that doctor 1 is belong to. qryTwo SELECT ClinicId, DoctorId FROM qryOne LEFT OUTER JOIN tbl_Doctors_Clinics ON ClinicId=ClinicId. This gives me all the clinics, and nulls in the doctor fields, for those the doctor is not a member of. How woud a single sql script look for this ? Thanks in advance. Assuming Access 2000 or later, you may be able to use a SQL statement as a table in a query like this: SELECT Q.BookID, Q.BorrowerID FROM TableName As Q INNER JOIN (SELECT BookID, Max(DateOut) As S FROM TableName GROUP BY BookID) As T ON Q.BookId=T.BookId AND Q.DateOut = T.S Or some other kind of subquery might help: http://allenbrowne.com/subquery-01.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. &...

How to query database with multiple queries
I have the following fieldname in an Access_Table: Field 1 = Cust_ID (Primary key) Field 2 = Date Field 3 = Description Field 4 = Inv_No Field 5 = Amount My SQL Input syntax are: "Select distinct Cust_ID,Date,Description,Inv_No,Amount from Access_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date = Main.Date" It failed to generate the result I want,anyway My "Required" Output is: Cust_ID Date Description Inv_No >30DYS >60DYS 8000 21/05/2004 PC RAM 200 8000 26/06/2004 Modem 180 --------------------------------------------------------------- Total: 180 200 --------------------------------------------------------------- 8001 22/04/2004 Cable 50 8001 23/05/2004 HD 210 --------------------------------------------------------------- Total: 260 --------------------------------------------------------------- Grand Total: 180 460 can anyone help? Thanks From:Cady Steldyn *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Hi You seem to have posted 3 very similar requests. Please see reply to "SQL-Query Access by Date" John "Cady Steldyn" <dcartford@yahoo.ca> ...

Need to populate access table from sql database query
I have an access database with a table that contains two columns: Computer_Name,User_ID The table contains 600 computer names. I need to populate the User_ID column with data from an SQL database. I want to use VBA to step through each Computer_Name in the table, query the SQL database for the User_ID associated with Computer_Name, and populate the User_ID in the access table. I'm not an Access programmer, but this was thrown at me today. I need some guidance on how to code this. I have the connection string to the SQL database working, but I can't figure out how to get the data from the SQL query into the access table. Thanks. *** Sent via Developersdex http://www.developersdex.com *** attach to the SQL database from Access and then just write a query. When you get what you want, turn the query into an update or append query. I got the query side working, but I can't update the access table. Here's the code: -------------------------------- Private Sub UpdateUserName(ByVal PCName As String, ByVal UserName As String) Dim CurConn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim CurDB As Database Set CurDB = CurrentDb Set CurConn = New ADODB.Connection With CurConn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "data source=" & CurDB.Name .Open End With Set rst = New ADODB.Recordset rst.CursorType = adOpenDynamic rst.LockType = adLockOptimistic rst.Open "SELECT * FROM [Dis...

Trouble when submitting sql-query to MS Access Database
Hi, I have some problems when trying to submit a simple insertion statement to a MS Access Database. The reading is fine, but when I want to execute an insertion statement using the following code: public void SQLCommand(String sql) throws SQLException { connection = DriverManager.getConnection(database); Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); connection.close(); } gives the following error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45) at org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15) Could someone help me? kito Suggestion 1: The error is caused because you're referring to a table that does not exist. Try to run the query in a seperate query editor and verify that it does work. Also check that the string you pass as argument is correct, e.g. not concatenation that has destroyed the intended string. Suggestion 2: You probably need to add quotes in the executeQuery. Whenever you pass a string to a database try to inclose the statement with...

PROC SQL: Using local database when querying DSN
I need to use a local file to filter the results I am recieving in a PROC SQL statement on the fly. I have a database of ID's: Local.IDList My code so far, without joining Local.IDList is here: PROC SQL; CONNECT TO DB2(DSN=DSNServ user=&ID password=&PWD); CREATE TABLE Local.SampleSet AS SELECT * FROM CONNECTION TO DB2 ( SELECT S.ID, S.Date, S.CDType, A.Parp, K.PartNum FROM DD.FullList S LEFT OUTER JOIN DD.ParpList A ON S.CDSysKey = A.CDSysKey LEFT OUTER JOIN DD.PartList K ON A.PartIndex = K.PartIndex WHERE K.PartNum in ('01','07','09') ); %PUT &SQLXMSG; DISCONNECT FROM DB2; My problem is that the above pull is over 700 GB, so I cannot just pull it and later filter it. My other problem is that I do not have write access to the DSN Server, so I cannot upload my file to a table. Is what I ask even possible? IDList is too long to put into the SQL itself, as it is over 100,000 rows. One idea I thought of was a macro that would run the query once for each item in IDList, but I wouldn't know how to impliment that, and it doesn't seem very efficient either especially for the size of the data sets I am working with. Is what I ask even possible to do without blowing up my computer and/or temp space with the massive size of the tables I am querying against? Thanks for your time, Dan ...

calculating the size of CLOB in a database
Hi, rate19Lob is a CLOB. I want to know the total size of all of them in a table in my database. I have run up against a problem. Any ideas to get around it. Please excuse this post if it is simple, I'm new to this and have tried the approach because of what I have read in Oracle DBMS_LOB. Thank you Don SQL> SQL> SQL> SELECT SUM(DBMS_LOB.GETLENGTH(rate19Lob)) 2 FROM tbl_JNY2003; SELECT SUM(DBMS_LOB.GETLENGTH(rate19Lob)) * ERROR at line 1: ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553: PLS-553: character set name is not recognized SQL> SQL> SQL> SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production SQL> ...

Make Table Query to external ODBC SQL Server database
Hi All, Is it possible to create a Make Table query in access (2.0 and 2003) that creates the table into a SQL Server database? Following the steps: 1- Create New Query 2- Set Query Type as Make-Table query 3- Enter table name 4- Click "Another Database", click Browse, then I cannot see anything about data sources, ODBC, or SQL Server in the drop down box !!! only few file types like *.mdb, *.adp, ...etc. I already setup a ODBC data source. If this is not supported from the GUI, then what is the SQL syntax? in other words, SELECT * INTO Table1 IN "???" FROM Table2 Thanks for reading. Mourad I am fairly certain that you can't create a table in Sql Server from an Access mdb using ODBC. But you can certainly create a table on the server from an Access mdb using ADO. First make a reference to the Microsoft ActiveX Data Objects 2.5 (or higher) Library. From a code module (you have to be in a code module to do this) go to the Tools/References menu and select Microsoft ActiveX Data Objects 2.5 (or 2.6...) Library And here is a code sample: Sub CreateServerTable() Dim cmd As New ADODB.Command cmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=yourServer;Database=yourDB;UID=steve;password=abc;" '--or if you have windows integrated security set 'cmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=yourserver;Database=yourDB;Trusted_Connection=Yes" cmd.ActiveConnection.CursorLocation = adUseClient cmd.CommandTyp...

Re: PROC SQL: Using local database when querying DSN
Dan, I'm not sure if pass-through DB2 SQL can accept a macro variable; that = might be the only way to handle it if you don't have any other access to = DB2. =20 You may want to try a small macro variable like this: %let myvar=3D'001' '002'; /* where you put in some of the ID's that you have */ Then you could use Proc SQL to pull maybe 500 ID's at a time into a = macro variable and then put your proc sql here into a macro and call it. = Do you have any access to the machine where DB2 is located? For = instance, if it is TSO, a much preferred approach would be to load your = temp table into a session table in a PL/1 or COBOL program and then join = by the rest of the query, creating a file to output and then download, = but you'd have to be on the machine that runs DB2 to run that- maybe = speak to your database people about that. Write back if a macro variable will work- then we could see about = pulling 500 or so items from your list at a time- but that would still = make about 200 calls; pretty difficult still. -Mary ----- Original Message -----=20 From: Dan=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Tuesday, January 22, 2008 2:27 PM Subject: PROC SQL: Using local database when querying DSN I need to use a local file to filter the results I am recieving in a = PROC SQL statement on the fly. I have a database of ID's: Local.IDList My code so far, without joining Local.IDList is here: PROC SQL; CONNECT ...

Is this possible? please help (MS access query of sql database)
I work for an organisation that uses a bespoke document imaging system, the database of which is an MS sql server. We have MS Access and already use it for some querying of the database. The database comprises a large number of distinct cases, which are differentiated by case reference numbers, in one field (table?) of the database. Each of these cases may have many documents associated with it, denoted by the reference number, and these documents may be "new", "pending" or "complete" shown in another data field. We need to know how many cases have work outstanding on them. Our problem is that our bespoke software will only count the number of documents of each status, and not the cases. Is it possible to design an MS Access query which will count the number of different reference numbers which have any "new" documents associated, but wont count each case more than once? I am reasonably computer-savvy, I just don't know Access or SQL.. If I know it is possible, I don't mind putting in the effort to find out how. I just don't want to waste time barking up the wrong tree ;-) Of course any advice about how this would be achieved, such as pointers to the right parts of the MS Access helpfiles, or to relevant websites would be greatly appreciated. Some quick code would be even better... Also, if there is any 3rd-party software which could easily do this, I need help discovering it...I have looked long and hard, but don't k...

capture and save running sql query to database table (for logging)
Hey, This may sound odd, but is there anyway to catch the current or just run query from inside a trigger? Kinda like how profiler displays the query just as you've run it, along with all the statistical data... But I'm just looking to capture the query itself and save it in a logging table. I just need to save an executing query in certain circumstances (if detected an attempted sql injection attack) for logging purposes. On MS SQL Server 2005 Hope someone can help... Thanks! Gear=F3id You may not be able to do that in a trigger. In SQL Server 2005 you can get the current statement with this query: SELECT [text] FROM sys.dm_exec_requests AS R CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S WHERE session_id = @@SPID; However, running this inside a trigger returns the SQL statement to create the trigger. The only statement that will actually output the current query is: DBCC INPUTBUFFER(@@SPID); But you cannot really store the result set from DBCC to a table. One way to accomplish what you need is to set up a server side trace. BTW, if the purpose of this is to prevent SQL injection attacks, you can go the other way around and fix the code to prevent them rather than audit. HTH, Plamen Ratchev http://www.SQLStudio.com Thanks for the reply, I'll take a look into your suggestions. But yeah - the injections are happening from older, badly written classic ASP pages with lots of dynamic sql, which we're looking to rewrite and fix up, an...

New Database Query Tool
CHARONWARE, s. r. o., http://www.charonware.com, announces release of a perfectly new database query tool Foxy SQL Pro 1.0. Foxy SQL Pro is a fast-performing and easy-to-use tool allowing database professionals to execute SQL commands and queries against a large number of databases, see and store the results and many more. It is mainly focused on a possibility to parse complete SQL script into each individual commands that can be then executed according to the user's selection. If you work with more than one database and have to handle several client applications for each...

Assistance sought re MS Query interrogation of SQL database
I am using MS Query to interrogate the firm's Practice Management Database, which, I am told, is SQL, and return the data to an Excel 2000 spreadsheet. This part of the exercise is successful. The data is retrieved and the desired filters work fine. However, the data then needs to have additional information added to it for subsequent reporting purposes. Unfortunately, I have been unsuccessful in ensuring that the additional data remains "attached" to the retrieved data, as, when the data is refreshed, new rows may inserted amongst the retrieved data, but the manually added data remains static. Is there a way to ensure that any rows inserted by the refreshing of the data also move the additional data? I hope I have made myself clear here. Please forgive me if I am overlooking something blindingly obvious or that has been covered before. Any assistance would be much appreciated. Jamie Roth wrote: > I am using MS Query to interrogate the firm's Practice Management > Database, which, I am told, is SQL, and return the data to an Excel > 2000 spreadsheet. > > This part of the exercise is successful. The data is retrieved and > the desired filters work fine. > > However, the data then needs to have additional information added to > it for subsequent reporting purposes. Unfortunately, I have been > unsuccessful in ensuring that the additional data remains "attached" > to the...

Re: PROC SQL: Using local database when querying DSN #4
I had great go rounds with DB2 administrators over this... Explained in the context of the alternative suggestions (involving repeated calls to DB2 for each lookup from your local table --which really will make them grumpy) I was finally able to convince them that we needed a user with limited write access so SAS could create temporary tables and then allow DB2 to do its thing: process the query (this is, after all, what DB2 is for right?) You are using pass-through, so I'm guessing you do not have SAS/ACCESS for DB2 available. If you do a lot of this type of querying, you should talk to whoever is in charge of licensing about getting it. In the grand scheme of things, you already have a large investment in DB2 and SAS, so paying for the tools to use them efficiently together seems like a no brainer. In shameless self promotion, see http://aipl.arsusda.gov/publish/other/2005/nesug_ahs.pdf for ways to use ACCESS. For specifics on your platform connections you can contact me directly and I'll offer whatever I learned from painful experience... Ashley Sanders Department of Animal Sciences PO Box 110910 Gainesville, FL 32611-0910 asanders@ufl.edu (352) 328-1559 (cellular) >>-----Original Message----- >>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >>Mary >>Sent: Tuesday, January 22, 2008 4:21 PM >>To: SAS-L@LISTSERV.UGA.EDU >>Subject: Re: PROC SQL: Using local database when querying DSN >> >>Dan...

Re: PROC SQL: Using local database when querying DSN #2
On Tue, 22 Jan 2008 15:21:00 -0600, Mary <mlhoward@AVALON.NET> wrote: >Dan, > >I'm not sure if pass-through DB2 SQL can accept a macro variable; that might be the only way to handle it if you don't have any other access to DB2. > >You may want to try a small macro variable like this: > >%let myvar='001' '002'; >/* where you put in some of the ID's that you have */ >Then you could use Proc SQL to pull maybe 500 ID's at a time into a macro variable and then put your proc sql here into a macro and call it. > >Do you have any access to the machine where DB2 is located? For instance, if it is TSO, a much preferred approach would be to load your temp table into a session table in a PL/1 or COBOL program and then join by the rest of the query, creating a file to output and then download, but you'd have to be on the machine that runs DB2 to run that- maybe speak to your database people about that. > >Write back if a macro variable will work- then we could see about pulling 500 or so items from your list at a time- but that would still make about 200 calls; pretty difficult still. > >-Mary > ----- Original Message ----- > From: Dan > To: SAS-L@LISTSERV.UGA.EDU > Sent: Tuesday, January 22, 2008 2:27 PM > Subject: PROC SQL: Using local database when querying DSN > > > I need to use a local file to filter the results I am recieving in a PROC > SQL statement on the fl...

Re: PROC SQL: Using local database when querying DSN #5
Hi, Dan,=20 Howard Schreier gave you code to get your variables into macro = variables- here's how to take it from there (you'd substitute your SQL = in the second SQL of the run_pulls code below. Also make sure before = you run that the quote around the id does not contain spaces: Good luck !!! -Mary data example; do _n_ =3D 21 to 100000; id =3D put(_n_,z5.); output; end; run; data listset(drop =3D id); run + 1; length list $ 20000; do _n_ =3D 1 to 500 until(nomore); set example end=3Dnomore; list =3D catx( ',' , list , quote(id) ); end; run; data allset; run; =20 %macro run_pulls(model_count); =20 proc sql noprint; select list into :idlist from listset where run =3D&model_count; quit; =20 proc sql; create table tempset as select id from example=20 where id in (&idlist); quit; run; =20 data allset; set allset tempset; run; =20 %Mend run_pulls; =20 %Macro docalls; %Local I; proc sql noprint; select count(*) into :model_count from listset; quit; %Do I =3D 1 %To &model_count ; %run_pulls(&i); %End ; %Mend docalls ; =20 %docalls; =20 run; ----- Original Message -----=20 From: Dan=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Tuesday, January 22, 2008 2:27 PM Subject: PROC SQL: Using local database when querying DSN I need to use a local file to filter the results I am recieving in a = PROC SQL...

search an Free SQL QUERY TOOL Running on Tomcat with Many DataBase
Hi, I search an Free SQL QUERY TOOL Running on Tomcat with Many DataBase ? Regards Philippe Poster une r�action I could suggest Scribe (www.reportgenerator.org), but it is not free. <pcouas@infodev.fr> wrote in message news:af5681c8.0504090027.3ec84675@posting.google.com... > Hi, > > I search an Free SQL QUERY TOOL Running on Tomcat with Many DataBase ? > Regards > Philippe > Poster une r�action ...

Re: PROC SQL: Using local database when querying DSN #7
Dan,=20 Oh, and I was very dubious that you could get that to work when you = first asked.... That is so great that it worked!!! Thank you SO MUCH for letting us = know! -Mary ----- Original Message -----=20 From: Dan Colejawigoman=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Monday, February 04, 2008 10:09 AM Subject: Re: PROC SQL: Using local database when querying DSN Thank you all for your help! Your solutions worked great! Howard and Mary your code worked great and did exactly as I wanted it = to! Thanks Ashley for the suggestions as well, I'm and attempting to find = out who my DB2 maintainers are so that potentially I can work with them to improve our department's efficienty when querying, as it would indeed = be to both our benifits. Thanks again! ...

Web resources about - SQL queries on AS400 database - comp.lang.clarion

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Fearing no punishment, Denver cops abuse crime databases for personal gain
(credit: Noel Hidalgo ) Denver police officers performed searches on state and federal criminal justice databases that were not work-related ...

Open source database improves protection and performance
Most enterprises rely on databases in some form or another, but they can be vulnerable to attack from people looking to steal information. They ...

Seattle’s Tableau Software snaps up database-computing startup in Germany
Seattle’s Tableau Software has acquired HyPer, a database-computing startup that spun out of research at a university in Munich, Germany. As ...

Amazon Web Services Announces that over 1,000 Databases Have Migrated to AWS since January 1, 2016
Amazon Web Services, Inc. (AWS), today announced that the AWS Database Migration Service is now generally available.

Taiwan launches database on areas vulnerable to quake damage
BEIJING (AP) — Earthquake-prone Taiwan is launching a database to inform residents which areas might be susceptible to creating potentially catastrophic ...

Interior Dept Spent $15 Million On A Crime Database That Doesn’t Work
Interior Dept Spent $15 Million On A Crime Database That Doesn’t Work

Microsoft's SQL database software now runs on Linux
Remember when Steve Ballmer likened Linux to cancer, and the notion of Microsoft courting the open source crowd was virtually unimaginable? The ...

Crate Raises $4M for New Container Database Technology
The founder of Docker Inc. invests in new technology that provides a very different and very distributed way to build a container database.

Resources last updated: 3/23/2016 3:49:54 PM