Pls help me with this SQL CASE WHEN (on ASP)

Hi and thanx for reading my post..

Well.. my problem is that i cannot seem to get the CASE syntax correct within my ASP page. I'm not sure where to break the lines and all that.

I have searched the net for quiiite a while before posting this :)

Will be VERY glad if someone can help me to put the puzzle in the right ASP-way :)

-----START-----
strSQL = "SELECT enavn,fnavn,fodselsnr,fangenr,convert(char(10),reg _date,104) " &_

"'status'= CASE when legal = '1' then "beginner" when legal = '2' then "intermediate" when legal = '3' then "professional" when kan_skal = 'kan' then "darlig foto" else 'status ikke angitt' END " &_

"FROM BILDE WHERE accepted LIKE " & "'1'" & " AND marked LIKE " & "'0'" & " AND pkode LIKE " & strDistrikt & " AND kan_skal LIKE " & "'kan'" & " AND arstall LIKE " & strArstall
-----END------

What i want to do is getting the three values from the SQL DB which are in the "legal" field (1, 2 or 3), and based on what it is display "beginner", "intermediate", "professional" or "darlig foto".

Best regards
Miradoryou have single quotes around status -- remove those

i prefer the as alias syntax instead of the alias = syntax

you have doublequotes around some strings ("beginner") which should be singlequotesselect enavn,fnavn,fodselsnr,fangenr
, convert(char(10),reg_date,104)
, case when legal = '1' then 'beginner'
when legal = '2' then 'intermediate'
when legal = '3' then 'professional'
when kan_skal = 'kan' then 'darlig foto'
else 'status ikke angitt'
end as status
from bilde
where ...also, none of your LIKE strings appear to have wildcards in them|||What language are you using here? What server? I suspect you're terminating your string improperly.

edit: r937 beat me to it.|||Programming in ASP, SQL towards MS SQL.
Tnx for the hints ppl...

I'm not very good at SQL... yet.. could anyone correct my code so that it might work ? thinking about where i should use double-qoutes in the start of lines and where i should use " &_

Mirador

Originally posted by Teddy
What language are you using here? What server? I suspect you're terminating your string improperly.

edit: r937 beat me to it.|||Tnx !!...

Hmm.. btw: what do u mean by Wildcards on the "LIKE"'s ?`

Mir..

Originally posted by r937
you have single quotes around status -- remove those

i prefer the as alias syntax instead of the alias = syntax

you have doublequotes around some strings ("beginner") which should be singlequotesselect enavn,fnavn,fodselsnr,fangenr
, convert(char(10),reg_date,104)
, case when legal = '1' then 'beginner'
when legal = '2' then 'intermediate'
when legal = '3' then 'professional'
when kan_skal = 'kan' then 'darlig foto'
else 'status ikke angitt'
end as status
from bilde
where ...also, none of your LIKE strings appear to have wildcards in them|||Wildcards can differ per application, but in SQL Server an example of a wild card in a like statement would be as follows:

Select legal
from bilde
where accepted like '%&%' (in this case the % are the wild cards).

This would find all records of [legal] from the table [bilde] where the amperstand (&) appears somewhere in the field [accepted].

Originally posted by Mirador
Tnx !!...

Hmm.. btw: what do u mean by Wildcards on the "LIKE"'s ?`

Mir..

Please help! I have lots of questions.

In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.

The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).

I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?
I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.

1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?

Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.

2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?

I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts.

You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ..." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return

3. Should my data source class return data objects like

1st approach

[DataObject(True)]
pubic class TopicDataSource{
public static Topic[] GetTopicList() { }
}

or should it return DataSet / DataTable / DataReader like

2nd approach

[DataObject(True)]
public class TopicDataSource{
public static DataTable GetTopicList() {}
}

Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.

4. If I go with approach 1, how should I control which property of my data objects is read-onlyafter it's has been inserted/created?
Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)?
Or do I set it and the page leveland write a code to throw an exception in the rare case the application / user try to change it's value? Or else?

Please help. These questions slow me down for days now.

If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.

I will be extremely grateful to anyone who answer any of my questions.

Thanks a lot.

PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.

Hello my friend,

First thing - do not call yourself stupid for asking questions. The stupid ones are those that never ask and then end up breaking something or building something crap. My answers are as follows: -

1) Use stored procedures for performance and then change the order by clause by using a DataView, like this: -

// instead of this
//myRepeater.DataSource = myDataSet;
//myRepeater.DataBind();

// do this (use a stored procedure to fill a dataset and then filter the dataset)
DataView dv = new DataView(myDataSet.Tables[0]);
dv.Sort = "MyField DESC";

myRepeater.DataSource = dv;
myRepeater.DataBind();

2) If all you need to change is the order by clause, use stored procedures with the above example. Only go back to hardcoded SQL if you need to specify the fields to select at runtime or if there are very complex and/or clauses at runtime and the data has a lot of records.

3) If you find approach 2 to be faster then use it. Extensibility is no substitute for performance. We do all the hard stuff so the application runs fast, as opposed to building a second class project to suit us.

4) You can have a Locked property, or whatever name you prefer, on your object. Then in the set property definition, have the following: -

Set(ByVal value As String)
If Me.Locked = False Then
MyProperty = value
End If
End Set

I hope this helps you.

Kind regards

Scotty

|||Thank you Scotty (or Scott, perhaps). That help answer question 2, 3, 4 quite clear. Although if anyone think differently, or if anyone think something should be added, don't hesitate. Please answer it here.

But I still worry about problem 1.
Does the dataset get everything after calling SQLDataAdapter.Fill? In case I SELECT everything without paging? I mean, won't it use too much memory?

Also, if I implement paging in stored procedure, the sorting won't be correct.
For example if the data is like this

Table: Test
Test1
Row 1: a
Row 2: b
Row 3: c
Row 4: d
Row 5: e

Situation : startRecord = 0 MaxRecords = 3 sortColumns = "Test1 DESC"

If I use paging, I will get "a, b, c", right?
Then if I use dataview sorting, my data will reorder from "a, b, c" to - "c, b, a" which is the final result.
But that's not what we actually want. The final data should be "e, d, c" not "c, b, a", right?

Can somebody help?|||

I just thought of something.

Even if I plan to have 2nd, 3rd ... versions, performance come first, right? Just to be sure.

As I told you above, my program intend to replace an existing one create by another company. But currently version 1's main objective is to replace the old system only. I intend to have new features added after version 1 is finished, to be version 2.