Click here to Skip to main content
15,891,184 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
public DataSet GetContractTerm(string prodKey, int propLinkID, OleDbConnection conn)
        {
            string sQuery;
            string Prod_Friendly_Name = string.Empty;
   sQuery = "select distinct VAL_ID, VAL_NAME from PPSR_OWNER.ppsr_q2o_att_data_v  where attribute_name = 'CONTRACT TERM'   ORDER BY VAL_NAME ASC";           
 DataSet dsContractTerm = new DataSet();

            try
            {
                dsContractTerm = CCBusiness.Utility.OracleHelperUtility.ExecuteDataset(conn, sQuery);
            }
            catch (Exception e)
            {
                CCBusiness.Utility.OracleHelperUtility.LogPPSRError(System.Web.HttpContext.Current, e);
                throw e;
            }
            return dsContractTerm;

}

private string GetContractName(DataSet ds, string contractID)
        {
            string contractname = string.Empty;

            for (int contract = 0; contract < ds.Tables[0].Rows.Count; contract++)
            {
                if (contractID == ds.Tables[0].Rows[contract]["VAL_ID"].ToString())
                {
                    contractname = ds.Tables[0].Rows[contract]["VAL_NAME"].ToString();
                    break;
                }

            }
            return contractname;
        }

Gettingresult
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
3
30
31
Expected result
1
2
3
4
5
6
7
8
9
10
11
Posted
Updated 11-Apr-11 0:12am
v3
Comments
Sunasara Imdadhusen 11-Apr-11 6:11am    
Added <pre> tag!!
Mahendra.p25 11-Apr-11 6:13am    
you want OrderBy asc on VAL_ID or VAL_NAME
Pratik Bhesaniya 11-Apr-11 6:15am    
can plz give entire output here i can see only Val_Id field but in select Clause you are giving both VAL_ID, VAL_NAME.

Or Just try this order by VAL_ID, VAL_NAME.That is order by both VAL_ID and VAL_NAME.
Ashishmau 11-Apr-11 6:16am    
Be clear in ur question as mahen25 asks

I think you need to convert VAL_NAME to Number.

eg.
SQL
ORDER BY CAST(VAL_NAME AS NUMBER)


Thanks,
Imdadhusen
 
Share this answer
 
Comments
Manfred Rudolf Bihy 11-Apr-11 6:25am    
OK I just checked: CAST works that way in Oracle too. My 5+
Ali Al Omairi(Abu AlHassan) 14-Apr-11 5:32am    
over my head, Sir;
how great are you !!
avigodse 11-Apr-11 9:37am    
Casting a column named like VAL_NAME is not safe while type-casting, if in the case it contains any text in it, it'll raise an error anytime.
If what you declared as your output is indeed from column VAL_NAME and the type of column VAL_NAME is of a character type (e.g. varchar etc.) then you should not be surprised, since sorting strings has nothing to do with sorting integers.

If what your output shows is column VAR_ID then you should also not be surprised since you aren't ordering by VAL_ID, but rather by VAR_NAME.

Subsummizing the two statements above all I CAN tell you this: Do not be surprised.

For a more detailed analysis we need more information from your side. Especially the code which you posted doesn't show us what fields are being output and is thus not helpful at all.

Best Regards,

-MRB
 
Share this answer
 
v3
try this query

select distinct VAL_ID, VAL_NAME from PPSR_OWNER.ppsr_q2o_att_data_v  where attribute_name = 'CONTRACT TERM'  ORDER BY Convert(Decimal,VAL_NAME) ASC
 
Share this answer
 
Comments
Manfred Rudolf Bihy 11-Apr-11 6:27am    
OP asked for Oracle and yes there is a convert function in P-SQL as well, but not int the T-SQLish way you used it. Read here: http://psoug.org/reference/convert_func.html
Kaiiser 11-Apr-11 7:46am    
My Bad.. Thnx Manny for the correction
Hm, I think the most obvious question is why do you use string data type for a numeric value in the table. At a glance I didn't see that your query has any verification that the content of the field is numeric.

Even though casting (or using TO_NUMBER in the SQL) works (unless character data is present) I think you have two options:
- add a descriptive field to your table to define if the VAL_NAME is numeric/date/string/...
- use NUMBER field for numeric data and if you also have other data types in the rows, use separate 'specialized' columns for those
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900