Sign up to create your own snipts, or login.

Public snipts » sql The latest public sql snipts.

showing 1-20 of 79 snipts for sql
  • ∞ number of records in recordset 
    $result = mysql_query($query) or die(mysql_error());
$count = mysql_num_rows($result);
echo "$count";

    copy | embed

    0 comments - tagged in  posted by mgrobertson on Mar 11, 2010 at 1:10 p.m. EST
  • ∞ Magento SQL example 
    <?php

// magento sql 
// 
public function abc($type)
{
    $type = strtolower($type);
    
    $validType = array(
        'temp',
        );
    
    if (in_array($type, $validType))
    {
        $sql = "SELECT *  
            FROM aaa
            WHERE bbb='$type' and
                status = '1'
            ORDER BY aaa_id desc";
        $data = Mage::getSingleton('core/resource') ->getConnection('core_read')->fetchAll($sql);
        if (!empty($data))
            return current($data);
        else
            return array();
    }
}

    copy | embed

    0 comments - tagged in  posted by robertbanh on Mar 06, 2010 at 12:38 p.m. EST
  • ∞ create DB utf8 
    CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

    copy | embed

    0 comments - tagged in  posted by toledot on Feb 07, 2010 at 1:05 p.m. EST
  • ∞ List Sql Server Sessions 
    select 	min(login_time) as logintime, loginame, hostname, program_name, 
        db.Name as "database_name" 
	from master.dbo.sysprocesses inner join master.dbo.sysdatabases as db
	on master.dbo.sysprocesses.dbid = db.dbid
	group by loginame, hostname, program_name,db.name  order by loginame

    copy | embed

    0 comments - tagged in  posted by bearfx on Feb 05, 2010 at 11:13 a.m. EST
  • ∞ Drop all tables and constraints within an Oracle schema 
    BEGIN
FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS');
END LOOP;
END;

    copy | embed

    0 comments - tagged in  posted by Fotinakis on Jan 29, 2010 at 5:22 p.m. EST
  • ∞ sql insert into syntax 
    INSERT INTO  physiologic_monitoring  (subject_id) SELECT id FROM subject id JOIN pm_participants p USING (email)

UPDATE participant_summary p  JOIN SSSSSS s USING (participant_id)  SET p.RDI= s.RDI

    copy | embed

    0 comments - tagged in  posted by tayhimself on Jan 28, 2010 at 4:40 p.m. EST
  • ∞ Función de SQL para determinar la edad con base a 2 fechas 
    CREATE FUNCTION [dbo].[fn_GetAge] (@DateOfBirth DATETIME, @AsOfDate DATETIME)
RETURNS INT
AS
BEGIN

/*
Descripción: Función que calcula  una edad en base a 2 fechas
Autor: David Austria

Revisión: 2 de Agosto de 2006
Descripción: Procedimientos Básico

Ejemplo:
SELECT dbo.Fn_GetAge(CONVERT(DATETIME, '1977-05-04 00:00:00', 102),GetDate()) as Edad
*/

    DECLARE @vAge         INT
    
    IF @DateOfBirth >= @AsOfDate
        RETURN 0

    SET @vAge = DATEDIFF(YY, @DateOfBirth, @AsOfDate)

    IF MONTH(@DateOfBirth) > MONTH(@AsOfDate) OR
      (MONTH(@DateOfBirth) = MONTH(@AsOfDate) AND
       DAY(@DateOfBirth)   > DAY(@AsOfDate))
        SET @vAge = @vAge - 1

    RETURN @vAge
END

    copy | embed

    0 comments - tagged in  posted by davidaustria on Jan 19, 2010 at 6:13 p.m. EST
  • ∞ Oracle table size 
      select to_char(begin_interval_time,'yyyymmdd hh24:mm') modify_date
       , object_name      table_name
       , space_used_total total_size
    from dba_hist_seg_stat       s,
         dba_hist_seg_stat_obj   o,
         dba_hist_snapshot       sn
   where o.owner = 'ODS'
     and s.obj# = o.obj#
     and sn.snap_id = s.snap_id
     and object_name like 'UNRATED%'
order by begin_interval_time;

    copy | embed

    0 comments - tagged in  posted by bgulcu on Jan 19, 2010 at 3:54 a.m. EST
  • ∞ Insert SQL via command line 
    -- insert sql via command line
