the geek sista

Wednesday, March 3, 2010

SQL Tree Hierarchy

How to construct the tree hierarchy using SQL???
I was searching for this and found a great link, so I followed and it works!

Here's the snippet for my codes:

declare @AITree table 
(Node int NOT NULL IDENTITY(100, 1), AIType int,
parent varchar(20), child varchar(20), 
depth int NULL, lineage varchar(1000))

--insert whatever inside your table, u can ignore the AIType
insert into @AITree (AIType, child)
select AIType, ID from AI_Grouping 
WHERE effstartdate<=@treeDate and effendDate>=@treeDate
order by AIType, parent, child

--updating parent using the node (not my table)
UPDATE T
SET T.Parent=P.node
FROM @AITree T 
INNER JOIN AI_Grouping C ON T.child=C.ID and T.AIType = C.AIType 
INNER JOIN AI_Grouping PT ON C.parent=PT.child and C.AIType = PT.AIType 
INNER JOIN @AITree P ON PT.ID=P.child and P.AIType = PT.AIType 
WHERE C.effstartdate<=@treeDate and C.effendDate>=@treeDate AND
PT.effstartdate<=@treeDate and PT.effendDate>=@treeDate 

-- updating the top parent
UPDATE @AITree SET Lineage='/', Depth=0 WHERE parent Is Null

-- updating the rest of the nodes
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.Parent,6,0)) + '/' 
FROM @AITree AS T 
INNER JOIN @AITree AS P ON (T.Parent=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

WHILE EXISTS (SELECT * FROM @AITree WHERE Depth Is Null) 
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.Parent,6,0)) + '/' 
FROM @AITree AS T 
INNER JOIN @AITree AS P ON (T.Parent=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

--try this to output the treee
SELECT Space(T.Depth*2) + 
    case depth when 0 then '-' else '' end + 
    G.child AS Name
FROM AI_Grouping G INNER JOIN @AITree T 
ON G.ID = T.child 
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

Enjoy!
Reference: here

To get the number of occurrence of a character in SQL

Wonder how to get the number occurrence of a character in MS SQL?
Here's a trick on how to do it

declare @char varchar(1), @str varchar(100)
SET @char ='/'
SET @str = '/102///111/'
SELECT LEN(@str)-LEN(REPLACE(@str,@char,''))

Wala.. there u go, u will get 5 for the scenario above.

Tuesday, March 2, 2010

SQL Query case sensitive

How to make the case sensitive query?
There are 2 records in the database with currAbbr = ZAR and ZAr, in order to get the correct case sensitive comparision, u can use BINARY_CHECKSUM.

try this query:
select * from MYTable
where BINARY_CHECKSUM(currAbbr) = BINARY_CHECKSUM('ZAR')
select * from MYTable
where BINARY_CHECKSUM(currAbbr) = BINARY_CHECKSUM('ZAr')

Friday, February 12, 2010

SQL Joins

This is a good website to illustrate the SQL joins.
http://www.codinghorror.com/blog/archives/000976.html

Thursday, January 28, 2010

check .net version installed in your computer

to do this, simply create a html page and copy the content.

<Html>
<body>
<script>
alert(navigator.userAgent)
</script>
</body>
</html>

Then open the page on IE, it will prompt the .NET version.

Reference: here

Tuesday, January 5, 2010

ASP.NET Dropdown SelectedValue does not change?

Problem:
My dropdown selected value does not change.
It will always return the initialized value.

Solution:
Check if you put the initialization within the page.ispostback criteria.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            ddl.SelectedValue = "1"
        End If
    End Sub

Monday, December 28, 2009

'Sys' is undefined - ASP.NET 2.0 Ajax Control Toolkit

To fix this:
1. Open your web.config
2. Add this in between

<httpHandlers> </httpHandlers>

section

<add verb="GET" path="ScriptResource.axd" type="Microsoft.Web.Handlers.ScriptResourceHandler" validate="false"/>

Reference: here

The above would work fine, however it will give the following error:

 Could not load type 'Microsoft.Web.Handlers.ScriptResourceHandler'.
ScriptResource.axd

Some suggested to change the code added to the following

<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>

(Change the word Microsoft to System)

But it gave me this javascript error:
Sys.ArgumentTypeException. Object of type 'Sys._Application' cannot be converted to 'Sys._Application'

In order to solve this will need to add this ScriptMode="Release" on the ScriptManager tag, like this:

<asp:ScriptManager id="ScriptManager1" runat="server" ScriptMode="Release">
</asp:ScriptManager>

Reference: here