I have a set of files in a library on an AS/400/iSeries/System-I/iSeries/whatever-IBM-wants-me-to-call-it-these-days which I do not have DDS for (created in SQL I gather) but to which I would like to add field descriptions. I cannot find a way to do this on the 400. Does anyone know how I can add field description? Is directly updat...
When LEFT JOINing tables in a SQL query, sometimes I need to reference multiple tables in the ON clause. For example: SELECT p.Name, j.Job, s.Salary FROM PeopleTable p, JobTable j LEFT JOIN SalaryTable s ON s.PeopleID=p.PeopleID AND s.JobID=j.JobID However, the above would give this error: SQL Error: The multi-part identifier "p.Peop...
My understanding is that in order to maintain source-compatibility, Java never introduces new methods to public interfaces, as that breaks existing clients implementing the interfaces. Java Release notes states In general, the policy is as follows, except for any incompatibilities listed further below: Maintenance releas...
Can we write a query like INSER TNTO Customers SELECT * FROM OPENROWSET( 'SQLNCLI', Remote Server Settings , 'SELECT * FROM Customers) Remote Server is on some other server over internet public IP. Will this be faster compared to SqlBulkCopy? I need to create a slave database which can regularly copy data from server accessible thr...
In the course of a complex database structure, I need to provide the user with a means of editing data stored in a series of tables. Although all the data types are the same, they don't line up 1:1 in their names. To alleviate this, I created a query that maps the original names (which come from outside reports) to the internally-used ...
I am learning SQL and am trying to learn JOINs this week. I have gotten to the level where I can do three table joins, similar to a lot of examples I've seen. I'm still trying to figure out the tiny details of how things work. All the examples I've seen of three table joins use INNER JOINS only. What about LEFT and RIGHT JOINs? Do ...
I have two sql server tables with the same structure. In a stored procedure I have a Select from the first table. Occasionally I want to select from the second table as well based on a passed in parameter. I would like a way to do this without resorting to using dynamic sql or temporary tables. ...
My SQL Server Management Studio suddenly went case sensitive on me. The database and server are both set to case insensitive SQL_Latin1_General_CP1_CI_AS I run queries like Select * From mytable and I get "invalid object name" but if i run select * from MyTable i get data!! I created a new database and created a dummy table a...
I have a table in SQL Server consisting of two columns that track how many users are logged into a system at a particular interval. The first column is Time15, which is the time rounded to the nearest 15 minute interval (datetime). The second column is UserCount, which is the number of users logged into the system during that 15 minut...
Is there a way to find out a list of the SQL Servers that a windows authenticated ASP.NET user on an intranet site has access to? I'd like to list them in a dropdown. ...
Hi, I have 4 tables for each month; oct, dec, feb, apr. They have the same schema. I need to extract the data from all the tables using the same query and grouping it for one attribute. I know how to do it for one table but I would like to combine the results into one query and then dump the output into the file. Here is my sample qu...
I have a table like this: Date StudentName Score 01.01.09 Alex 100 01.01.09 Tom 90 01.01.09 Sam 70 01.02.09 Alex 100 01.02.09 Tom 50 01.02.09 Sam 100 I need to rank the students in the result table by score within different dates, like this: Date ...
Suppose I have these two tables: Invoice ------- iInvoiceID int PK not null dtCompleted datetime null InvoiceItem ----------- iInvoiceItemID int PK not null iInvoiceID int FK (Invoice.iInvoiceID) not null dtCompleted datetime null Each InvoiceItem might be fulfilled by a different process (executable) that runs on a different machine...
I have a simplified case here: a table with 5 fields of startdate, starttime, enddate, endtime and totalduration, all as varchar(20) type. Date fields are in the format like '02/02/2009' and time format like '02:02:00'. There are no null values at all. There is no problem for the following query: select cast(startdate + ' ' + sta...
I feel a little silly for asking this since I seem to be the only person in the world who doesn't get it, but here goes anyway. I'm going to use Python as an example. When I use raw SQL queries (I usually use ORMs) I use parameterisation, like this example using SQLite: Method A: username = "wayne" query_params = (username) cursor.ex...
How do you go about integration testing your database through your domain layer/model (repositories) that uses LINQ 2 SQL in the implementation and leave the DB as you found it? In other words, the ideal world of unit testing the DB, the integration test would leave the DB as it found it. Are there tools out there that will handle this...
Hello, I have the query below that fetches 500 records according to certain criteria. The fact that there are many rows (millions). I want to get total number of records so I can say "display 500 out of .... rows". Can I do this with this query? currently I have separate query to do that but I was wondering if I can do it in same query....
What is the benefit of having junction tables between the first 1:m and the second 1:m relations in the following database? The book Joe Celko's trees and hierarchies in SQL for Smarties says that the reason is to have unique relations in 1:m's. For instance, the following tables resrict users to ask the exactly same question twice an...
I need to (safely) handle the case where there is < 5 "reviews" for a product (even none). In addition, being able to define the ordering of the "reviews" is important. I would prefer a solution that would work with both SQL Server and PostgreSQL (with minor modification). I would be willing to use PIVOT as a last resort, but from what I...
Hi. Newbie here, so bear with me... Would it be possible to find patterns of records? I need to find repeating SEQUENCES of songs in the output of a radio programming software. The station manager wants to know if certain songs 'next' to certain other songs repeat over time, ie if the DJs are getting lazy and repeating certain sequenc...