Database

It's all fixed now. I now know for a fact(tested) that even the ORDER BY SUM(QualityData.NrOfDefects); does not cause a recalculation.

I can see a requirement for a custom SQL UI. We also make extensive use of stored proc and I find scrolling through 500+ procs irritating in the extreme. And if you miss name one it can be a royal PITA trying to find the bloody thing. Go annoy Red-Gate, they make some excellent tools and may well be interested in the idea. Never underestimate the power of human stupidity RAH

The responses you have assume that the databases are on the same server, in which case database.owner.tablename will work. If they are on different servers you have a problem, you need to set up linked servers, something I hate doing as the server is then hard coded into the query. Never underestimate the power of human stupidity RAH

Good morning Sir to you too. :) Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

Creamboy wrote: However, i need to disallow the same name inputed again. That would be a Unique Constraint, somewhat similar to this example; ALTER TABLE [MyTable] ADD CONSTRAINT [UniqueConstraintName] UNIQUE (ColumnValue) I are Troll :suss:

No, Sorry about that. My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.

Thank you for your Reply. Last night i slept over it and i came back with this /****** Object: StoredProcedure [dbo].[sp_Traverse_Tree_Special] Script Date: 02/16/2010 22:54:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Traverse_Tree_Special] @curr int with recompile AS set nocount on if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes]')) drop table [#Nodes] if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes_FINAL]')) drop table [#Nodes_FINAL] if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#SemiFinal]')) drop table [#SemiFinal] if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Surt1]')) drop table [#Surt1] if exists (select \* from \[tempdb\].\[dbo\].sysobjects where id = object\_id(N'\[tempdb\].\[dbo\].\[#Children\]')) drop table [#Children] Create table [#Nodes] (id int IDENTITY(0,1), NodeID int, parent int, [Description] varchar(128), refParent int ) Create table [#Nodes_FINAL] (id int IDENTITY(0,1), id2 int , NodeID int, parent int, [Description] varchar(128), refParent int ) INSERT INTO #Nodes (NodeID, Parent, [Description]) select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description] from tbl_node n inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of') where n.curr = @curr union select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr from tbl_node n inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject' inner join tbl_modl m on m.id = n.modl where n.curr = @curr select distinct nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent into #nodes2 from #Nodes nP left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent order by refParent,nP.Description WITH CTENodes AS ( SELECT ID, NODEID, PARENT, DESCRIPTION, REFPARENT, CAST(ROW_NUMBER() OVER(ORDER BY id ) AS VARCHAR(MAX)) NodePath FROM #nodes2 WHERE REFPARENT is null UNION ALL SELECT c.ID, c.NODEID, c.PARENT, c.DESCRIPTION, c.REFPARENT, NodePath + '.' + CAST(ROW_NUMBER() OVER(ORDER BY C.id ) AS VARCHAR(MAX)) NodePath FROM CTENodes AS P JOIN #nodes2 AS C ON C.REFPARENT = P.id

Again, your point is? I know the language. I've read a book. - _Madmatt

I think this issue of left outer join so go through below link once may be this will help you http://www.devsource.com/c/a/Languages/Defining-a-Left-Join-with-LINQ-Queries/1/[^]

It is good form to show what you have tried, or even describe the solution that you "think" may be along the right lines. Unfortunately, you did neither. Therefore it is difficult for anyone to answer your question - we are not mind readers. (My first reply to you was to useless information because that is what I had to go on.) If you had said in the op that you had looked at various sites and MSDN then the response would have been warmer. It did appear however that you did neither. I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

Tridip Bhattacharjee wrote: how to query address field in my xml or how to query Options field,how query CompanyOrName. A XML-query will look somewhat more complicated then a "regular" query. I hope that the example below provides a good impression of the used construct; DECLARE @ShipXML XML SET @ShipXML = '<OpenShipments> <OpenShipment> <NumberOfPackages>1</NumberOfPackages> <ShipmentActualWeight>5</ShipmentActualWeight> <DescriptionOfGoods>Car Parts</DescriptionOfGoods> </OpenShipment> <OpenShipment> <NumberOfPackages>2</NumberOfPackages> <ShipmentActualWeight>3</ShipmentActualWeight> <DescriptionOfGoods>Bike Parts</DescriptionOfGoods> </OpenShipment> </OpenShipments>' SELECT shs.sh.value('NumberOfPackages[1]','bigint') AS NumberOfPackages ,shs.sh.value('ShipmentActualWeight[1]','bigint') AS ShipmentActualWeight ,shs.sh.value('DescriptionOfGoods[1]','varchar(20)') AS DescriptionOfGoods FROM @ShipXML.nodes('OpenShipments/OpenShipment') shs(sh) WHERE shs.sh.exist('.[DescriptionOfGoods != "Car Parts"]') = 1 The example was built using sample-code from MSDN[^]. I are Troll :suss:

One of the (minor) reasons we dumped (on) Crystal was the export to excel was so crappy. We use SSRS and the excel export is one hell of a lot better than Crystal used to be. Having said that SSIS does not support data dumps, it shouldn't it is a REPORTING tool. I repeat this line to my management weekly, it does not help. They still insist that data dumps are a requirement. So one of our devs has extended SSRS to export csv files via RS. This is not possible in CR and is pretty ugly even in SSRS, it means hacking the UI to a custom page that calls a stored proc that writes the file to a folder and then displays a link for the user to download the file. As I said ugly, but it works without having to pull major volume to the UI to export. Never underestimate the power of human stupidity RAH

So one of your fields is in this data format, check each field in the join and change the data type of the offending field. Never underestimate the power of human stupidity RAH

You are right. Thanks :) Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Hi, This query dose not answer because RegNo in Table_B in Id 4 dose not equal with RegNo with Table_A, But if it's correct this is answer select a.RegNo,a.Name,count(a.Class),b.LastUpdated from Table_A as a left join Table_B as b on a.RegNo=b.RegNo where (b.Id between 3 and 4 ) or (a.RegNo Not In('16RTY')and a.RegNo In('14DFG','15FHG')) group by a.RegNo,a.Name,b.LastUpdated

The query with the distinct is below. However, this returns an error unless distinct is removed. I am using Provider=Microsoft.Jet.OLEDB.4.0 SELECT DISTINCT ID_BB_SEC_NUM_DES FROM Curncy_FORWARD2010020801.csv WHERE ID_BB_SEC_NUM_DES LIKE '%HKD%' AND NAME NOT LIKE '%/%' AND NAME NOT LIKE '%+%' AND UCASE(NAME) NOT LIKE '%FWD POINTS%' AND UCASE(NAME) NOT LIKE '%IMPLIED YLD%' AND UCASE(NAME) NOT LIKE '%OUTR%' AND LEN(ID_BB_SEC_NUM_DES) BETWEEN 5 AND 10 AND UCASE(MARKET_SECTOR_DES( = 'CURNCY' AND SECURITY_TYP='FORWARD' AND ID_BB_SEC_NUM_SRC IS NOT NULL AND RIGHT(ID_BB_SEC_NUM_DES,1) IN ('W','M','Y') ORDER BY RIGHT(ID_BB_SEC_NUM_DES,1)<>'W' DESC, RIGHT(ID_BB_SEC_NUM_DES,1), LEFT(RIGHT(ID_BB_SEC_NUM_DES,LEN(ID_BB_SEC_NUM_DES)-3),LEN(ID_BB_SEC_NUM_DES)-4)+0

Where null is null or null = null Evaluates to Where true or false Evalutes to Where true where isNull(@Year, [Year]) = [Year] evaluates to (is @year is null and [year] is null) where IsNull(Null, Null) = Null evaluates to Where Null = Null evaluates to Where false I hope this helps Wout Louwers