sql

ADVICE on billing Query in SQL Server 2000

I need some advice in tackling a query. I can handle this in a front-end application, however, due to design, I have to inplement this in the back-end. I have the following CREATE TABLE [dbo].[openitems]( [id] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [type] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ...

Unable to create a simple view on Oracle table

An external DB admin guy exported a production database and imported it into test environment. We are using Oracle 9.2. Majority of imported database objects (tables, views, idexes, packages,...) works fine, but we have problems with three specific tables: we can do SELECT,UPDATE, DELETE on those tables, but we can not create views on ...

SQL Query: How do you combine count function result into a select query?

select distinct Franchise.FranchiseName, Franchise.Initials, Franchise.StoreNo, AccountCancellation_Process.Store_Num FROM FranchiseData INNER JOIN AccountCancellation_Process on FranchiseData.StoreNo = AccountCancellation_Process.Store_Num select count(*) from AccountCancellation_Process where Store_Num = '1234' select count(*) from ...

Using System Tables to Count the Percent of Rows Null in Various Tables

I'm trying to determine the percent of null items for all fields of a table. I have to run this on several tables with a ton of fields, and I was looking for an automated way of doing this. I know I can query "information_schema.columns" and get a nice clean list of field names ala: select Column_name from information_schema.columns ...

What's with the IF in this query

This is my query update b set b.col1 = if(1) <= 0 begin select 1 as bal end else select 0 as bal from dbo.table1 b inner join dbo.table1 a on b.id = a.id and b.date = a.date The "IF" part works great by itself. Puting it in this query form throws Incorrect syntax near the keyword '...

SQL to transpose row pairs to columns in MS ACCESS database

I have an MS Access database that contains translated sentences in source-target pairs (a translation memory for fellow users of CAT tools). Somewhat annoyingly, source and target are not stored in separate columns, but in rows linked by ID, like this: +---+----+--------------+ |id |lang| text | +---+----+--------------+ 1 a...

Reading MS Access mdb files in Delphi (for free)?

I'm looking for a Delphi component / library to open and read from an mdb (MS Access) database. I will not be writing to the db or displaying the data; just need to read the db using whatever sql Access supports. This is for a personal side-project (programming is not my paying job), so I need a free or a very inexpensive solution that...

Querying Java Data Structures

Is there any way to perform SQL Like Queries or Filtering on Java Data Structures? I want to filter objects in an ArrayList and a HashMap by fields of the objects contained within. ...

Need ad-hoc reporting component

We need some simple ad-hoc reporting solution for our ASP.NET web-site. Just an ability to build a query with user friendly interface, then show the result of this query in some table and maybe export it to Excel or print. The solution must be quite easy for end users (our site visitors) who know nothing about databases, SQL and other ...

SqlConnectionStringBuilder form in .net C#

I am wondering how I can add DATA LINK form to my WIN application. You know those forms where Users can choose on witch SQL server they going to connect and what type of security they going to use and on wht database. Something like on this picture ...

Differentiate empty from NULL query results

I've got a table with measurement data in SQL Server 2005, one value per person and year, if available. My TSQL code fetches these values in a loop and processes them: ... SET @val = (SELECT measurement FROM tbl_data WHERE persid = @curpersid AND yr = @curyear) ... Now, for a certain person and year, the table can contain (i) a valid ...

Turn off constraints temporarily

I'm looking the way to temporarily turn off all DB's constraints (eg table relationships) I need to copy (using INSERTs) one DBs tables to another DB I know I can archive that by executing commands in proper order (to do not break relationships) But it would be easier if I could turn off checking constraints temporarily and turn on it...

Counting rows in SQL stament using grouping by incremnental variables

Hi all Table I'm trying to get analytical result from is voice calls record. Each call (one row) has duration in seconds (just int value, not datetime). I'm trying to get number of records grouped by 15 seconds spans like this: +-------------------+ |Period | Count | +-------------------+ | 0-15 | 213421 | |15-30 | 231123 ...

Order By query ignores punctuation marks

This is with Postgresql. A column in a table contains string values with punctuations. The values are "aac", ".aaa", "aa_b", etc. When this column is specified in order by clause, the order of results is almost random. The strings starting with a period should appear at the top, which doesn't happen. They appear somewhere in the middl...

SQLite in Python 2.2.3

I've written a web-app in python using SQLite and it runs fine on my server at home (with apache and python 2.5.2). I'm now trying to upload it to my web host and there servers use python 2.2.3 without SQLite. Anyone know of a way to use SQLite in python 2.2.3 e.g. a module that I can upload and import? I've tried butchering the module f...

Postgres - How to check for an empty array

I'm using Postgres and I'm trying to write a query like this: select count(*) from table where datasets = ARRAY[] i.e. I want to know how many rows have an empty array for a certain column, but postgres doesn't like that: select count(*) from super_eds where datasets = ARRAY[]; ERROR: syntax error at or near "]" LINE 1: select count...

varchar(max) MS SQL Server 2000, problems?

Hi, I've inherited a asp.net website project that currently runs MS SQL 2000 as its backend. I've been doing some databases changes on a local copy of the db using MS SQL Server 2005 Express. I've create a table using varchar(max) columns. They are used to stored snippets of XHTML that are of arbitrary length. While browsing around on ...

SQLite optimizing multi-select insert

Hi All, I've been using SQL for years now but rarely anything more that simple inserts and selects etc... so I'm no SQL expert. I'm wondering if I could get some help in optimizing a more complex SQL statement that I'm executing on SQLite, from PHP through PDO. The statement seems to work correctly, just seems to take longer that I wo...

Join via junction table with nullable field

Hi everyone! A "plain SQL question" :) I have two tables and a third one that acts as a "semi junction table", meaning that sometimes there's just one of the two foreign keys.. Is there a way to join them? For example, given the following data: Table D id's: 1,2,3,4,5 Table C id's: 1,2,3 Table Junction (D.id, C.id): (1,1) (2, NULL)...

Comma separated values in a database field

I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example: Id | Code ---------------------- 0001 | IN,ON,ME,OH 0002 | ON,VI,AC,ZO 0003 | QA,PS,OO,ME What I'm trying to do ...