sql

Create/Write Permissions in MySQL

I am experiencing some permission problems with my SELECT ... INTO OUTFILE statement. When I log into my database and do a simple export command, eg: mysql> select * from XYZ into outfile '/home/mropa/Photos/Desktop/TEST.txt'; I get the respond: ERROR 1 (HY000): Can't create/write to file '/home/mropa/Photos/Desktop/TEST.txt' (Err...

Converting Oracle SQL Select into PosgreSQL select

I have this SQL statement: SELECT ABX.ABX_APO_NUMBER, COUNT(A1.PROCESS_MODE) AS NUM_PLANNING, COUNT(A2.PROCESS_MODE) AS NUM_SETUP, COUNT(A3.PROCESS_MODE) AS NUM_OUTPUT FROM ABX, USER_INSTANCE U, ACTIVE_PROCESS A1, ACTIVE_PROCESS A2, ACTIVE_PROCESS A3 WHERE U.ABX_APO_NUMBER (+) = ABX.ABX_APO_NUMBER AND A...

SQL: how do I speed up this query

Here is the situation. I have one table that contains records based on records in many different tables (t1 below). t2 is one of the tables that has information pulled from it into t1. t1 table_oid --which table id is a FK to id --fk to other table store_num --field t2 t2_id Here is what I need to find: I need the ...

how to make mysql structure of up,down rating

Every programmer here knows about ratings like that: The problem is that I don't know how to make my SQL structure for that. I think I can add up and down field in article table in MySQL, but this does not allow multi voting. Could you please tell me how to make it? Do I have to create a new table? ...

Trim Whitespaces (New Line and Tab space) in a String in Oracle

I need to trim New Line (Chr(13) and Chr(10) and Tab space from the beginning and end of a String) in an Oracle query. I learnt that there is no easy way to trim multiple characters in Oracle. "trim" function trims only single character. It would be a performance degradation if i call trim function recursivelly in a loop using a function...

Default column value based on the value of a different column

SQL Server 2005. I have a table with ColumnA bit, ColumnB int Can I add a default value to ColumnB so that ColumnB is 15 if ColumnA is 1 and ColumnB is 0 if ColumnA is 0? I know I could do this with a trigger but I my boss is prejudice against triggers (he needs trigger sensitivity training). ...

Vanilla SQL with correlated subquery works in T-SQL, fails in PL/SQL

I would like to use essentially the same query in both T-SQL (SQL Server 2000/2005) and PL/SQL (Oracle 10g). Though it is not trivial, it is reasonably vanilla code, but it works in SQL Server yet fails in Oracle. My goal is to generate a count of rows for each unique upper/lower case combination of a particular field, omitting any field...

PHP PDO bindValue in LIMIT

Here is a snapshot of my code: $fetchPictures = $PDO->prepare("SELECT * FROM pictures WHERE album = :albumId ORDER BY id ASC LIMIT :skip, :max"); $fetchPictures->bindValue(':albumId', $_GET['albumid'], PDO::PARAM_INT); if(isset($_GET['skip'])){ $fetchPictures->bindValue(':skip', trim($_GET['skip']), PDO::PARAM_INT); } else{ ...

Help with syntax and adding a table and conditions to a query

switch($_GET["action"]) { case "add_item": { AddItem($_GET["idc"], $_GET["qty"]); ShowCart(); break; } case "update_item": { UpdateItem($_GET["idc"], $_GET["qty"]); ShowCart(); break; } case "remove_item": { RemoveItem($_GET["idc"], $_GET["id"]); ShowCart(); break; } default: { ShowCart(); } } function AddItem($itemId, $qty){ $result...

help with sql query with parentheses

i have following code: SELECT * FROM table WHERE thread = $thread AND (user != $user1 OR user != $user2) i want the code to pick all rows that contains $thread BUT the user isn't $user1 or $user2. is my code correct? or should it be like: SELECT * FROM table WHERE thread = $thread (AND user != $user1 OR user != $user2) thanks in a...

Setting multiple scalar variables from a single row in SQL Server 2008?

In a trigger, I have code like: SET @var1 = (SELECT col1 FROM Inserted); SET @var2 = (SELECT col2 FROM Inserted); Is it possible to write the above in a single line? Something conceptually like: SET (@var1,@var2) = (SELECT col1,col2 FROM Inserted); Obviously I tried the above, without success; am I just stuck with the first method?...

SQL Agent Job failing

I have a BizTalk Server that has been configured per the instructions for BizTalk disaster recovery using the BizTalk Log Shipping implementation. On the backup SQL Server, call it SQL02, the "Get Backup History" job fails constantly with the following error: Executed as user: DOMAIN\User. SQL Server Network Interfaces: Error ge...

Why in this php code the indexes ["name"],["price"] and ["id"] won't display any data?

$result= mysql_query("SELECT cart.id cart_id, dkb.id dkb_id, cdkb.id cdkb_id, dbl.id dbl_id, cart.*, dkb.*, cdkb.*, dbl.* FROM cart LEFT OUTER JOIN dkb ON ( cart.id = dkb.id AND dkb.id = '".$ids."' ) LEFT OUTER JOIN dbl ON ( dbl.id = dkb.id ) LEFT OUTER JO...

Select groups. Case ForRun ?

here is a structure :-/ So I need to select ID and Names from CfgListGroupParIzm for CfgIzmeritel using Type and where ForRun - False it's SELECT A.ID_ListGroupParIzm, A.Name FROM CfgListGroupParIzm A, CfgIzmeritel B WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=0 AND ID and NamePoint(from Cfg...

How to do troubleshooting of 1000 sql queries everyday?

What would be the best way to trouble shoot thousands of sql queries everyday? Trouble shooting might includes finding the blocked queries, improving performance of query, Queries that are hogging maximum processing time. ...

IsTornPageProtectionOn in Visual Studio Database Edition GDR

Hi all, im having major issues trying to mirror our newly upgrade database solution (to GDR)... specifically the database project settings. when i run a schema compare, i get differences on the IsTornPageProtectionOn variable, however when I go to the settings of the database project this setting is nowhere to be found! i have tried ...

TSQL - How to extract column with the Minimum and Maximum Value in another column

eg. I have 2 tables Customers and Orders Customers has columns CustomerID, Name Orders has columns OrderID, OrderedOn where OrderedOn is a DateTime Now I want a query which will give me the CustomerID OrderID and OrderTally where OrderTally = 'Initial' for the first order OrderTally = 'InMiddle' for everything in the middle and ...

in LINQ to SQL how would i create an outer left join to the first element of the right table?

Hi, I have two tables Orders and Products where Orders has a ProductID as a forgin key, I would like to select all products, if a product has orders i would like to select the one with the highest distance field. thanks, ...

Insert Into Statement - Ommiting One Column

I am trying to insert into a table all data but change just the date column So far I have something like INSERT INTO MyTable (SELECT * FROM MyTable...) What I want to do is set one of the columns to be a constant value (like todays date instead of what is selected out) Does anyone know of an easy way of doing that ...

how to import data from DBF to SQL using SQL script?

I am trying to import data from DBF file to SQL table using the following command - select * from openrowset('MSDASQL', 'Driver={Microsoft dBase Driver (*.dbf)};DBQ=E:\data\;', 'select * from E:\data\a.dbf') But it is failing saying OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC dBase Dr...