[T-SQL] Complex hierarchy: how to build? |
- Complex hierarchy: how to build?
- concatenate with leading zeros
- Function Vs Stored Procedure
- Decimal result not getting properly.
- Compatibility 2005 vs 2008 TVP and Merge
| Complex hierarchy: how to build? Posted: 09 Sep 2013 03:59 AM PDT (SQL SERVER 2008 R2)Hi guys,I need some help on how to retrieve Hierarchy in a table.In this case, the example is more complex than the the traditional one: I mean, the hirarchy stops and restarts when along the chain a special value type is encountered:To make an example:The Chain:Px: ParentsCx: ChildsP1 -- C1 -- C2 -- P2 -- C3 -- C4 --C5 -- C6 -- P3 -- C7Hi need to create a hierarchy like:P1 C1 (P1 is parent of C1, C1 is child of P1)P1 C2 (P1 is parent of C2, C2 is child of P1)P2 C3 (P2 is parent of C3, C3 is child of P2)P2 C4 .... and so onP2 C5P2 C6P3 C7It means that every time a Px is encountered, the hierarchy must be stopped and started with a new hierarchy.The above is only an example. The chain is more complex (eg: several chains, the number of <Px> for every chain is unknown, and the number of child <CX> between <Px> is unknown)Do you know how to build it?Many thanks |
| concatenate with leading zeros Posted: 09 Sep 2013 07:05 AM PDT Hi Everyone I am creating a view which involved concatenation of 2 int columns.The data in the columns look like Column 1 Column 21234 112345 11I am trying to get the following output001234001012345011So the first column should have zeros padded to the front to make 6 numbers, the second column should be 3 numbers long with zeros in front. So when added together it is 9 numbers long. Thanks in advance. |
| Posted: 09 Sep 2013 04:40 PM PDT Hi Every one,We all Knows that we call function through select and Sp through Exec.My Question is why can't we call the Sp through Select .is there any hidden Reason behind that? |
| Decimal result not getting properly. Posted: 09 Sep 2013 04:48 PM PDT Hi,Below query displays result as 0.00 but i want to show as 0.73SELECT ROUND(CAST (22/30 AS decimal (6,2)),-2);Please suggest where m going wrong.Thanks,Abhas. |
| Compatibility 2005 vs 2008 TVP and Merge Posted: 09 Sep 2013 03:40 AM PDT I am not sure if I am doing something wrong, but I am getting an issue that seems to be part of the compatibility level on a 2008 server.If I set the DB Level to 90 I can create a TVP[code="sql"]ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 90GOIF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TreeSortList' AND ss.name = N'dbo') DROP TYPE [dbo].[TreeSortList]GOCREATE TYPE [dbo].[TreeSortList] AS TABLE( [SortId] [uniqueidentifier] NOT NULL, [TheSort] [int] NOT NULL)GO[/code]I would have expected that to fail. I can even use that TVP with the compatibility set to 2005In the next example because of the example above I would expect Merge to work the same way and it doesn't[code="sql"]ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 90GOCREATE TABLE #Test (TheID uniqueidentifier, TheSort int) GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Save_ProjectTreeSort]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[Save_ProjectTreeSort]GOIF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TreeSortList' AND ss.name = N'dbo') DROP TYPE [dbo].[TreeSortList]GOCREATE TYPE [dbo].[TreeSortList] AS TABLE( [SortId] [uniqueidentifier] NOT NULL, [TheSort] [int] NOT NULL)GOCREATE PROCEDURE [dbo].[Save_ProjectTreeSort]@TreeSortList [TreeSortList] READONLYASSET NOCOUNT ON MERGE #Test AS [Test] USING @TreeSortList AS [TSL] ON TSL.[SortId] = Test.TheID WHEN MATCHED THEN UPDATE SET Test.[TheSort] = TSL.[TheSort] WHEN NOT MATCHED THEN INSERT ([TheID], [TheSort]) VALUES ([SortId], [TheSort]);--endregionGO[/code]You get the following errorIncorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.When you change the compatibility level to 100 the error goes away and the sproc is created. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment