F_DISTINCTVALUES

///////////////////////////////////////////////////////////////////////////////////////////////////////////////

// Function Name : f_distinctvalues
// Argument Name : as_table, Arg Type : String, Pass By : Value
//                           as_table_dddw, Arg Type : String, Pass By : Value
//                           as_column, Arg Type : String, Pass By : Value
//                           as_dispcolumn, Arg Type : String, Pass By : Value
//                           as_values[], Arg Type : String, Pass By : Reference
//                           as_dispvalues[], Arg Type : String, Pass By : Reference
// Return Type :       (None)
///////////////////////////////////////////////////////////////////////////////////////////////////////////////
//    Description:  Get the distinct values from the database
//
//    Arguments:
//    as_table:  a SQL table name
//    as_column:  a database column name in passed SQL table
//    as_values:  string array, passed by reference to hold the returned values
///////////////////////////////////////////////////////////////////////////////////////////////////////////////

Long    ll_rc
String    ls_sqlstatement
String    ls_value
String     ls_dispvalue
String    ls_name

// Build the SQL Select statement
ls_sqlstatement = "SELECT DISTINCT " + as_column + "," +as_dispcolumn+ " FROM " + as_table + &
"," + as_table_dddw + " WHERE " + as_column +" = "+ as_table_dddw  + mid(as_column,pos(as_column,"."))

// Execute the SQL
Prepare sqlsa From :ls_sqlstatement;
Describe sqlsa into sqlda;
Declare c_values_cursor    Dynamic Cursor For sqlsa;
Open Dynamic c_values_cursor Using Descriptor sqlda;
Fetch c_values_cursor Using Descriptor sqlda;
ll_rc = sqlca.SQLCode

// Retrieve the distinct values and add them to the array
Do While sqlca.SQLCode = 0
    Choose Case sqlda.OutParmType[1]
        Case TypeString!
            ls_value = GetDynamicString (sqlda, 1)
        Case TypeDate!
            ls_value = String (GetDynamicDate (sqlda, 1))
        Case TypeTime!
            ls_value = String (GetDynamicTime (sqlda, 1))
        Case TypeDateTime!
            ls_value = String (GetDynamicDateTime (sqlda, 1))
        Case Else
            ls_value = String (GetDynamicNumber (sqlda, 1))
    End Choose
   
    Choose Case sqlda.OutParmType[2]
        Case TypeString!
            ls_dispvalue = GetDynamicString (sqlda, 2)
        Case TypeDate!
            ls_dispvalue = String (GetDynamicDate (sqlda, 2))
        Case TypeTime!
            ls_dispvalue = String (GetDynamicTime (sqlda, 2))
        Case TypeDateTime!
            ls_dispvalue = String (GetDynamicDateTime (sqlda, 2))
        Case Else
            ls_dispvalue = String (GetDynamicNumber (sqlda, 2))
    End Choose   
   

    as_values[UpperBound(as_values)+1] =  ls_value
    as_dispvalues[UpperBound(as_dispvalues)+1] =  ls_dispvalue
   
    Fetch c_values_cursor Using Descriptor sqlda;
    ll_rc = sqlca.SQLCode
Loop

Close c_values_cursor;