sql

Adding trigger to table with cascades

I'm trying to add a simple trigger to a table- the 1st issue i came accross was that this table has text columns - so the for delete, insert, update triggers aren't going to float. 'instead of' does though. I am now up against the fact that the table has cascades set on it. Do you know if there's a way to get around that little gem or a...

Hierarchyid Problem

Hi all, I have a table with hierarchyid column. It is like: [NAME] [PATH] Ahmet / Aliye /1/ Selen /1/1/ Erdem /2/ Bilge /2/1/ Aydin /2/2/ Tomrs /2/2/2/ I want to see NAMES like: [NAMES_WITH_HIERARCHY] Ahmet Ahmet/Aliye Ahmet/Aliye/Selen Ahmet/Erdem Ahmet/Erdem/Bilge Ahmet/Erdem/Aydin Ahmet/Erdem/Aydin/Tomrs How can i do this? ...

How to change time part of a datetime value with doctrine?

I need to change the time part of a datetime in a database. What I got is in the database: '2010-01-01 01:00:00' I need to update this to '2010-01-01 03:00:00' Only thing I have is '03:00:00' As I'm using doctrine I could iterate through all objects but this would decrease the perfomance. So what I tried was: $q = Doctrine_Query::crea...

Indexing a column used to ORDER BY with a constraint in PostgreSQL

I've got a modest table of about 10k rows that is often sorted by a column called 'name'. So, I added an index on this column. Now selects on it are fast: EXPLAIN ANALYZE SELECT * FROM crm_venue ORDER BY name ASC LIMIT 10; ...query plan... Limit (cost=0.00..1.22 rows=10 width=154) (actual time=0.029..0.065 rows=10 loops=1) -> In...

My SQL query within a query

I have 2 tables that I am trying to combine in a specific way Table 1: ‘part_defs’ Table 2 Items_part_values in value_defs: ID | Name ------------- 1 | color 2 | size 3 | weight in Items_part_values ItemID | valueID | Value ------------------------- 10 | 1 | red 11 | 1 | blue What I need is a query where...

Raw sql query text in .net

Is it possible to get somehow the query text, that SqlCommand generates? ...

Does anyone know how to write this SQL If statement better?

I have this IF statement in my Stored Procedure: if ((@someParam = 'C') OR (@someParam = 'I' AND @SomeOtherParam <> 2 AND @SomeOtherParam <> 4 AND @SomeOtherParam <> 5)) My question is can I check @SomeOtherParam in one shot, instead of having to check it 3 separate times? ...

SQL Case with multiple fields

I have 3 fields that shows a hierachy in my application. The 3 fileds are as follows: rl.Level1 rl.Level2 rl.Level3 I am trying to show only the last level that is populated in my output. Basically this is what I am trying to do. If level3 is null then return level2 but if level2 is also null then return level1 but if level3 is not ...

Tool or Best practice for installing a database with a client application

I have come up with 2 methods for installing a local database with my WPF application: Create a backup of the DB, then restore in the installer via SMO Script the database install and execute it via ADO or osql.exe I'm required to provide an upgrade path, rather than just dropping the currently installed databases. Are there other m...

Need to optimize a nested select statement

I've got the following SQL: SELECT customfieldvalue.ISSUE FROM customfieldvalue WHERE customfieldvalue.STRINGVALUE IN (SELECT customfieldvalue.STRINGVALUE FROM customfieldvalue WHERE customfieldvalue.CUSTOMFIELD = "10670" GROUP BY customfieldvalue.STRINGVALUE HAVING COUNT(*) > 1); The inner nested select return...

replacing Group By with row_number(partition by ...)

Hello, I have an indexed view and I query this view to get counts on how many products each seller has in a specific category. This query works perfect and produces results in 45ms. I want to join additional tables and apply additional WHERE filters to the result set. I am not able to do it because I had to get products_pid out of With c...

How Can I Rid off Redundant Values from a Column?

This is the sample output Let me explain what's going on: The query returns all invoices # of every year along with the products that is involved in the invoice. As you see, we have two invoice in 2010...The invoices are 30463 and 30516. The invoice 30463 has 4 products, its shipping price is 105.88. As you see the shipping price is r...

How to create temp table from tags?

I have a product_table with a product_id among several other fields. I am creating a tag_table with 2 fields, tag_id and tag_name. The tags could be anything like "silver" or "penn state" etc. I am also creating a product_tag_map table with product_id and tag_id in order to map a product to any number of tags. If I wanted to create a...

SQL interview question

I got following question on an interview: Given a table of natural numbers with some missing ones, provide output of two tables, beginning of number gap in first table and ending in second. Example: ____ ________ | | | | | | 1 | | 3 | 3 | | 2 | | 6 | 7 | | 4 | | 10| 12| | 5 | |___|___| | 8 | | 9 | | 13 | |___...

SQL Stored Procedure to handle several null parameters?

On SQL Server I am trying to be able to select * records from a table with four parameters, in some cases the incoming parameter will be a certain string value 'Select', in that case I would like to select * from the specified field. Does anyone know of a good way to handle this? ( @mParameter varchar(50) = Null, @tParameter varchar(...

How to get Clob data from native query

I have tried this: List resultList = session.createSQLQuery("select pack.FuncName ClobData from dual").list() whereis FuncName it is the name of function that returns clob data. But in resultList I see some proxy object (for example $Proxy189). I believe... It is something simple and I don't want ising reflection to get my data. ...

Can any one explain this SQL Query?

Today, my boss told the below SQL query and went out without explaining it. Its working good. But i want to know the way how it works. SELECT NAME FROM PERMISSIONTOKENS WHERE ID IN (SELECT TOKENID FROM ROLETOKENASSOCIATION WHERE ROLEID = '1'); ...

mysql count selected field

I have a query that selects the nth results (in this case the 10th) I just want to use the reply_chunk_id of that nth result and count others like it to see how many there are in total with the same id. Does anyone know how to do this in one query? SELECT reply_chunk_id,message FROM ( SELECT * FROM messages ORDER BY timest...

SQL Anywhere dbBackup Error

Hi All, We are trying to run the following bdbackup command, on the command line from one of our virtual machines: dbbackup -y -c "uid=xxx;pwd=xxx;dsn=DSN NAME" C:\xxx\Applications\xxx\Backup only we are recieving the following error message: "Parse error: DSN 'DSN NAME' does not exist" The DSN is setup as a System DSN, which is wh...

Performance issues calling stored proc from within a stored proc

Hello, I worked on a project that had the following requirement: TableA is the parent table. Whenever any children records of TableA are updated the 'LastActivityDate' field in TableA should be updated with the current UTC date. Now, I know there are many ways of doing this. My post is NOT about the many different ways this coul...