I have a table in SqlServer 9 with data of the form Code Number J 0 J 5 J 8 Y 2 Y 8 I'd like to add a column that has a number with the internal sequence of numbers for each code, so that my table looks like this; Code Number Seq J 0 1 J 5 2 J 8 3 Y 2 ...
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values. This is identical to this question, but for Oracle rather than SQL Server or MySQL. ...
I'm trying to write a stored procedure and depending on a certain column value, I want to be able to change what table I select from. I'll try to give an example: SELECT ItemNumber, ItemType, Description FROM CASE ItemType WHEN 'A' THEN TableA ELSE TableB END WHERE CASE ItemType WHEN 'A' THEN ItemNumber = @itemNumber...
I have a SQL query, that returns a set of rows: SELECT id, name FROM users where group = 2 I need to also include a column that has an incrementing integer value, so the first row needs to have a 1 in the counter column, the second a 2, the third a 3 etc The query shown here is just a simplified example, in reality the query could be...
is there any way to log all failed sql statements in oracle 10g to a table or file? By failed I mean bad formated sql statement or sql statements that do not have permission for a table or object. ...
Hi, I am trying to construct a sql select statement that does the following: SELECT GetDate() Mon, GetDate() Tues But I also want to be able to put WHERE restrictions on each column (Mon, Tues). So I want something like this, but it is not correct: SELECT (GetDate() Mon WHERE (1=1)), (GetDate() TuesWHERE (1=1)) Please point me in ...
Hello, I am testing a simple query to get data from an AS400 database. I am not sure if the way I am using the SQL query is correct. I get an error: "The parameter is incorrect." Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = @Field1 I don't get an error when I run the following query: Select FIELD1, FIELD2 From Mylib...
Hi, I have a problem where my table stores the date as 2009-01-10 10:00:00.000 and I have another table which stores the date as 2009-01-10 12:00:00.000 I know they are not the same but the dates are, is there away in SQL to easily drop the time and keep just the dates for comparison? Thanks. ...
Oracle decided to dismiss the rule-based optimizer from version 10g, leaving the cost-based one as the only choice. I think that a rule-based optimizer has the unvaluable positive side of being always predictable. I've seen instead Oracle 10g changing execution plans from night to day, leading to turtle-like performances. Which could be ...
How would you go about calling several methods in the data access layer from one method in the business logic layer so that all of the SQL commands lived in one SQL transaction? Each one of the DAL methods may be called individually from other places in the BLL, so there is no guarantee that the data layer methods are always part of a ...
I use GORM to generate my database's DDL from groovy classes. Which is great. However, the order of fields in the generated SQL is not the same as the order of fields in the class. For example, if I create the class class Person { String firstName String lastName String address String email } the following SQL is generated ...
if (isset($_POST['login'])) { $query = mysql_query(" SELECT id FROM users WHERE username = '".mysql_real_escape_string($_POST['username'])."' AND password = '".mysql_real_escape_string($_POST['password'])."' "); /* wrong login information? output a message and terminate the script */ if (!mysql_num_rows($que...
Hey Stackers, Since SQLite does not support RIGHT OUTER JOINS I pose the following challenge (read: invitation to do my work for me): Refactor this query so it no longer utilises SQLite-unsupported constructs like RIGHT/FULL OUTER JOINs. SELECT strings.*, translations.text FROM translations INNER JOIN ...
I am trying to take a person and display their current insurance along with their former insurance. I guess one could say that I'm trying to flaten my view of customers or people. I'm running into an issue where I'm getting multiple records back due to multiple records existing within my left join subqueries. I had hoped I could solve...
For some of the apps I've developed (then proceeded to forget about), I've been writing plain SQL, primarily for MySQL. Though I have used ORMs in python like SQLAlchemy, I didn't stick with them for long. Usually it was either the documentation or complexity (from my point of view) holding me back. I see it like this: use an ORM for ...
Hi there, I am trying to limit the following SQL statement. SELECT expense.*, transaction.* FROM expense INNER JOIN transaction ON expense_id = transaction_expense_id What I want to do, is limit the number of 'parent' rows. IE. if I do a LIMIT 1, I would receive only one expense item, but still get all transactions associated with it....
I'm going to be using the ASP.NET Membership model on my website. However, there are multiple tables that I do not plan to ever use, such as: aspnetPaths aspnetPersonalizationAllUsers aspnetPersonalizationPerUser aspnetProfile aspnetWebEventEvents Is it safe to delete these tables or will I cause problems with the way ASP.NET's membersh...
Hi all, image this two tables: Table: Item Columns: ItemID, Title Table: Tag Columns: TagID, ItemID, Title Which is the best way (without changing table structure (yes, I don't mind if they are not normalized)) to retrieve a list of items with all their tags attached using less possible query (i.e. not doing 11 queries to retrieve 10...
Hi, I got an Java application using SQL tables that contains an ordered list of entities, ordered by an order column. I would like to add/remove things in/from the middle of the list. Now, I'm wondering if some persistence framework / orm / you-name-it could provide this kind of functionality with batch update of order column. At the...
Hi! I tried to execute the DESCRIBE command via a database link, but this was the return message: DESCRIBE <table>@<database>; ERROR: ------------------------------------ ERROR: object <table> does not exist 1 rows selected A SELECT on this table works well. Does Oracle permitts DESCRIBE via a datab...