sql

Rails : How to build statistics per day/month/year or How database agnostic SQL functions are missing (ex. : STRFTIME, DATE_FORMAT, DATE_TRUNC)

Hi everyone, I have been searching all over the web and I have no clue. Suppose you have to build a dashboard in the admin area of your Rails app and you want to have the number of subscriptions per day. Suppose that you are using SQLite3 for development, MySQL for production (pretty standard setup) Basically, there are two options ...

PDO Request Causing 500 Internal Server Error

This PDO request is not welcomed by my server for some reason. It makes the server throw a 500 Internal Server Error. All of my other PHP files are working fine and I haven't changed any server settings. Strangely though, It seems that when I comment out the line that binds the variable $u, it does not give a 500 error. I am perplexed. ...

SQL Server and Oracle joins tutorials

Hello All, Question 1: I am learning SQL Server and Oracle joins (inner joins, outer joins...) and case statements syntax. Though I can google for the info, but would like to ask for some sites, links, and materials that fully explain the joins and case statments. Once again, thanks. Thanks gentlemen. Question 2: I am running a sim...

Android - How to sort list

I have a list that i fill with data from a database, and now i want to sort this data by title and date. Im still new to programming for Android, so cant figure out how to do this here, hope someone can help. Here is how the list is filled: public void fillData() { // get all the data from database DBAdapter db = new DBAda...

Use SQL instead of Delete and End from keyboard

Hi, I am running a simple statment below which gives the output as follow: select '''' + name + '''' + ',' as Emp_Names from dbo.employee Emp_Names 'Jason', 'Robert', 'Celia', 'Linda', 'David', 'James', 'Alison', 'Chris', 'Mary', Is there a way in SQL that can show my desired output as: Emp_Names 'Jason', 'Robert','Celia'...

PHP query output of SQP

I have no actual php errors, but When I make my query this I get output : $sql_result = "SELECT AuthorFirst, AuthorLast, OnHand, Title FROM Inventory i, Wrote w, Author a, Book b WHERE i.BookCode = b.BookCode AND i.BookCode = w.BookCode AND a.AuthorNum = w.AuthorNum AND 2 = i.BranchNum"; When ...

Max Function in MySQL not working the way I thought it would

Basically, I am querying 3 columns in MySQL, Item_id, Date, and New_Qty. When I use the max function on the date column, the other columns all display their maxes for the same date range. SELECT `item_id` , max(date) AS max_date ,`new_qty` FROM `item_warehouse_link_history` WHERE warehouse_id =1 AND item_id=1000 AND DATE BETWEEN '2010...

Mysql: How to call sql script file from other sql script file?

Suppose I have wrote script Table_ABC.sql which creates table ABC. I have created many such scripts for each of required tables. Now i want to write a script that call all of these script files in a sequence so basically I want another script file createTables.sql. Mysql provides option to execute a script file from "mysql" shell applica...

MySQL Advanced SELECT help

Alright well I recently got into normalizing my database for this little side project that I have been creating for a while now, but I've just hit a brick wall. I'll try to give an understandable example of what I have and what I need to accomplish ― and hopefully it won't be too painful. OK. I have 3 tables the first one we will call ...

Tracking down an Umbraco Error Message

I have a couple of packages installed in my Umbraco installation so it would be kinda hard to to track down each one of them one by one. I get this error message every 5 minutes in the log file: Error executing scheduled task: System.UriFormatException: Invalid URI: The hostname could not be parsed. at System.Uri.CreateThis(String uri, ...

Problem with SQL query involving XOR-like condition

Let's say we have a table (EnsembleMembers) in an SQL database with the following data. It lists the musicians which are part of various ensembles, along with their instruments. EnsembleID (FK) MusicianID (FK) Instrument ---------------------------------------------- '1' '1' 'Clarinet' '1' '...

MYSQL Select on Trim

Really simple, I want to select all titles that starts with letter 'A' while ignoring the dash at the beginning of the string. SELECT * TRIM(LEADING '- ' FROM title) WHERE title LIKE 'A%' This just doesn't seem to work. Please help. Thanks. ...

SQL Sorting: server vs. client

Most of the times, when it comes to sort some data, we have two options: Sort on the SQL server -- use ORDER BY clause Sort on client one we get the data from the database When would you use one over the other and why? ...

Structuring a recipe database

I'm working on building a database that will search for recipes by ingredients. For example, I think I plan on populating the database with types of ingredients that are accepted, but I don't want to have to parse the string which includes all the ingredients in a particular recipe. I was thinking of making just like an list of acceptab...

SQL statement to update a percentage change

Hi guys. I have searched S.O. for this answer and have came close to an answer but still not close enough. I am interested in knowing if MySQL has this capability. I have developed in Perl and MySQL 4 and I'm on MySQL 4 now. My table looks like this... symbol varchar(25) todayDate date interest int(11) My problem is this.....these s...

SQL Query to show number of stories created in last 24 hours?

I'm trying to create a custom query that will show the number of stories that have been posted in the last 24 hours on a Drupal 6 site. Stories are stored in the "node" table. each record has a "created" row that records the UNIX timestamp when the story was posted. Here's the query I'm trying so far: $sq = 'SELECT COUNT(*) cnt ' ...

Converting a MySQL query, to a routine which takes parameter

Hi! I'm using VSC++ and MySQL ver.5.2.25 database, I've tested a query In MySQL that is like this: select Name from test.virus where Name LIKE 'b' '%' ORDER BY Name; It works well and return all Names that starts with 'b',I want to use a routine instead of this query, so that I can call the routine from my program. I've tried this...

Is it possible to rewrite this precedure using table vars

I have a simple delete procedure which is using na cursor. I read somewhere that table vars should be preferred to cursors. CREATE OR REPLACE PROCEDURE SMTAPP.LF_PLAN_VYMAZ (Str_oz varchar2, Num_rok number, Num_mesiac number, Str_s_trc_id varchar2) IS var_LF_PLAN_ID Number(20); cursor cur_plan is select id from lp_plan where lf_...

How to execute query based on passed operators <,= by param using Stored Procedures?

I have a stored procedure(SP) where one of it's param passes the operator. The operator can be: <=, >=, = How can I use this in the query? How the operators are passed is not built yet, it can be string represented <= or by using a custom code 1, 2 or 3. What do you think? ...

Want to show data in group using group by

I have a table where there is composite key of id and emp_no. I want to show record of all tids against one emp_no like this (not using any aggregate functions because no need to show any thing like that): emp_no tid tid tid emp_no2 tid tid tid Thanks in advance ...