The Solution To Yesterday’s Problem

This unusual Saturday blog post is to discuss yesterday’s issue and demonstrate what I did to solve it. It is ugly. It is not “clean”. But I was happy to get it out the door because I almost didn’t!

I also wanted to thank you as a group all of you who came to help me when I was facing a task I was not sure how to handle. I knew how to do it but I didn’t know how to do it well is the best way to describe what happened here.

First a little background. I build a SQL statement on the fly with which the “Operators” they choose are things like Period = 2009. This is a good example here in XML format.

<Operators>
    <ID>-1</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>465</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>0</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>476</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>1</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>482</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>2</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>471</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>3</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>473</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>4</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>474</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>5</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanShortName</ColumnName>
    <DataType>varchar</DataType>
    <Operator>= (Non Numeric)</Operator>
    <OperatorValue>PAM</OperatorValue>
    <ADV>AND</ADV>
    <IsSort>false</IsSort>
    <Hide>false</Hide>
  </Operators>

These rows are populated by user choices.

 http://cid-a0d71e1614e8dbf8.skydrive.live.com/embedrowdetail.aspx/BlogImages|52|6/rw.JPG

 

 

 

Up until Wednesday I thought I was done. What i had forgotten was if the columns that were the same but had the OR ADV were not grouped together with a parenthesis at the beginning and end the SQL statement would not be accurate.This became critical this month as MTD reports that were being generated also included the previous months totals because I had not given SQL the correct logic. What was being generated by my code was this: (That that was in italics is what thsi code was generating. )

CREATE VIEW [20090321143859]
AS
SELECT [DB].[dbo].[Employee].[EMPID]AS C0,
[DB].[dbo].[Employee].[Lastname]AS C1,
[DB].[dbo].[Employee].[Firstname]AS C2,
[DB].[dbo].[Result].[Period]AS C3,
[DB].[dbo].[Result].[PlanID]AS C4,
[DB].[dbo].[Result].[ElementShortName]AS C5,
[DB].[dbo].[Result].[AllocatorShortName]AS C6,
[DB].[dbo].[Result].[MTD]AS C7,
[DB].[dbo].[Result].[YTD]AS C8,
[DB].[dbo].[Result].[PlanShortName] AS C9,
[DB].[dbo].[Employee].[Ending] as OE0,
[DB].[dbo].[Employee].[Starting] as OE1 FROM  [DB].[dbo].[Employee] INNER JOIN
[DB].[dbo].[Result] ON
[DB].[dbo].[Result].[EmployeeID] =
[DB].[dbo].[Employee].[ID]
WHERE [DB].[dbo].[Employee].[Ending] >= 200902 AND
[DB].[dbo].[Employee].[Starting] <= 200902 AND
[DB].[dbo].[Result].[Period] = 200902 AND
[DB].[dbo].[Result].[PlanID] = 474 OR [DB].[dbo].[Result].[PlanID] = 473 OR [DB].[dbo].[Result].[PlanID] = 471 OR
[DB].[dbo].[Result].[PlanID] = 482 OR [DB].[dbo].[Result].[PlanID] = 476 OR [DB].[dbo].[Result].[PlanID] = 465
AND [DB].[dbo].[Result].[PlanShortName] = ‘PAM’

when want I wanted was this:

CREATE VIEW [20090321143859]
AS
SELECT [DB].[dbo].[Employee].[EMPID]AS C0,
[DB].[dbo].[Employee].[Lastname]AS C1,
[DB].[dbo].[Employee].[Firstname]AS C2,
[DB].[dbo].[Result].[Period]AS C3,
[DB].[dbo].[Result].[PlanID]AS C4,
[DB].[dbo].[Result].[ElementShortName]AS C5,
[DB].[dbo].[Result].[AllocatorShortName]AS C6,
[DB].[dbo].[Result].[MTD]AS C7,
[DB].[dbo].[Result].[YTD]AS C8,
[DB].[dbo].[Result].[PlanShortName] AS C9,
[DB].[dbo].[Employee].[Ending] as OE0,
[DB].[dbo].[Employee].[Starting] as OE1 FROM  [DB].[dbo].[Employee] INNER JOIN
[DB].[dbo].[Result] ON
[DB].[dbo].[Result].[EmployeeID] =
[DB].[dbo].[Employee].[ID]
WHERE [DB].[dbo].[Employee].[Ending] >= 200902 AND
[DB].[dbo].[Employee].[Starting] <= 200902 AND
[DB].[dbo].[Result].[Period] = 200902 AND
( [DB].[dbo].[Result].[PlanID] = 474 OR [DB].[dbo].[Result].[PlanID] = 473 OR [DB].[dbo].[Result].[PlanID] = 471 OR
[DB].[dbo].[Result].[PlanID] = 482 OR [DB].[dbo].[Result].[PlanID] = 476 OR [DB].[dbo].[Result].[PlanID] = 465 )
AND [DB].[dbo].[Result].[PlanShortName] = ‘PAM’

 

These statements obviously produce wildly different results. The first virtually ignores the dating parameters I set and now that we were in February this bug was discovered because it was seen picking up January’s results too.

So I thought no big deal I will just loop through the table find the columns with the same name, write those values first then write the other rows that weren’t written (that ADV has AND values) and it will be an easy fix. NOT!! First there was the issue what if there was more than one group of column that had multiple OR statements? Then there was the maddening logic required to determine where my For and Next statements were going. A co worker suggested a dataview. Well I would have loved to do that except I was running out of time. Even if I had accomplished by the dataview sort method what I wanted I was still going to have to determine what row had already been added and what had not been. 

