I have a JRXML file that I use to generate a report built on a massive SQL query for an Oracle database. I have entered a parameter to the report that gets filled as an Integer. However, I would also like to reference this parameter as a String at a different point in the query. Is there a way I can convert the parameter from an Intege...
I want to update a table's column using the results of a stored procedure create procedure seq as DECLARE @NextValue INT; INSERT SequencesTable DEFAULT VALUES; DELETE SequencesTable WITH(READPAST); SELECT SCOPE_IDENTITY(); go I can't use a UDF since it is nodeterministic. Something like this won't work UPDATE [dbo].[t1] SET [c1]...
Here is the situation,each page will show 30 topics,so I had execute 1 sql statements at least,besides,I also want to show how many relpies with each topic and who the author is,thus I have to use 30 statements to count the number of replpies and use other 30 statements to find the author.Finally,I got 61 statements,I really worry about ...
I know in general it is a good practice to move as much processing as possible from Sql Server to the application (in my case ASP.NET). However what if the processing on the application level means passing 30+ extra parameters to the Sql Server. In this case is it worth moving the processing to the Sql Server? Here's the specific dilemm...
I have C# Client application calling Windows webservice written in WCF calling Sql Procedure and this proc give output around 1.3 million records then the C# client application keep them in memory and does all validations one by one I am getting error: System.Exception: An exception has occurred when recalculating the balances, ...
In light of the "Hidden features of..." series of questions, what little-known features of PL/SQL have become useful to you? Edit: Features specific to PL/SQL are preferred over features of Oracle's SQL syntax. However, because PL/SQL can use most of Oracle's SQL constructs, they may be included if they make programming in PL/SQL easier...
I'm using a SELECT statement in T-SQL on a table similar to this: SELECT DISTINCT name, location_id, application_id FROM apps WHERE ((application_id is null) or (application_id = '4')) AND ((location_id is null) or (location_id = '3')) This seems to work fine when searching for one application_id or one location_id, but what if I want...
According to MSDN the range for date datatype is January 1, 1753, through December 31, 9999 In SQL2005 the following is interesting: declare @date as datetime SET @date = '1/1/1901 12:00:00 AM' SELECT @date if (isnull(@date,0) = 0) SELECT 'date is null' ELSE SELECT 'date not null' change the date to '1/1/1901 12:00:00 AM' and sudden...
Hello, As I had written in title, I have SQL query, run on Oracle DB, lets say: SELECT * FROM TABLE WHERE TABLE.NAME Like 'IgNoReCaSe' If I would like, that the query would return either "IGNORECASE", "ignorecase" or combinations of them, how can this be done? Is this possible? ...
I am doing a testing scenario 6 people in each Site Site 1 - A, B, C, D, E, F Site 2 - G, H, I, J, K, L Site 3 - M, N, O, P, Q, R Site 4 - S, T, U, V, W, X I want to write a query that can suggests me datewise the people who can test the site - two at a time. Here are the rules: Rule 1: If a person has tested his site on Day 1, then...
I cannot figure out this complex join.. I have 3 tables: I want to join threads id with posts thread_id and users id with posts poster and threads poster.. Threads: ID int(10) Subject varchar(100) Message text Poster int(10) Posts: ID int(10) Poster int(10) Message text() Thread_id int(10) Users: ID int(10) username varchar(...
I am currently writing unit tests for an application near completion (before management looks at it and turns it inside out). I am testing the business layer and a method is set to return a datatable of all "GroupID"s, which are simply an integer to represent the group that a user belongs to. I'm assuming the best way to assert on this...
I'm trying to load data from an external csv file that needs to be massaged before being inserted into a SQL table. Reading the file is fine, but when I try to loop through the stream and load an array using the SPLIT function I get a "Expected End of Statement" error. Do While Not txtFile.AtEndOfStream strText = txtFile.ReadLine ...
I have a view that's currently defined as CREATE VIEW riders AS select ridelog.uid AS rid,sum(ridelog.distance) AS distance from ridelog group by ridelog.uid What I'd like to do is add a second field that shows the distance y-t-d. i.e, the sum of the distances for rides that occurred since January 1st of the current year. The problem ...
I have two tables (1) MonthlyTarget {SalesManCode, TargetMonthYear, TargetValue}; this table has 1966177 rows. (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; this table has 400310 rows. I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, De...
I have two tables (1) MonthlyTarget {SalesManCode, TargetValue}; (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, DecAch} What should be the query? ...
I'm trying to update an old Access database using SQL scripts, but I can't find the syntax for removing a default value from a column. Is this possible in Access? ...
I'm making a forum. I'm gonna store the id, subject, date, poster in a table named topics and keep the content in a table named posts. Now I just have to join them together nicely. And how should I do to connect the post with the topic? I haven't slept in 30 hours so I'm a little slow :P What do you guys think about this approach? ...
In my database, I have 3 tables that have 2 similar columns, Year and Month. These tables aren't linked by anything. What i want to do is select the distinct year and months from these tables. So where table 1 contains: 2009 MAY (multiple times) 2008 NOVEMBER (multiple times) 2007 MAY (multiple times) and table 2 and 3 contains: 20...
We keep getting this error randomly in our web application. System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured t...