mysql -u dbuser -p -h dbhost.yoursite.com dbname < /path/to/backup.sql

    copy | embed

    0 comments - tagged in  posted by corbanb on Dec 07, 2009 at 1:59 p.m. EST
  • ∞ Query para retornar pies de ventas 2008 por empresa y periodo 
    SELECT     d.Empresa, v1.Nombre, SUM(v1.MontoIngreso) AS Expr1, d.Periodo
FROM         Documento AS d WITH (nolock) INNER JOIN
                      DocumentoVal AS v1 ON d.Empresa = v1.Empresa AND d.TipoDocumento = v1.TipoDocumento AND d.Correlativo = v1.Correlativo
WHERE     (d.TipoDocumento LIKE 'operacion%') AND (d.Fecha >= '2008-01-01') AND (d.Fecha < '2009-01-01') AND (d.Estado = 's') AND (v1.Nombre = 'contado')
GROUP BY d.Empresa, v1.Nombre, d.Periodo

    copy | embed

    0 comments - tagged in  posted by dubo on Dec 04, 2009 at 8:50 a.m. EST
  • ∞ Busca operaciones devueltas sin finalizar 
    SELECT     Empresa, TipoDocumento, Correlativo, Entidad, Fecha, Moneda, ListaPrecio, Ejecutivo, Glosa, Periodo, Estado, Abierto, Etapa, RolCreacion, RolActual, FechaCreacion, 
                      UsuarioCreacion, FechaModif, UsuarioModif
FROM         Documento
WHERE     (TipoDocumento LIKE 'operaciones%') AND (Empresa = 'futuro') AND (Fecha > '31-12-2008') AND (Correlativo IN
                          (SELECT DISTINCT Id
                            FROM          Historial
                            WHERE      (Tabla = 'documento') AND (Subtabla LIKE 'operacio%') AND (Comentario LIKE '%Dev. Supervisor%'))) AND (Etapa <> 'Fin') AND (Etapa <> 'Post Venta') 
                      AND (Etapa <> 'cuponera') AND (Etapa <> 'facturacion')

    copy | embed

    0 comments - tagged in  posted by dubo on Nov 26, 2009 at 1:56 p.m. EST
  • ∞ parser 
    <?php
	$file_path = 'inst_response.cvs';
	$file_sql_path = 'inst_response.txt';
	$file_rights = 'r';
	$file_handler = '';
	$sql_query = "INSERT INTO `Message66` (`User_ID`, `Subdivision_ID`, `Sub_Class_ID`, `Priority`, `Keyword`, `Checked`, `TimeToDelete`, `TimeToUncheck`, `IP`, `UserAgent`, `Parent_Message_ID`, `Created`, `LastUpdated`, `LastUser_ID`, `LastIP`, `LastUserAgent`, `UserID`, `Grade`, `Comment`) VALUES ";
	
	$file_lines = file($file_path);
	$sql_lines = array();
	
	foreach ($file_lines as $line) {
		list($id, $text, $grade, $date) = explode("%;", $line);
		// ??????? ??????? ??????
		$date = substr($date, 0, strlen($date) - 2);
		list($date_day, $date_month, $date_year) = explode(".", $date);
		// ????????? ?????? ??? ?????????? ?????? ??????? ? ????????? ?? ? ??????
		$sql_lines[] = "(1, 149, 167, $id, '', 1, NULL, NULL, '217.117.112.154', 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.9 (KHTML, like Gecko) Iron/2.0.178.0 Safari/530.9', 0, '$date_year-$date_month-$date_day 01:08:38', '2009-06-15 01:08:38', 0, NULL, NULL, $id, $grade, '$text')";
	};
	$sql_query = $sql_query.implode(', '.Chr(10), $sql_lines);
	file_put_contents($file_sql_path, $sql_query);