What I thought would be easy turned out to be ….. well kind of a nightmare.

Anyway so here’s the code solution I came up with. Like I said, it ain’t pretty and it ain’t clean. But it seems to work. Feel free to leave suggestions on how to improve it. And I am sorry I was not more clear initially to those of you who were confused. I was in a rush and sometimes we don’t explain things too well when in that situation. Feel free to knock this code or improve on it. I know it is bad.

You will notice a function called “FormatOperator” in this code. That is in reference to another function that based on the Operator value of the dtOperators table row it returns a string determing how it should be stated in the SQL statement (if its a date, integer, double etc).

Well tonight I am off to have some Chicago style pizza at UNO’s and go to a coffee house and enjoy the music there. It has been a long week. Again thank you all for your help!

Sub HelpMe()

        Dim i As Integer

        Dim j As Integer

        Dim holdtable As New DataTable

        holdtable = Me.dtOperators.Clone

        For i = Me.dtOperators.Rows.Count – 1 To 0 Step -1

            If Me.dtOperators.Rows(i).Item("ADV") = "AND" Then

            Else

                holdtable.Rows.Clear()

                holdtable.AcceptChanges()

                Dim row As DataRow = holdtable.NewRow

                row.Item("DBName") = Me.dtOperators.Rows(i).Item("DBName")

                row.Item("TableName") = Me.dtOperators.Rows(i).Item("TableName")

                row.Item("ColumnName") = Me.dtOperators.Rows(i).Item("ColumnName")

                row.Item("Operator") = Me.dtOperators.Rows(i).Item("Operator")

                row.Item("OperatorValue") = Me.dtOperators.Rows(i).Item("OperatorValue")

                row.Item("ADV") = "OR"

                holdtable.Rows.Add(row)

                For j = Me.dtOperators.Rows.Count – 1 To 0 Step -1

                    If Me.dtOperators.Rows(j).Item("ADV") = "AND" Then

                    Else

                        If j = i Then

                        Else

                            If dtOperators.Rows(i).Item("DBName") = dtOperators.Rows(j).Item("DBName") _
                            And dtOperators.Rows(i).Item("TableName") = dtOperators.Rows(j).Item("TableName") _
                            And dtOperators.Rows(i).Item("ColumnName") = dtOperators.Rows(j).Item("ColumnName") Then

                                Dim row1 As DataRow = holdtable.NewRow

                                row1.Item("DBName") = Me.dtOperators.Rows(j).Item("DBName")

                                row1.Item("TableName") = Me.dtOperators.Rows(j).Item("TableName")

                                row1.Item("ColumnName") = Me.dtOperators.Rows(j).Item("ColumnName")

                                row1.Item("Operator") = Me.dtOperators.Rows(j).Item("Operator")

                                row1.Item("OperatorValue") = Me.dtOperators.Rows(j).Item("OperatorValue")

                                row1.Item("ADV") = "OR"

                                holdtable.Rows.Add(row1)

                                Me.dtOperators.Rows(j).Delete()

                            End If ‘ Matching If of seeing if j and i meet

                        End If ‘If j = i if statement

                    End If ‘Second Adv loop j

                Next ‘second for next j

                ‘write rows to string actions then delete row

                Me.dtOperators.Rows(i).Delete()

                Me.dtOperators.AcceptChanges()

                i = i – (holdtable.Rows.Count – 1)

            End If ‘if it matched ADV loop i
            Dim k As Integer
            If holdtable.Rows.Count – 1 > -1 Then
                sql = sql & "( "
                For k = 0 To holdtable.Rows.Count – 1
                    ‘If i <> holdtable.Rows.Count – 1 Then
                    sql = sql & Me.FormatOperator(holdtable.Rows(k).Item("DBName"), holdtable.Rows(k).Item("TableName"), _
                                      holdtable.Rows(k).Item("ColumnName"), holdtable.Rows(k).Item("Operator"), _
                                      holdtable.Rows(k).Item("OperatorValue").ToString) & " " & holdtable.Rows(k).Item("ADV").ToString & " "
                Next

                sql = sql & ") "
                sql = sql.Replace("OR ) ", ") " & vbCrLf)

                If Me.dtOperators.Rows.Count – 1 <> -1 Then
                    sql = sql & " AND "
                End If
            End If

        Next ‘i loop
        Me.dtOperators.AcceptChanges()

        ‘Now writing the "AND" values that are left
        For i = 0 To Me.dtOperators.Rows.Count – 1
            If i <> Me.dtOperators.Rows.Count – 1 Then
                sql = sql & Me.FormatOperator(Me.dtOperators.Rows(i).Item("DBName"), Me.dtOperators.Rows(i).Item("TableName"), _
Me.dtOperators.Rows(i).Item("ColumnName"), Me.dtOperators.Rows(i).Item("Operator"), _
Me.dtOperators.Rows(i).Item("OperatorValue").ToString) & " " & Me.dtOperators.Rows(i).Item("ADV").ToString & vbCrLf
            Else
                sql = sql & Me.FormatOperator(Me.dtOperators.Rows(i).Item("DBName"), Me.dtOperators.Rows(i).Item("TableName"), _
Me.dtOperators.Rows(i).Item("ColumnName"), Me.dtOperators.Rows(i).Item("Operator"), _
Me.dtOperators.Rows(i).Item("OperatorValue")) & vbCrLf
            End If
        Next
    End Sub

 

 

 

Technorati Tags: ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: