Sorting Records By Id Prefixed by a character problem

[mohsinhere]

Hello,

I'm having a table with PK ServiceId-Text datatype.
The id has both prefixed values and non prefixed values
For ex: the values be
1
2
10
30
M1
M2


M10

When I sort using order by Value(ServiceId), I get
M1
M10
M2
1
2
10
30

the number sorting is ok..but the values with prefix sorting is not proper.
M2 should have come before M10.

I need the result to be
M1
M2
M10
1
2
10
30

Can any one help me..

Thanks in advance.

Mohsin

[weekend00]

if the prefix in fixed or fixed length?
sample1: only "M"
sample2: any one character,"M","B","C"
sample3: various length, "M", "AB"

which case do you have?

[mohsinhere]

Now it is only one character..but the client keeps on changing the requirements...Coz he started with only M...now he wants any characters...may be his next requirement is various lengths...

[mohsinhere]

Can you help me with Sample 2 and 3?

[weekend00]

sample 3 includes sample2.
we need to use a user function to solve this. put following user function in a modoule:

Public Function formatStr(sstr As String) As String
    Dim i As Long
    i = Len(sstr)
    Do While Mid(sstr, i, 1) <= "9"
        i = i - 1
        If i = 0 Then Exit Do
    Loop
    formatStr = Format(Mid(sstr, i + 1), "0000000000")
    If i = 0 Then
        formatStr = Format(sstr, "0000000000")
    Else
        formatStr = Left(sstr, i) & Format(Mid(sstr, i + 1), "0000000000")
    End If
 
End Function

then in your query:
select ... order by formatStr(ServiceID)

[mohsinhere]

Bingo!!!

It worked well...

Thanks a lot dude...

[mohsinhere]

But dude..will I be able to call the function from C#??
My front end is C#.

It is raising an exception...inside Access query, it is working well...

[weekend00]

create Access query, then in C# use this query instead of the table.

[mohsinhere]

Pal,

I really need your help. I did every thing as u instructed.

Still not working.

I have a query which is saved as query1 which calls the function from the module. It works fine within the access.

Now from C#, I call the query same way I do to call a stored procedure in SQL Server.

From C#, it cannot get the function in the module...

Error is Undefined function formatStr in expression...

Can you help me with this one too??

My query is

SELECT SERVICEPrimary.ServiceId, Service.Complaint, ServicePrimary.DateReceived, ServicePrimary.DateCompleted, Status, SUM(A.MANPOWER_AMOUNT+MATERIAL_AMOUNT) AS TOTAL_AMOUNT, Remarks
FROM ((ServicePrimary INNER JOIN Service ON ServicePrimary.ServiceId = Service.ServiceId) LEFT JOIN (SELECT IIF(ISNULL(SUM(AMOUNT)),0,SUM(AMOUNT)) AS MANPOWER_AMOUNT,COMPLAINT_ID FROM ServiceManPower GROUP BY COMPLAINT_ID) AS A ON Service.Complaint_Id = A.Complaint_Id) LEFT JOIN (SELECT IIF(ISNULL(SUM(AMOUNT)),0,SUM(AMOUNT)) AS MATERIAL_AMOUNT,COMPLAINT_ID FROM ServiceMaterial GROUP BY COMPLAINT_ID) AS B ON Service.Complaint_Id = B.Complaint_Id
WHERE Serviceprimary.serviceid alike IIF(ISNULL(@SERVICE_ID),'',@SERVICE_ID) +'%' AND
DEPARTMENT ALIKE IIF(ISNULL(@DEPARTMENT),'',@DEPARTMENT) + '%' AND
BUILDING ALIKE IIF(ISNULL(@BUILDING),'',@BUILDING) + '%' AND


(IIF(ISNULL(@START_DATE), '', IIF(@START_DATE='','',IIF(ISNULL(@END_DATE), '',IIF(@END_DATE='','', ((SERVICEPRIMARY.DATECOMPLETED>=@START_DATE AND SERVICEPRIMARY.DATECOMPLETED<=@END_DATE) OR (SERVICEPRIMARY.DATERECEIVED>=@START_DATE AND SERVICEPRIMARY.DATERECEIVED<=@END_DATE)))))))
GROUP BY SERVICEPrimary.ServiceId, Service.Complaint, ServicePrimary.DateReceived, ServicePrimary.DateCompleted, Status, Remarks
ORDER BY FORMATSTR(SERVICEPRIMARY.SERVICEID);




The function in the module is the same name as u had told.

My c# code is

OleDbConnection conn = new OleDbConnection();
conn = DbConnection.getConnection();
conn.Open();
DataSet ds = new DataSet();
try
{
string query = string.Empty;
query = "Query1";

string dept = "";
string building = "";

if (cmbDepartment.Text != "")
{
dept = cmbDepartment.SelectedItem.ToString();
}

if (cmbBuilding.Text != "")
{
building = cmbBuilding.SelectedItem.ToString();
}

OleDbCommand cmd = new OleDbCommand(query,conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ServiceId", txtServiceId.Text);
cmd.Parameters.AddWithValue("Department", dept);
cmd.Parameters.AddWithValue("Building", building);
cmd.Parameters.AddWithValue("Start_Date", txtDateReceived.Text);
cmd.Parameters.AddWithValue("End_Date", txtDateCompleted.Text);

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);

SearchResult.ItemsSource = ds.Tables[0].DefaultView;

}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
catch (ArgumentException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}

[weekend00]

Sorry pal, I don't think I can help you farther, actually, I don't know much of C#.

I was thinking when query is run from C#, it would find the user defined function. But it may not work this way base on what you got.

without user defined function, I only have solution for sample 2, which have only one letter or no letter in the front:
......order by iif(serviceID>"A", left(serviceID,1) & format(mid(serviceID,2), "00000000"), format(serviceID, "00000000"))

[mohsinhere]

Hai frnd...

At last i told to the client...limitation for prefix is a single alphabet..

Thanks a lot for your help...delivered it today...

Cheers!