Oracle BLOB vs VARCHAR
Hi. I need to store in a column of a table a SQL query (it's going to be a large one) and I thought using a BLOB field. What's best to use: BLOB or a VARCHAR ? Or something else maybe? Thanks for any ideas. C.C. ...
sql
What is the best way in SQL to swap the direction of a one-to-many relationship?
Okay, so for whatever reason I have ended up with a situation where the key is pointing the wrong way in a one-to-many. It was obviously never used as a one-to-many, only as a one-to-one, and now there is a need to expand one of those to be many, and the way the key was stored this turned out to be backwards. The images table has a t...
How to access help pages from SQL*Plus prompt?
Does SQL*Plus provide the help pages for its commands? Is there a way to access the help text from the SQL*Plus prompt, like this text for STARTUP? I tried SQL> man startup SP2-0734: unknown command beginning "man startu..." - rest of line ignored. SQL> startup -help SP2-0714: invalid combination of STARTUP options SQL> startup --help ...
Oracle: enumerate groups of similar rows
Hi all! I have the following table: ID | X 1 | 1 2 | 2 3 | 5 4 | 6 5 | 7 6 | 9 I need to enumerate groups of rows in such way that if row i and i-1 differ in column X by less than 2 they should have the same group number N. See example below. ID | X | N 1 | 1 | 1 2 | 2 | 1 3 | 5 | 2 4 |...
Complex ordering in SQL Query: possibly using CASE WHEN
I have a complex prioritisation algorithm that I want to write in SQL to when return prioritised pages of data. Some of these are prioritised according to fixed values, other are ordered by variable values. i.e. // Initial sort on ATTR1 (value1/value2 is higher than value3) if ATTR1 = value1 or value2 then orderBy creationDate, then ...
sql query count the zeros
I have this sql query retrieving IDs from a table with 3 columns: ID, Country and Age SELECT Country, (CASE WHEN AGE BETWEEN 0 AND 9 THEN '0-9' WHEN AGE BETWEEN 10 AND 19 THEN '10-19' WHEN AGE BETWEEN 20 AND 29 THEN '20-29' WHEN AGE BETWEEN 30 AND 39 THEN '30-39' WHEN AGE BETWEEN 40 AND 49 THEN '40-49' ELSE '50+' END) Age_Bins, ...
SQL Joins: Future of the SQL ANSI Standard (where vs join)?
We are developing ETL jobs and our consultant have been using "old style" SQL when joining tables select a.attr1, b.attr1 from table1 a, table2 b where a.attr2 = b.attr2 instead of using inner join clausule select a.attr1, b.attr1 from table1 as a inner join table2 as b on a.attr2 = b.attr2 My question is that in the long run, i...
SQL select with hierarchy for default values
I have a table in the following format: +---+---+--------+ | u | i | value | +---+---+--------+ | 0 | 1 | Value1 | | 0 | 2 | Value2 | | 0 | 3 | Value3 | | 8 | 2 | Value4 | | 9 | 2 | Value5 | | 9 | 3 | Value6 | +---+---+--------+ My goal is to do a select that selects values from that user (u) for each id (i) if it exists, if not f...
Importing Excel into SQL2005, issue with currency symbols and text in a number field
I have a field in my Excel as follows €250 €240 Free .... In my SQL2005 Preview this looks as follows 250 240 (Blank) So it doesnt like Symbols and Text in this column altho it is going to a varchar column. Any Ideas ? ...
OracleCommand seems to convert literal dates
Hi, I built a small query tool for Oracle using OracleCommand and OracleDataAdapter. Users just input a full query (no parameters), execute and the results are shown in a datagridview. So far so good, although I tried an invalid query, e.g.: SELECT * FROM mytable WHERE dateColumn = '1-JAN-10' This query is not valid SQL for Oracle. Y...
Remove dupe rows from DataTable
_CheckPayees = ds.Tables("Payees") _CheckPayees is a DataTable variable. After it is set with the code above, I would like to go through it and remove duplicate rows from it. These rows have to be exactly dupes though, say two columns have matching values however another one doesn't, that's not considered a dupe. Is there an easy way ...
A lot of Many To Manys
Hi All, I am working on a DB Structure and it seems like I have ended up with a lot of many to many tables. The reason I have am getting so many of them is that my base table is items and each item can have multiple occurrences of the search criteria that will be surfaced to the users. So I normalized all of the search criteria and us...
Mysql multi select with excluding row
Have 2 tables: A(`A_id`,`A_name`) and B(`B_id`,`A_id`,`B_kind`) B_kind column is ENUM('type 1','type 2'). So, how can I get all rows A which have an entry in table B with a B_kind='type 1' but without B_kind='type 2'. Representation in php: $A = array(); $B = array(); $sample = array(); foreach( $A as $key => $value ) { if( f...
unnecessary normalization
My friend and I are building a website and having a major disagreement. The core of the site is a database of comments about 'people.' Basically people can enter comment and they can enter the person the comment is about. Then viewers can search the database for words that are in the comment or parts of the person name. It is complete...
Uing PIVOT/UNPIVOT to convert rows to columns...
Hi, I have been reading up on PIVOT an UNPIVOT but have not been able to get the results formatted correctly to present the data. Here is my source table: StepID | ShortDesc | Type_1 | ar1 | ar2 1 ShortDesc1 10 11.11 11.01 2 ...
how to select fields from different db's with the same table and field name
I have two databases, for argument sake lets call them db1 and db2. they are both structured exactly the same and both have a table called table1 which both have fields id and value1. My question is how do I do a query that selects the field value1 for both tables linked by the same id??? ...
Generate all combinations in SQL
I have a need to generate all combinations of size @k in a given set of size @n. Can someone please review the following SQL and determine first if the following logic is returning the expected results. And secondly, is there a better way? /*CREATE FUNCTION dbo.Factorial ( @x int ) RETURNS int AS BEGIN DECLARE @value int IF ...
how to find date difference when dates are places in different rows in same table?
I have a table:: ItemID VersionNo CreatedDate ------------------------------- 1 3 7/9/2010 1 2 7/3/2010 1 1 5/3/2010 1 0 3/3/2010 2 0 4/4/2010 3 1 4/5/2010 3 0 3/4/2010 ...where Version 0 means .. its a newly produced item. Here ...
SQL query for non duplicate records
I'm attempting to build a query that will return all non duplicate (unique) records in a table. The query will need to use multiple fields to determine if the records are duplicate. For example, if a table has the following fields; PKID, ClientID, Name, AcctNo, OrderDate, Charge, I'd like to use the AcctNo, OrderDate and Charge field...
perl dbi rollback not working
Hello, i am using this approach. If there is an error in the sql, rollback only happens for the first id of the asset_group. Rest of the ids are ignored. Am i doing it the right way? my $sql = "sql batch that update and insert depending on the condition"; $dbh->{RaiseError} = 1; $dbh->{PrintError} = 0; $dbh->{AutoCommit} = 0; m...