sql

sql group by with left join

fail statement:Error: ORA-00979: not a GROUP BY expression select org_division.name , org_department.name , org_surveylog.division_code as divisionCode,org_surveylog.department_code as departmentCode , max(org_surveylog.actiondate) from org_surveylog left join org_division on (org_surveylog.division_code= org_division.division_code a...

iBatis get executed sql

Hi all. Is there any way where I can get the executed query of iBatis? I want to reuse the query for an UNION query. For example: <sqlMap namespace="userSQLMap"> <select id="getUser" resultClass="UserPackage.User"> SELECT username, password FROM table WHERE id=#value# </select> </sqlMap> ...

SQL query to calculate running group counts on time-phased data

I have some data, like this: BUG DATE STATUS ---- ---------------------- -------- 9012 18/03/2008 9:08:44 AM OPEN 9012 18/03/2008 9:10:03 AM OPEN 9012 28/03/2008 4:55:03 PM RESOLVED 9012 28/03/2008 5:25:00 PM CLOSED 9013 18/03/2008 9:12:59 AM OPEN 9013 18/03/2008 9:15:06 AM RESOLVED 9013 18/03/2008 9:16:44 AM C...

Help Regarding SQL injection

Please help me to prevent my data from SQL injection. I have replaced ' with '' (single quote with 2 quote) while doing any operation on sql server. Please tell me what all i need to do , to prevent my application from SQL injection. my application is in asp.net 2.0 i will use parameterized queries but what about my old projects.. i mea...

Efficient way to update all rows in a table

Hi, I have a table with a lot of records (could be more than 500 000 or 1 000 000). I added a new column in this table and I need to fill a value for every row in the column, using the corresponding row value of another column in this table. I tried to use separate transactions for selecting every next chunk of 100 records and update t...

How to convert a C# Queue into a binary datatype to be stored as a SQL Blob?

Hi All, How do I convert a C# Queue into a binary variable, so that I can store it as a blob in SQL? Help appreciated! Soham ...

SQL query to translate a list of numbers matched against several ranges, to a list of values

I need to convert a list of numbers that fall within certain ranges into a list of values, ordered by a priority column. The table has the following values: | YEAR | R_MIN | R_MAX | VAL | PRIO | ------------------------------------ 2010 18000 90100 52 6 2010 240000 240099 82 3 2010 250000 259999 50 5 2...

What are the best practices to separate data from users

Hi. For a customer we where developing a big application that where open to all users if you will, meaning, all users could see each others data. Now suddenly the customer is saying that they want only users belonging to the same organization to be able to view each others data. So we came up with this data model: So now the questi...

How to structure data... Sequential or Hierarchical?

I'm going through the exercise of building a CMS that will organize a lot of the common documents that my employer generates each time we get a new sales order. Each new sales order gets a 5 digit number (12222,12223,122224, etc...) but internally we have applied a hierarchy to these numbers: + 121XX |--01 |--02 + 122XX |--22 |--23 |--...

.NET: Allow NULLS in DB fields?

Hi there, I have the task of re-factoring an SQLServer DB.... A lot of the tables and columns "ALLOW NULLS", Is this good practice... I seem to remember the authour of CSLA.NET saying it was really bad practice to allow nulls in a DB... If this is the case, what are my alternatives? Remove all "ALLOW NULLS" from all columns.... and ...

SQL ENQUIRY In how to Get defined number of records

I have a select statement, retrieve about 1000 record I want to modify it to return only some records defined by @startIndex and @count e.g. : If I said @startIndex=20 and @count=20 the result will be : from the 21th record to 40th I try to make it, but it take the same time as if I retrieve the 1000 record what is the best way to do ...

privmsg system db schema

I'm making a PM-system on my site. And I want to know ultimate db schema. I have always just used only 1 table. But my users have started complained that the messages in their outbox suddently dissapers =D Thats because if the other users deletes it, the one who sent it wont see it to. So im thinking of making another table with the sa...

SQL Server Blocking Issue

We currently have an issue that occurs roughly once a day on SQL 2005 database server, although the time it happens is not consistent. Basically, the database grinds to a halt, and starts refusing connections with the following error message. This includes logging into SSMS: A connection was successfully established with the server, bu...

DbDataReader with DbTransactions

Its the wrong way or lack of performance, using DbDataReader combinated with DbTransactions? An example of code: public DbDataReader ExecuteReader() { try { if (this._baseConnection.State == ConnectionState.Closed) this._baseConnection.Open(); if (this._baseCommand.Transaction ...

How to execute an update via SQLQuery in Hibernate

Hi, I need to update a joined sub-class. Since Hibernate doesn't allow to update joined sub-classes in hql or named-query I want to do it via SQL. I also can't use a sql named-query because updates via named-query are not supported in Hibernate. So I decided to use a SQLQuery. But Hibernate complaints about not calling addScalar(): ...

ORM or SQL in large, scalable and MAINTAINABLE web application?

I know there already are a lot of posts floating on the web regarding this topic. However, many people tend to focus on different things when talking about it. My main goal is to create a scalable web application that is easy to maintain. Speed to develop and maintain is far more appreciated BY ME than raw performance (or i could have u...

Muslim Hadeeth Database / CHM to CSV or SQL

I am looking here to create a Facebook App on Hadeeth. My first question is for muslim programmers here because other might not be having clue. Is it possible to find hadeeth in csv / sql (at least the popular ones like bukhari, muslim, tirmidhi) otherwise Do we have any tools or is it possible to convert CHM files into CSV or SQL ? T...

SQL Table Setup Advice

Hi all. Basically I have an xml feed from an offsite server. The xml feed has one parameter ?value=n now N can only be between 1 and 30 What ever value i pick, there will always be 4000 rows returned from the XML file. My script will call this xml file 30 times for each value once a day. So thats 120000 rows. I will be doing quite comp...

Is this possible with sql?

Is it possible to do something like this: INSERT INTO table(col1, col2) VALUES(something_from_another_table, value); With something_from_another_table being a SQL command? Like, is there something I can do that's equivelant to: INSERT INTO table(col1, col2) VALUES((SELECT value FROM table2 WHERE id = 3), value); ...

Spooling data to CSV truncates

Hi, I am using the below script to output data to a csv file: set heading off set linesize 10000 set pagesize 0 set echo off set verify off spool D:\OVERNIGHT\TEMP_FILES\PFRA_DETAIL_VIXEN_OUTPUT.txt SELECT TRIM(T4.S_ORG_ID)||','|| TRIM(T4.NAME)||','|| TRIM(T3.CREATION_TIME)||','|| TRIM(T5.X_HOUSE_NUMBER)||','|| TRIM(T5.X_FLAT_NUMBER...