?>

    copy | embed

    0 comments - tagged in  posted by Cokol on Nov 25, 2009 at 10:42 a.m. EST
  • ∞ DB2 Drop FK 
    ALTER TABLE <name>
      DROP FOREIGN KEY <foreign_key_name>

    copy | embed

    0 comments - tagged in  posted by TanerDiler on Nov 24, 2009 at 6:47 a.m. EST
  • ∞ DB2 Create Table with PK&FK 
    CREATE TABLE table_name (
       ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL,
       USER_ID INT NOT NULL,
       USERNAME varchar(45) NOT NULL,
       TIME timestamp NOT NULL,
       OLD_QUANTITY DECIMAL(20,5) NOT NULL,
       CONSTRAINT FK_LOGPROFILE_USER FOREIGN KEY(USER_ID) REFERENCES PERSON(PERSON_ID),
       CONSTRAINT FK_LOGPROFILE_STOCK FOREIGN KEY(STOCK_ID) REFERENCES SM_STOCK(STOCK_ID)
)

    copy | embed

    0 comments - tagged in  posted by TanerDiler on Nov 24, 2009 at 6:46 a.m. EST
  • ∞ create view 
    CREATE VIEW HotelAndGuests AS
	SELECT hotelName, guestName
	FROM Hotel
	INNER JOIN Booking ON Hotel WHERE Booking.hotelNo = Hotel.hotelNo
	INNER JOIN Guest ON Booking WHERE Guest.guestNo = Booking.guestNo;

    copy | embed

    0 comments - tagged in  posted by dgalarza on Nov 08, 2009 at 9:12 p.m. EST
  • ∞ create insert 
    CREATE TABLE BookingArchive(
	hotelNo		HotelNumber,
	guestNo		GuestNumber,
	dateFrom	DateFrom,
	dateTo		DateTo,
	roomNo		RoomNumber);
	
INSERT INTO BookingArchive VALUES(SELECT * FROM Booking);

    copy | embed

    0 comments - tagged in  posted by dgalarza on Nov 08, 2009 at 8:49 p.m. EST
  • ∞ mapex db 3 
    SELECT SUM(price) AS totalIncome
FROM Room
INNER JOIN Hotel ON Room WHERE Room.hotelNo = Hotel.hotelNo
INNER JOIN Booking ON Room WHERE Room.roomNo = Booking.roomNo
WHERE Booking.dateFrom >= '8-11-2009'
AND Booking.dateTo <= '8-11-2009'
AND Hotel.HotelName = 'Grosvenor Hotel'
GROUP BY Room.hotelNo

    copy | embed

    0 comments - tagged in  posted by dgalarza on Nov 08, 2009 at 4:47 p.m. EST
  • ∞ sort PostgreSQL database dump to minimize diffs 
    #!/usr/bin/env python

import sys
import re
from bisect import insort

COPY_RE = re.compile(r'COPY .*? \(.*?\) FROM stdin;\n$')

def try_float(s):
    if not s or s[0] not in '0123456789.-':
        # optimization
        return s
    try:
        return float(s)
    except ValueError:
        return s

def linecomp(l1, l2):
    p1 = l1.split('\t', 1)
    p2 = l2.split('\t', 1)
    result = cmp(try_float(p1[0]), try_float(p2[0]))
    if not result and len(p1) == len(p2) == 2:
        return linecomp(p1[1], p2[1])
    return result

if __name__ == '__main__':
    copy_lines = None
    for line in sys.stdin:
        if copy_lines is None:
            if COPY_RE.match(line):
                copy_lines = []
            sys.stdout.write(line)
        else:
            if line == '\\.\n':
                copy_lines.sort(cmp=linecomp)
                for copy_line in copy_lines:
                    sys.stdout.write(copy_line)
                sys.stdout.write(line)
                copy_lines = None
            else:
                copy_lines.append(line)

    copy | embed

    0 comments - tagged in  posted by akaihola on Oct 14, 2009 at 10:28 a.m. EDT
  • ∞ Script to search a value on BBDD 
    EXEC SearchAllTables 'A38010088'
GO 

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT


	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

    copy | embed

    0 comments - tagged in  posted by KeiDash on Oct 09, 2009 at 9:32 a.m. EDT
  • ∞ sql force id insert 
    SET IDENTITY_INSERT TABLE ON;
                          
INSERT  INTO TABLE
  (ID, ...)
VALUES
  (ID, ...);

SET IDENTITY_INSERT TABLE OFF;

    copy | embed

    0 comments - tagged in  posted by nicolascormier on Sep 30, 2009 at 3:25 a.m. EDT
Sign up to create your own snipts, or login.