What is the best way to store large amounts of data in python, given one (or two) 500,000 item+ dictionary used for undirected graph searching? I've been considering a few options such as storing the data as XML: <key name="a"> <value data="1" /> <value data="2" /> </key> <key name="b"> ... or in a python file for direct acce...
Hi, I've got a funny question for you, sql gurus out there (or maybe inhere?). I got this habit when I need to construct a query at runtime. In order to avoid multiple 'if' checks I just go and write 'where true '. Then the rest is another part of the clause in the form, of course of ' and true ...' and continues with useful filtering. ...
Hi all, I'm using oracle(10). I've got two tables as follows: Table1 (uniq rows): ID AMOUNT DATE Table2: ID AMOUNT1 AMOUNT2 ...AMOUNTN DATE Table2 is connected many to one to Table1 connected via ID. What I need is update-ing Table1.DATE with: the last (earliest) date from Table2 where Table1.AMOUNT - SUM(Table2.AMOUNT1...
How do I write a query that outputs the row number as a column? This is DB2 SQL on an iSeries. eg if I have table Beatles: John Paul George Ringo and I want to write a statement, without writing a procedure or view if possible, that gives me 1 John 2 Paul 3 George 4 Ringo ...
Hi everyone. I have the following table in MSSQL2005 id | business_key | result 1 | 1 | 0 2 | 1 | 1 3 | 2 | 1 4 | 3 | 1 5 | 4 | 1 6 | 4 | 0 And now i want to group based on the business_key returning the complete entry with the highest id. So my expected result is: business_key | result 1 | 1 2 | 1 3 | 1 4 | 0 I bet that there is ...
Is a select * from myView faster than the query itself to create the view (in order to have the same resultSet): select * from ([query to create same resultSet as myView]) ? It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query. Any help would be appreciated! ...
In a C++ application that can use just about any relational database, what would be the best way of generating queries that can be easily extended to allow for a database engine's eccentricities? In other words, the code may need to retrieve data in a way that is not consistent among the various database engines. What's the best way ...
Hi all, I have a table like this (Oracle, 10) Account Bookdate Amount 1 20080101 100 1 20080102 101 2 20080102 200 1 20080103 -200 ... What I need is new table grouped by Account order by Account asc and Bookdate asc with a running total field, like this: Acc...
I am trying to understand how does joins work internally. What will be the difference between the way in which the following two queries would run? For example (A) Select * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID FULL JOIN TABLE3 ON TABLE1.ID = TABLE3.ID And (B) Select * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = T...
I am trying to filter items with a stored procedure using like. The column is a varchar(15). The items I am trying to filter have square brackets in the name. For example: WC[R]S123456. If I do a LIKE 'WC[R]S123456' it will not return anything. I found some information on using the ESCAPE keyword with LIKE but I do not understand how...
Just a quick database design question: Do you ALWAYS use an ID field in EVERY table, or just most of them? Clearly most of your tables will benefit, but are there ever tables that you might not want to use an ID field? For example, I want to add the ability to add tags to objects in another table (foo). So I've got a table FooTag wi...
I started with a query: SELECT strip.name as strip, character.name as character from strips, characters, appearances where strips.id = appearances.strip_id and characters.id = appearances.character.id and appearances.date in (...) Which yielded me some results: strip | character 'Calvin & Hobbes' | 'Calvi...
We have a SQL query that pulls a large number of fields from many tables/views from a database. We need to put a spec together for integration with a 3rd party, what is the quickest way to compile the data types of the result set? Clarifications: There are 25+ tables/views involved, so functions at the table level will still be cumb...
If I have a list of items, say apples pairs pomegranites and I want to identify any that don't exist in the 'fruit' column in an SQL DB table. Fast performance is the main concern. Needs to be portable over different SQL implementations. The input list could contain an arbitrary number of entries. I can think of a few ways to do i...
With xml like: <a> <b> <c>1</c> <c>2</c> </c>3</c> </b> I'm trying to create an xpath expression (for a postgresql query) that will return if is a particular value and not that is all three values. What I currently have (which does not work) is: select * from someTable where xpath ('//uim:a/text()', job, ARRAY[ ARRAY[...
Two tables: COURSE_ROSTER - contains COURSE_ID as foreign key to COURSES USER_ID as field I need to insert into COURSES COURSES - contains COURSE_ID as primary key INSTRUCTOR_ID as field that needs to be updated with USER_ID field from COURSE_ROSTER What would the UPDATE sql syntax be? I am trying this, but no good... I'm miss...
Why would someone use numeric(12, 0) datatype for a simple integer ID column? If you have a reason why this is better than int or bigint I would like to hear it. We are not doing any math on this column, it is simply an ID used for foreign key linking. I am compiling a list of programming errors and performance issues about a product, ...
So, I have a method that performs a parametrised LIKE query. The method takes in the search parameter/value in and then it is added to the command ready for the query. It is not working. It should work, and when I code the value to search for directly into the SQL string, sans parametrisation, it does work! When I have it as a paramete...
I'm currently writing a class to handle all database-activity in my application, and so I've come to the method that performs UPDATE-queries. As of now I'm returning and displaying the content of the commandtext to check it, and it seems fine: UPDATE news SET title = @title, newsContent = @newsContent, excerpt = @excerpt, date = @date,...
Hi everyone, can someone tell me why the following behavior occurs (Oracle 10.2): SQL> create table part_test ( i int primary key, d date ) partition by range (d) (partition part_test_1 values less than (to_date(' 2 3 4 5 1980-01-01', 'yyyy-mm-dd'))); create public synonym part_test for part_test; select obj...