sql

Grouping by financial year and applying these groups as filters

Hi, I am looking to set up 6 groups into which customers would fall into: Non-purchaser (never bought from us) New purchaser (purchased for the first time within the current financial year) Reactivated purchaser (purchased in the current financial year, and also in the 2nd most recent year) Lapsed purchaser (purchased in the prior finan...

Counting updates/inserts in SSIS using PGNP

I'm updating a table from another table in the same database - what would be the best way to count the updates/inserts? I can think of a couple of methods: Join the tables and count (i.e. inner join for update, left join where null for inserts) then perform the update/insert Use the modification date in the target table (this is maint...

In a PL/SQL procedure, how do I pass a table name as a parameter?

CREATE PROCEDURE A(tab IN <table - what should I write here?>) AS BEGIN INSERT INTO tab VALUES(123); END A; How can I specify that the parameter tab is a table name? ...

constraint with condition

Can I add a constraint with a where clause. This means that I want the constraint on specific values of columns Thanks ...

sqlite timestamp formatting

Hi all. I am trying to work with dates in an sqlite database. I am storing my dates as timestamps, but when I use strftime() to format them to human readable dates I am getting back unxpected results. Condider the following, I select the current timestamp: SELECT strftime("%s","now"); 1281353727 Then I try to format a date using th...

SQL Math Issue During Sum - Works when removing either of the conditions

I'm using MS SQL, and here is my query... SELECT SUM(Quantity) FROM table1 WHERE [item_no]='0052556' AND [qty_to_ship] > 0 AND [type] = 5 Interestingly, the query as it stands above returns 'NULL'...however, if I remove either one of the last 2 conditions it works fine (returns a real number) to clarify, these queries work fine: SEL...

CSS and SQL populated lists.

I'm planning on doing this, but I can't get my head around to if this is completely possible or not. I'm trying to follow this design: http://i37.tinypic.com/28hfpzm.jpg The list on the left is populated by SQL. When clicking on one of these links on the left, the page will change (i.e. something like designers.php?designerID=whatever)...

SQL detecting null value and change query

hi i am using c# and mssql. i send parameters from c# to sql and execute a query but sometimes it comes null values. i m using sql like function. it is great for string values but has problems with bool. for example bool b = NULL query executes like that: .... WHERE B LIKE '%%' and it never returns a value. i want to detect null value ...

Hibernate: Value generated by SQL query

Hi, I want Hibernate 3.3.0 to generate a value by performing a SELECT query before INSERT (persist()). Which would work like this: @Generated(GenerationTime.INSERT) @GenerateSQL("SELECT RANDOM() * 2") private int number; I've had a look at @Generated(), that's good for TRIGGERs. I don't want to introduce a trigger. I also looked at ...

Where is the ROWID fetched from when queried?

For example, if I do SELECT ROWID, name from emp where age > 30; As ROWID does not take any storage space, is it calculated every time a query like this runs? From here, Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn is not evident when listing the structure of a table by ...

versioning of a table

anybody has seen any examples of a table with multiple versions for each record something like if you would had the table Person(Id, FirstName, LastName) and you change a record's LastName than you would have both versions of LastName (first one, and the one after the change) ...

Trigger is invalid and failed re-validation

Here is the code that I use to create a table, a sequence and a trigger DROP TABLE CDR.ExtDL_JobStatus; -- -- TABLE: CDR.ExtDL_JobStatus -- CREATE TABLE CDR.ExtDL_JobStatus( Id NUMBER(38, 0) NOT NULL, ShortName NUMBER(38, 0) NOT NULL, Description NUMBER(38, 0) NOT NULL, CONSTRAINT PK_ExtD...

How do I use dynamic SQL to declare a column name derived from a table name?

Building on Tony's answer on this question: If I want to do something like this, CREATE PROCEDURE A(tab IN VARCHAR2) IS tab.col_name <column> --static declaration (column name always remains the same) BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)'; END A; How can I use Dynamic SQL in the above case? ...

Recommendations for best SQL Performance updating and/or calculating stockonhand totals.

Apologies for the length of this question. I have a section of our database design which I am worried may begin to cause problems. It is not at that stage yet, but obviously don't want to wait until it is to resolve the issue. But before I start testing various scenarios, I would appreciate input from anyone who has experience with such...

Need a SQL query to select people within a specific age limit based on the current date

How to select people within age limit 19 t0 25 if the current date is less than '2011-01-01' and age limit between 19 to 26 if the current date is greater than or equal to'2011-01-01' ...

PostgreSQL SQL or PL/pgSQL query for traversing a directed graph and returning all edges found

Hi, I'm not particularly accustomed to generating complex SQL queries and am having difficulty in mixing my understanding of procedural languages and of set-based operations in devising a recursive query for network traversal. I wish to find the set of edges that lie 'upstream' of a particular node through conducting a depth first sear...

SQL Count where clause

I have the the following SQL statement: SELECT [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId] FROM (Leagues l INNER JOIN Lineups lp ON l.LeagueId = lp.LeagueId) WHERE (lp.PositionId = 1) OR (lp.PositionId = 3) OR (lp.PositionId ...

Optimized query to get min/max

I have data as Employee: id Name -------- 1 xyz 2 abc 3 qaz Employee_A: (Eid - employee table, title - title table) eid active type title ------------------------------ 1 1 1 1 1 1 2 2 1 1 4 3 2 0 3 4 2 1 2 2 2 0 ...

Move table row to a new table

In SQLite is it possible to move a whole row of a table to another table in one query, rather than selecting the row then inserting it into another table then deleting the row in the original table (3 query's) ...

SHOW ERRORS terminates execution

I have a script to create table and related structures DROP TABLE CDR.ExtDL_JobStatus; -- -- TABLE: CDR.ExtDL_JobStatus -- CREATE TABLE CDR.ExtDL_JobStatus( Id NUMBER(38, 0) NOT NULL, ShortName NUMBER(38, 0) NOT NULL, Description NUMBER(38, 0) NOT NULL, CONSTRAINT PK_ExtDL_JobStatus PRIMA...