Monster select statement revisited

I have a database with a date of birth. From this, I need to select records that include a large number of variables including records where an individual is older than a selected age and/or younger than a selected age, and even worse, of a given astrological sign if one is selected.

I'm currently using a strongly typed dataset. Is this something that's do-able with a dataset, or should I build the select string on the form programmatically? I have an ASP site that I'm re-writing in ASP.NET 2.0. In the ASP site the select string includes the code below. I just don't see how I can do this in a dataset.

if (strLowerAge <> 18) or (strUpperAge <> 99) then
If (strLowerAge <> "") Then
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ")"
End If
If (strUpperAge <> "") Then
If (strSQL <> "") Then
strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
End If
end if
End If
If (strAstSign <> "") Then
Select Case strAstSign
Case "Aries 3/21 - 4/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m, [DATE OF BIRTH]) = 3 AND DATEPART(d, [DATE OF BIRTH]) > 20 ) OR (DATEPART(m, [DATE OF BIRTH]) = 4 AND DATEPART(d, [DATE OF BIRTH]) < 20 )"
End If
Case "Taurus 4/20 - 5/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Gemini 5/21 - 6/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Cancer 6/21 - 7/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Leo 7/23 - 8/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Virgo 8/23 - 9/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Libra 9/23 - 10/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Scorpio 10/23 - 11/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Sagittarius 11/22 - 12/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Capricorn 12/22 - 1/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 )"
End If
Case "Aquarius 1/20 - 2/18"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 )"
End If
Case "Pisces 2/19 - 3/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
End Select
END IF

Diane

I have made sql statements like this, and they are a pain in the butt. I cannot comment on how to do this in a dataset in ASP.NET 2.0, but this is how I would do it:

Firstly, I would use a stringBuilder to create this string. It is more effiecient that a string.

Secondly, I see that you have a lot of If (strSQL <> "") Then ... Else ... End If statements. To combat this, I make sure that the strSQL is not empty in the first place. This way you do not need to check if it is empty or not. In your case, I would put the following at the top:

Dim strSQL as new StringBuilder("SELECT .... FROM members WHERE NOT [FIRST NAME] IN NULL ")

Then all you have to do is append to this every time e.g:

Dim strSQL as new StringBuilder("SELECT " & _" [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE]" & _" FROM members " & _" WHERE NOT [FIRST NAME] IS NULL" )If (strLowerAge <> 18) or (strUpperAge <> 99) then If (strLowerAge <> "") Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') >= " & strLowerAge & ")")End If If (strUpperAge <>"")Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') <= " & strUpperAge & ")")End ifEnd If

That should make you code a little shorter and easier to handle. To get the finall string, just use Dim mySQL as String = strSQL.toString().

If you really want to make it effiecient, the I would do all of this in the database as a stored procedure. If you are using SQL Server, you can build SQL statements as string and then execute them within a stored procedure. e.g.

CREATE PROCEDURE mySQLBuilder (@.sdtDOBsmalldatetime@.otherVariableint@.vSortColumn@.vSortDirection@.vWhereStr)ASDECLARE @.sqlvarchar(1000)SET @.sql ='SELECT ... FROM ...' + @.vWhereStr +' ORDER BY ' + @.vSortColumn +' ' + @.vSortDirectionEXEC (@.sql)GO

Just treat the stored procedure as you would a select statement.

HTH

Jag

|||

Thank you Jag, you gave me a lot of useful information.

I have never used a stored procedure. It sounds like I would have the same problem though that I would have with the dataset - the astrology sign which is not in the database and needs to be calculated. Unless I have 12 different procedures, 1 per sign, I need to some how include an 'if then' in the SELECT statement. Is that doable?

It gets even better. Currently, in order to give the members better exposure, I have the order clause dependent on the day of the week. One day O oder by last name, another by first name, another by date of birth, another by state, etc. So I would need 84 different procedures. Right?

Diane

|||

As you haven't used stored procedures before, I would leave that for now and go the way you are going. You can do 'if then' statements in stored procedures and use them to build a select statement (like you did in VB), but you should start off with easier things than this.

To learn about stored procedures, just do a google search for 'tutorial stored procedures'. You should get some good ones. They are mostly used for database input, but you can use them for data retrieval as well. They are more efficient that adhoc SQL statements as they are compiled in the SQL database.

