sql

pseudo columns & DUAL table - what do they actually mean?

Dual table is used to select pseudo columns. it has one row and one column DUMMY which has a value X. I have two questions What actually does a pseudo column mean? How is the dual able to give the value for example: select sysdate from dual will result in current datetime. How is this possible? ...

Asp.Net VB or C# how to combine lines & columns based on unique primary field.

I have been working on a web based report from an existing client/server apps mssql database. I have a working query, that pulls this information together from many tables, and can provide the query and a result set if necessary. The results currently are similar to this. ID | Name1 | Date1 | Veh | PO | Stops ___________________...

Wirunsql in .Net

I need to modify an MSI file, and I'd like to do it in code. I can do it via command line using cscript.exe and wirunsql.vbs, but I can't figure out how to do it in .Net. I know it can be done, as I've found snippets of other people doing something, but they don't say what to include or where to get it. I have the Windows Platform SDK, b...

Query Problem - Result Coming incorrect

I am having trouble solving this. Please help. I have a table named Product_Information. I want to count the number of products present in a category and subcategory. This is the table Product_Id - Product_Title - Product_Sub_Category - Product_Category 1 ----------------abc------------------XYX------------------X 2 ----------------...

How do I force BIRT to display zero values?

I have a situation in BIRT reporting. A report I created is insisting on displaying some fields as blank rather than zero. The situation arises when the field is actually a sub-select that returns no rows. So for example, if the select includes: 0 as p3, then the 0 is displayed okay. If however, the select has: (select sum(other_fie...

How to prevent SQL Injection attack in applications programmed in Zend Framework ?

I don't have any concept about ZF safety. Do I have to use Filter when operating on database? Maybe binding is enough ? How about this: $users->update($data, 'id=1'); Should $data array be filtered somehow ? Feel free to write anything you know about the issue. Could you give some links to good articles about safety in ZF (mainly abo...

Continuing a transaction after primary key violation error

I am doing a bulk insert of records into a database from a log file. Occasionally (~1 row out of every thousand) one of the rows violates the primary key and causes the transaction to fail. Currently, the user has to manually go through the file that caused the failure and remove the offending row before attempting to re-import. Given th...

SQL Server 2008 FILESTREAM Feature with VLDB

I have a bunch of xml files that is about 700 GB in size. I'm going to load the data within those files into a SQL Server 2008 database table(tabular data). In addition to the fields that will hold the data in a tabular format, the table will contain a field of SQL Server XML type that holds the xml data as a whole. I want to use the FIL...

T-SQL: Omit/Ignore repetitive data from a specific column

Hi, For my question lets consider the following sample table data: ProductID    ProductName    Price   Category 1                Apple                 5.00       Fruits 2                Apple                 5.00       Food 3                Orange               3.00       Fruits 4                Banana   ...

Find path between nodes with SQL

Hi, i have two mysql tables: nodes and relations CREATE TABLE `nodes` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `relations` ( `node_id` int(10) unsigned NOT NULL, `related_node_id` int(10) unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Let...

Composite Primary keys and Foreign key constraint error

Hi, I have an SQL table defined as below: CREATE TABLE [TestComposite] ( ID int, SiteUrl nvarchar(255), Name nvarchar(max) NOT NULL, ParentID int NULL, PRIMARY KEY (ID, SiteUrl) ); Items and folders are stored inside the same table, if an item is inside a folder, the ParentID column is the ID of the ...

What is the relationship between CLP and SQL?

In reading up on constraint-logic programming, I can't help but notice an apparent relationship with SQL programming. Is SQL an example of "constraint logic programming" in action? ...

Counting the results of a query for use in SELECT TOP in a second query

I am trying to do the following but it does not work: SELECT TOP count(SELECT plot_details.[Design Code] FROM plot_details WHERE plot_details.[Design Code] = "ET2") CountOfET2.[CountOfPlot Number] bids.[Bid Amount], bids.[Design Code], bids.Username FROM bids ORDER BY bids.Username DESC; The query that provides the SELECT TOP value re...

SQL Query within VS TableAdapter Query Configuration Wizard

Hi, I am trying to write an SQL query within Visual Studio TableAdapter Query Wizard My SQL query is: DECLARE @SQL varchar(255); SET @SQL = ' SELECT * FROM dbAddress WHERE 1 = 1' IF @ApexLine1 = '' BEGIN SET @SQL = @SQL + ' AND addLine1 IS NULL ' END ELSE BEGIN SET @SQL = @SQL + ' AND addLine1 = ''' +...

IN(1, 2...n) query optimization

Hi! As far as I know, mysql doesn't use index if using IN(1, 2...n) queries. Am I wrong? Or is there anything I could do to make mysql use it? I don't mean IN() subquery optimization, because this is clearly explained in the manual. Example (assuming there is an index on all fields, named index_abc): WHERE a = 1 AND b = 2 AND c = 3 - ...

joining two tables

I have users table. There are three other tables: developers, managers, testers. All of these tables have a foreign key user_id. I need to find all the users who are either developer or manager. What the sql will look like? Update: Someone can be both a developer and a manager. ...

I need a query that retrieves this result

I have a table of 3 columns: AuthorID (id can be repeated) JournalName (name can be repeated) AuthorScore I need a query that gets JournalName and the count of all authors having their maximum score in this journal. Thank you in advance. ...

Is this SQL valid?

I am still new to implementing joins in my db_design and i am not sure if, at all this SQL is valid. "SELECT * FROM notes JOIN small_note ON notes_id = '$id' AND authname = '$author' AND bookname = '$book' AND user = '$username'" A bit easier to read like this: "SELECT * FROM notes JOIN small_note ON notes_id = '$id' AND auth...

Linkage Table: Technical name given to table storeing assoications?

Title pretty much sums it up. Is there a technical name given to a table that stores primary key from two separate tables to create a linkage. i.e. car ( id, manufacturer, model, year, vin), passenger ( id, name ), linkage_table ( car, passenger ) Where car stores value of the id column from the car table and passenger stores the val...

SQL View displaying current status of a "unit" for every date that something changes with the "unit"

In a project I'm working on, there is a table of units and then there are several tables that pertain to the units' locations at a given point in time as well as other factors for a given point in time. For example, Unit1 might be at LocationA on 1/1/2009 and have a second location entry that has Unit1 at LocationB on 3/1/2010. Likewis...