I have the following db tables (which is simplified to illustrate the problem) CampaignTx campaignTx_id | member_id | date_created | shop_id 1 | 2 | 7/12/2009 | 2 2 | 4 | 7/13/2009 | 3 3 | 6 | 7/14/2009 | 4 4 | 5 | 8/14/2009 | 3 5 | 10| 8/19/2009 | 1 Reliability Reliability_id | campaignTx_id | status 1 | 3 | 0 2 | 2 | 1 3 | 4 |...
Is there any way to make NHibernate.Linq generate a like query on an integer field? The SQL that I want it to generate is: select IntegerColumn from Table where IntegerColumn like '%StringValue%' I've tried something like: from entity in _session.Linq<Entity> where entity.IntegerColumn.ToString().Contains(StringValue) select enti...
What will be the sequence of execution followed by SQL if a query has both group by and order by clause. Does it depend on their position in the query??? ...
I'm building simple application for myself in JSP which stores URL for me and finds it based on tags. For which i want to design a database. I'm limited with my knowledge of SQL. But still i want to learn by doing. I want to create database that stores tags for the URL and the URL itself. The URL could be text(50) or more in a table co...
Hello, I'm facing a new challenge here. I can't seem to find precedence for replication from MySQL, running on a Linux box to MS SQL Server. Has anybody done this before? Most importantly all changes made to the MySQL database should be replicated on the MS database realtime or close. MS database are not likely to be updated in any ot...
I've got a log table in SQL Server that looks like this: CREATE TABLE [dbo].[RefundProcessLog]( [LogId] [bigint] IDENTITY(1,1) NOT NULL, [LogDate] [datetime] NOT NULL, [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RefundId] [int] NULL, [RefundTypeId] [smallint] NULL, [LogMessage] [varchar](1000) COLLATE S...
When 2 decimal(30,10) numbers are divided in Sql Server 05, 2 last decimals seem to be getting lost (not even rounded off, simply truncated). For example: Declare @x decimal(30,10) Declare @y decimal(30,10) Declare @z decimal(30,10) select @x = 2.1277164747 select @y = 4.8553794574 Select @z = @y/@x select @z Result: 2.28196731...
I have a script that updates itself every week. I've got a warning from my hosting that I've been overloading the server with the script. The problem, I've gathered is that I use too many UPDATE queries (one for each of my 8000+ users). It's bad coding, I know. So now I need to lump all the data into one SQL query and update it all at o...
I have the following table: Comments -------- id PK cid content uid comment If the content is image /i want it to print ?imgID=$cid and get the data from the row title from the table images and if it's a thread I want it to print ?threadID=$cid and get the title from the table threads and so on. How should I do this? <h3...
I'm using a stored procedure in MySQL, with a CASE statement. In the ELSE clause of the CASE ( equivalent to default: ) I want to select and return an empty result set, thus avoiding to throw an SQL error by not handling the ELSE case, and instead return an empty result set as if a regular query would have returned no rows. So far I've...
Hi I need to update date a value in table if it does not exist then it must be inserted What is the best way to does this in MySql Currently I am using SELECT Id INTO LId FROM ATABLE WHERE ID = FID; IF LId IS NULL THEN INSERT INTO ATABLE(abc) Values (2) ELSE UPDATE ATABLE Set Abc = 2 Where Id = LId END IF; ...
I am converting some of the queries I have from CAML to SharePoint search SQL and ran across an issue when trying to compare my Date metadata fields to "Today." Specifically, I have the following part of a CAML query that uses : <Leq><FieldRef Name="Article_x0020_Publish_x0020_Date"/><Value Type="DateTime"><Today /></Value></Leq> Whe...
So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order: ORDER BY date_sent NULLS FIRST What is comparable in SQL Server? There are these alternatives, assuming the date values are NULL or in the past: ORDER BY ISNULL(date_sent, GETDATE()) DESC ORDER BY (CASE W...
My application serializes data into various XML attributes, and depending on data it might send it as text, or as base64. In the latter case, attribute name will be "attribute-base64". So, on SQL server side it is possible to use following convention to decode XML: declare @DataXml xml set @DataXml='<root v="test data"/>' ; --or: set @D...
I want to pull back information about a loan. One piece of information is a certain fee amount. If I simplify my query down to the loan number and fee amount, I still can not figure it out. The first query returns what I expect, one loan number and a 0 for the fee amount (fee was not applied) while the second one I can not get to work...
Im going through a project I have taken over, and on the database side I have noticed that the previous programmers have written a bunch of triggers to delete child records. The thing is, these records already have a a foreign key relationship with the parent record I am deleting. The delete triggers are nothing but simple delete state...
I am trying to write a report (DB2 9.5 on Solaris) to do the following: I have a set of data, let's say it's an order table. I want to run a report which will give me, for each month, the number of orders per customer, and their "rank" that month. The rank would be based on the number of orders. I was playing around with the RANK() OVER...
Hi, I have a table in which there are records about works accesing entrance doors. DECLARE @doorStatistics TABLE ( id INT IDENTITY, [user] VARCHAR(250), accessDate DATETIME, accessType VARCHAR(5) ) Sample records: INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:02:43.000','IN') INSERT IN...
Hi, Sorry about the title. That was the best I could come up with. Anyways, here is my question - I have 2 tables, a Customer table and Order table as shown below: Customer { Long id; String name; String address; Timestamp createdOn; } Order { Long id; String productName; Long customerId; Timestamp createdOn; } Ther...
I am just beginning to learn how to write software that accesses an SQL server. It seems that each server implementation (Postgres, MySQL, etc.) offers API libraries for various languages (my code is in C and C++, though solutions for Java and Python would also interest me). I'm a little wary of depending on these libraries, however, b...