Oracle partition by

I have been trying to understand the over partition by function present in sql, as I have wrote a query based on some parameters for which I'm getting multiple records for same id's. They query which I wrote was skeptically to fetch a person's details like his name, dob, address and more.. In this case I'm getting multiple records as the address of the user is more then one. For now I'm taking the preferred address, but it may happen that there ain't any preferred address for the user, so I won't get that person's details even if it matches to my given parameters. So I thin...

Has anyone had any success in unit testing SQL stored procedures?

We’ve found that the unit tests we’ve written for our C#/C++ code have really paid off. But we still have thousands of lines of business logic in stored procedures, which only really get tested in anger when our product is rolled out to a large number of users. What makes this worse is that some of these stored procedures end up being very long, because of the performance hit when passing temporary tables between SPs. This has prevented us from refactoring to make the code simpler.We have made several attempts at building unit tests around some of our key stored procedures ...

How to trace with SQL Server profiler

i am trying my hands on SQL server profiler i would like to know how to trace using SQL server profiler. any link with apprpiate GUI will be more then helpfull. Steps:SQL Server Management StudioGo to MS SQL Server Management Studio and select the database you want to trace in the Profiler.Right click the database and select New Query.In the query window type select db_id() then execute... and remember the number generatedSQL Server ProfilerFile - New Trace then choose server, Authentication Mode provide UserName and Password and click connect.In the trace properties go ...

What is wrong with my sql "group by" and "having" statement?

I keep getting an error saying this is not a group by expression when i run this statement in oracle XE.SELECT PROJECTID, HOURSWORKED FROM ASSIGNMENT GROUP BY PROJECTID HAVING HOURSWORKED > 20; GROUP BY is used for an aggregating expression, such as MAX() or SUM(). [XXX]You can either writeSELECT PROJECTID, HOURSWORKED FROM ASSIGNMENT WHERE HOURSWORKED > 20;orSELECT PROJECTID, SUM(HOURSWORKED) FROM ASSIGNMENT GROUP BY PROJECTID HAVING SUM(HOURSWORKED) > 20;(or some other aggregate function, eg: MAX, MIN) depending on your intent.WHERE is used to filter indivi...

Evaluate Dax expression at leaf level then sum up the results

I am trying to essentially mimic the behaviour of performing a T-SQL calculation inside a subquery and summing up the results of that calculation in an outer query, within DAX.At the leaf level I have the following simple calculation:ROUND((SUMX('Profile Category Views', 'Profile Category Views'[Visit]) * 1.52 ) , 0)For each row 'Profile Category Views'[Visit] = 1. This calculation is correct at the leaf level, however when i aggregate the results of the sum, the calculation is applied at run time, at a higher level, skewing the results.As shown in the image above - the ...

Could not get JDBC Connection with jdbc and Camel

I try to implement a simple example with camel, where I define routes to interact with a database using jdbc and sql.First, I write log4j.properties for the log:log4j.rootLogger=INFO, outlog4j.appender.out=org.apache.log4j.ConsoleAppenderlog4j.appender.out.layout=org.apache.log4j.PatternLayoutlog4j.appender.out.layout.ConversionPattern=[%30.30t] %-30.30c{1} %-5p %m%n#log4j.appender.out.layout.ConversionPattern=%d [%-15.15t] %-5p %-30.30c{1} - %m%nAfter I write in a file called sql.properties the queries:sql.insertNewTopic=INSERT INTO newtopic(TopicId, TopicName, url, Module...

Pagination and alternative solution

Let's suppose I have a table with 10 000 rows. Some user (for example on web site) wants to work with this table applying some where conditions and order by conditions.Variant #1 - using sql limit and usual paginationAs I understand database must 1)find all(!) rows that meet where conditions, 2) database must sort all(!) found rows at step 1 and from these found rows 3) it must return some set defined by limit and offset. Variant #2 - reading all ids in list structureAnother solution is do a first query to get list of ids for certain where conditions and order by conditions...

How to generate the weeks from current _date ( minus 12 months) and current _date (minus 7 days) in postgressql

This question already has an answer here: How to generate 52 weeks from current date using Postgresql 1 answer select week::date from generate_series( current_date - interval '12 month', current_date - interval '1 week', interval '1 week') q (week)Here is a dbfiddle demoSample output: week------------ 2016-05-24 2016-05-31 2016-06-07 ... 2016-06-28 2016-07-05 2016-07-12 2017-05-02 2017-05-09 2017-05-16(52 rows) [XXX]

Error at "dbms_output.put_line"?

