Better for SQL to work out values or use VB code?

[Arg81]

One of my tables has 6 particular fields.

Well, this is actually 12, as each "field" has a min and a max value. This is known as the "range". I then need to program outside the range, for "action" and "reject".

So it'll look something like;

Reject -- Action max -- Action min -- Range min -- Range max -- Action min -- Action max -- Reject

Now, the table has the Range min and the Range max values stored already.

I'll try and explain this the best I can

Reject -- Action Max -- Action Min -- Range Min

Action Min = Range Min
Action Max = Range Min - 2
Reject = Range Min - 2

Range Max -- Action Min -- Action Max -- Reject

Action Min = Range Max
Action Max = Range Max + 2
Reject = Range Max + 2

Sooo....a row would in theory look like this;

Reject -- Action Max -- Action Min -- Range Min -- Range Max -- Action Min -- Action Max -- Reject

7 -- 7 -- 9 -- 9 -- 10 -- 10 -- 12 -- 12

now, that may look weird, but I then need to add validation in my program - example:

IF value < 7 THEN do not allow any more rows to be added.

IF value >= 7 and <9 THEN flag a comments box as Action is required

IF value >=9 and <10 THEN do nothing

IF value >=10 and <12 THEN flag a comments box as Action is required

IF value >=12 THEN do not allow any more rows to be added.


Make sense yet?

OK, question is, would it be easier for me to
(a) set a datatable up in my app that pulls the Range values across, and then creates "on the fly" columns for the other values?
(b) on the form, create a sub that uses the label.text and works out the other values?

with (b), I would require the values to change as the value in a combobox is changed (just like the databinding would from (a) )

Regards,

 

[giadich]

I didn't take a real good look that the logic you have but let me throw in my two cents.

As a general rule of thumb, you would want to make the decision to let SQL server carry out the work load or not based on:

a. How much data is involved?
b. What is the distance the data has to travel to get to the client machine?
c. How many users are connected to the SQL server?
d. What kind of CPU/RAM you have on the server?
e. What kind of CPU/RAM you have on the client machine?
e. How much impact would it have on the other users if you tie up the SQL server?

If you have a DBA available, don't forget to get him involved in making such a decision. Two heads are always better than one.

 

[Arg81]

If you have a DBA available, don't forget to get him involved in making such a decision. Two heads are always better than one

Wish I had 2 heads....(DBA, Tech Support, Programmer, Network Admin etc etc = me)

 

[cjard]

Action max and reject are identical?
Action min and (other reject) are identical?

You'd have your mins and maxes in another table, one row, no join condition

That way you:
a) have one place only to update ranges
b) dont store the same range thousands of times pointlessly


In your logic, you'd do in vb:

IF value < 7 OrElse value >=12  THEN do not allow any more rows to be added.

ElseIF value <9 value >=10 THEN flag a comments box as Action is required

 

[Arg81]

Action max and reject are identical?
Action min and (other reject) are identical?

yep. the only thing at moment being stored in the DB is the range values.

Not sure what you mean by

You'd have your mins and maxes in another table, one row, no join condition

I can't see there being any more than 20 sieves in the database. So there will only be 20 rows that need these values working out.



I've got my logic sorted out although it may be overcomplicated and can be cut down..I had to use converts as I orginally was using Option Strict On to test something, but I've now turned that off as it wasn't needed in the end.

Dim intValue As Decimal = CDec(Me.txtMoistNIR.Text)

            If intValue < CDec(Me.lblRejectLowNIR.Text) Then
                Me.panelReject.Location = New System.Drawing.Point(0, 3)
                Me.panelReject.Visible = True
                Me.txtRejectComments.Focus()
                Me.panelResult.Visible = False
            ElseIf intValue >= CDec(Me.lblActionMinLowNIR.Text) AndAlso intValue < CDec(Me.lblActionMaxLowNIR.Text) Then
                MessageBox.Show("action low")
'put rest of code here for action
            ElseIf intValue >= CDec(Me.lblActionMinHighNIR.Text) AndAlso intValue < CDec(Me.lblActionMaxLowNIR.Text) Then
                MessageBox.Show("action low")
'put rest of code here for action
            ElseIf intValue > CDec(Me.lblRejectHighNIR.Text) Then
                Me.panelReject.Location = New System.Drawing.Point(0, 3)
                Me.panelReject.Visible = True
                Me.txtRejectComments.Focus()
                Me.panelResult.Visible = False
            End If

 

[cjard]

You should always, always, always program with Option Strict On
Turning it off means you turn over control of things like type-conversion to VB's guesswork. If the code is complex to look at, convert them ahead of time and store them in suitably typed variables

Dim seivePriceDbl As Double = Convert.ToDouble(myRow.SievePriceFloat)

Now use seivePriceDbl..

 

[cjard]

You'll do yourself a big favour if you name these columns better:

RejectBelow, ActBelow, ActAbove, RejectAbove


The range is implicit and hence absolutely unnecessary
The Action max (in either direction) are also unneeded

Store them as numbers, damnit!

You can hence run different queries:

SELECT
  seive.*
  CASE 
    WHEN seiveVal < rejectBelow or seiveVal > RejectAbove THEN 'REJECT'
    WHEN seiveVal < actBelow or seiveVal > actAbove THEN 'ACTION'
    ELSE
      'VAL_OK'
  END as decision
FROM
  seive

Or FillByRejectNeeded:

SELECT
  seive.*
FROM
  seive
WHERE
  seiveVal < rejectBelow or seiveVal > RejectAbove

etc..

Stop confusing your issue (and hence yourself and your user) by giving the same values different names needlessly

 

[Arg81]

You'll do yourself a big favour if you name these columns better:

RejectBelow, ActBelow, ActAbove, RejectAbove

Yeah I know, was a little tired whilst writing the post out y'day

Are you suggesting these go into my "sieve" table as well then?

Store them as numbers, damnit!

I need the decimal point, hence why i set it to decimal. Unless I'm being thick and you can use a decimal point in number? (I thought it rounds down / up)

Stop confusing your issue (and hence yourself and your user)

I get confused easily

Attached is a screenshot of all the values I need to get from this. It's not just 1 set of "2 range values", it's 6 sets. Again, I may have got confused with what you're trying to suggest

 

[cjard]

Here.. Ive de-complicated your UI seeing as most the text boxes were showing identical values anyway:

 

[cjard]

Yeah I know, was a little tired whilst writing the post out y'day

Are you suggesting these go into my "sieve" table as well then?

Yep.

I dont know what a sieve is, but so long as the sieved thing must always be in that range, then its ok

I need the decimal point, hence why i set it to decimal. Unless I'm being thick and you can use a decimal point in number? (I thought it rounds down / up)

OK.. Well, System.Decimal is used to map to numbers in a database often. What the db does witha number depends on the SCALE and PRECISION. PRECISION = how many digits. SCALE = how many digits are decimal places

Scale = 2, Precision = 10

Stores:
12345678.90

I get confused easily

You should try being me, reading your post