Jag

Monster Replication

I'm trying to replicate databases from 7 serves into one main one. For example say data from servers A, B, C, D, E, F, G go into one main server, say, server Z. I'm using snapshot replication method. The final outcome of this replication should be as follows: Each server has tables in it, when these tables are replicated into Server Z, I want to add extra field in the table (on Server Z). This field will represent where the record came from, ie, either Server A,B.. etc. I cant add the extra column in the Publisher server tables (Server A,B..etc). The column MUST be added in the final replicated table on server Z.

Has ne one out there done sumthing similar to this? If so please help me out with. I've been struggling with this for a while now. So any kind of help will be appreciated.

Thanks in advance.can you use a separate non-replicated table on server z to perform the final insert from 7 tables using a union and adding this addition field at the time of select for each section of the union?|||You could use merge replication with horizontal filters. Add field hostname and use function host_name() for filtering on publisher.
Your main db will be publisher - another dbs - pull subscribers. Use guid (uniqueidentifier ) as id for all tables. This schema is working fine in one of my projects.|||but my understanding was that it's a reverse, - there are 7 publishers and 1 subscriber. plus, i don't think he is considering total system overhaul (changing structures, guids, etc.)|||Originally posted by snail
You could use merge replication with horizontal filters. Add field hostname and use function host_name() for filtering on publisher.
Your main db will be publisher - another dbs - pull subscribers. Use guid (uniqueidentifier ) as id for all tables. This schema is working fine in one of my projects.

So this idea has already been tested?... Newayz, the guid (uniqueidentifier) column would be default as newid() rite? Well I want a certain value to go in the column depending on the server the record came from.

Since you have done this kinda thing b4, i'd be askin alot of questions ... =)

Thanks in advance.

Monster Query Assistance

I realize this query is inherently incorrect, but my issue is mainly

syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong.

I want to ensure that a.order_id is not in the settlement table. So I

was thinking something along the lines of "WHEN a.order_id not in

(select order_id from settlement)" which I know will cause a slower

response time, but I'm willing to deal with it. In any case, that

syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!

Keep in mind that the effect of the CASE structure is to return a value, in this case a date value. That value is then place inside these parentheses.

Can you spot the syntax problem simply by replacing the entire CASE (inside the parens) with a date value?

and ( CASE
WHEN a.order_id <> b.order_id THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

|||Erm. I'm sure I don't follow you. used_date will be a.transaction_date if a.order_id is not b.order_id. Correct? Are you saying that my syntax is incorrect inside of the case statement? If it is, I just don't see where.

Also, can I simply use

and ( CASE
WHEN a.order_id not in (select order_id from settlement) THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

?|||

This statement makes no sense: (the substituted date value is immaterial)

and ( '01/01/2007' ) used_date datediff(d,used_date, ".$cutOffDate.") < 30)

|||Hrm, so you're saying that I need some sort of separator. I tried parentheses, but those did not work. To quote your example:

and (( CASE ... END) used_date) datediff(d,used_date, ".$cutOffDate.") < 30)

I also tried adding a comma after "used_date)" and just after used_date and removed the parentheses. Can you simply not nest cases in this way or am I completely destroying the query?|||

You can next CASE structures, BUT each nested level MUST be a syntactically complete CASE structure.

I have not attempted to work out your structure, you have not provided any sample data, etc. (See: http://www.aspfaq.com/5006)

I am attempting to point out that (( CASE ... END) used_date) seems meaningless. There is no evaluation, assignment, or other indication about how these values are to be used. And then somehow there is this magical continuation of the line into the datediff function. I don't get what you are attempting with this line.

I suggest that you separately TEST each CASE structure, determining the return value, and then add that value to the outer CASE structure. ONLY when the entire CASE structure works with those substituted values, then you can carefully replace the substituted value with the CASE structure that derived that value.

|||Awesome. Yeah, all I needed to do was remove the "used_date" from that. I had assumed that I needed to alias out the result of the case statement and use the alias in the query later on. I guess I just have to constantly use the same case statement. Oh well, still works so I'm happy.

Thanks much for your help!