I am creating a trigger in Oracle and we are stuggling with the syntax.I have run it through a code checker several times but it keep saying we have an error at "dbms_output.put_line".When we try to run it, the code says "trigger created with compilation errors"CREATE TRIGGER date_trigger BEFORE INSERT ON PrintJob FOR EACH ROWBEGIN IF StartDate > SysDate THEN BEGIN dbms_output.put_line ('Please check date'); END; Trigger can't reference table columns just by naming them - you'll need :old (or :new) pseudorecord qualifiers, such as in this modified example. It...

TVP insert shows many SP:Starting and SP:Completed events in SQL Server Profiler

I am profiling an insert query that takes a temporary table as parameter, and seeing that each item being inserted counts towards a SP:Starting and SP:Completed event in SQL Server Profiler.Here is roughly the query I am doing:DECLARE @temptable TABLE (id INT)INSERT INTO @temptable (id) VALUES (0), (1), (2), ..... (500)INSERT INTO TableOfInts (id)SELECT id FROM @temptableWhen I look at my SQL Profiler, I have a statement for each value I'm inserting in the real table from the temporary table:EventClass TextDataSP:Starting INSERT INTO TableOfInts (id)....SP:Completed INSE...

SQL Join Types and Performance: Cross vs Inner

So, I've been seeing a lot of SQL examples on this site. I have a question about the relative performance of inner joins (plain JOIN) and cross joins (SELECT foo FROM bar,baz WHERE). Turns out the question has already been asked:INNER JOIN ON vs WHERE clauseBut I still have an issue I'd like clarification on. I didn't see anything in the answers.The questions is this:Assume no fields are NULL. Given two equivalent queries, one formulated like this:SELECT * FROM t1JOIN t2 ON t1.t2_id=t2.t1_id AND t2.bar='baz'WHERE t1.foo='bar'And one formatted like this:SELECT * FROM t1,...

sql server executing update it takes more time

I have two tables (UserTable and UserProfile) and the Structure:create table userTable(id_user int identity(1,1) primary key , Name varchar(300) not null , Email varchar(500) not null , PasswordUser varchar(700) not null,userType int ,constraint usertype_fk foreign key(userType) REFERENCES userType(id_type)on delete set null)and userPtrofile: create table UserProfile( id_profile int identity(1,1) primary key , ClientCmpName varchar(300) null, Clientaddress varchar(500) null, phone varchar(50) null, descriptionClient varchar(400) null, img image null, ...

Combining Count and MIN functions

I have a part of my query as:SUM(POReceiptQuantity) as Receieved,MIN(ItemLocalStandardCost) as Low,MAX(ItemLocalStandardCost) as High,Received returns the total number of Items we sold this year. The LOW is the lowest price we paid, and High is the highest price we paid.I'm trying to incorporate a new column showing how many if the item we sold at the Low price. I tried to use Count along with Min function but it returns a "cannot perform an aggregate function on an expression containing an aggregate or a subquery"Does anyone have any ideas how i could go about thi...

Why doesn't SQL Full Text Indexing return results for words containing #?

For instance, my query is like the following using SQL Server 2005:SELECT * FROM Table WHERE FREETEXT(SearchField, 'c#') I have a full text index defined to use the column SearchField which returns results when using:SELECT * FROM Table WHERE SearchField LIKE '%c#%'I believe # is a special letter, so how do I allow FREETEXT to work correctly for the query above? The # char is indexed as punctuation and therefore ignored, so it looks like we'll remove the letter C from our word indexing ignore lists.Tested it locally after doing that and rebuilding the indexes and I get r...

How to phrase an answer, data is separated at the logic / programmatic / application layer?

How do I describe the partition of client data when all data is stored in one place and separated via programming?If a collection of data from various clients is stored in a variety of SQL tables and is separated via the code (E.g. members from different orgs defined by organisation table) at which layer is the data separation defined?Sorry if this question is a bit poorly worded. In terms of how to explain it, I'd need more information on how you're actually separating the data for consumption by different members, but we've done a similar thing using SQL views. In our ...

Using inner join, left outer join, cross apply get syntax error with Where Clause

We have a third party database. The contact information is contained in many tables: contact1, contact2, contsupp. Using inner join, left outer join, cross apply get syntax error with Where Clause. The query works without the left outer join. But I need that to select the correct email. Where should the where clause go in this case? SELECT 'P', 'NA' PEOPLE_ID, 'NA' PEOPLE_CODE_ID, T.ForeName, T.Middle_Name, T.Surname, 'PERM', 'N', GetDate(), GetDate(), ...

Page 1 of 287  |  Show More Pages:  Top Prev Next Last