4 replies to this topic

[newphpcoder]

Hi

I have select statement to get the Rate and Hours of employee, and I had noticed that if no hours save in database for that employee but he has rate, the rate was not also displayed. i want to displayed rate even he has no hours, and hours will be 00:00

the fields is came from those databases and tables.

HRIS - database name

Tables and fields list

employment AS em
EMP_ID
EMP_NO
STATUS

wage AS w
EMP_ID
RATE

payroll - Database Name

Table Name and Fields
casual_hours As c
EMP_NO
Casual_Hours


Here is my code:

if($STATUS == 'Casual'){

 

  $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.casual_hours c WHERE em.EMP_NO = c.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY c.EMP_NO"; 

  $RsEarnings = $conn2->Execute($sql); 

  

  $Rate      = $RsEarnings->fields['RATE'];

  $Hours      = $RsEarnings->fields['Casual_Hours'];


  $Hours = substr($Hours, 0, 5);

  $Hours = str_replace(':', '.', $Hours);

  

 $Amount = $_POST["Amount"];

 

 $Amount = round(($Hours/8)* $Rate, 2);  

 }

 else{

     $Hours = ('00:00');

     $Amount = (0);

 }

  

  $smarty->assign('Rate', $Rate);

  $smarty->assign('Hours', $Hours);

  $smarty->assign('Amount', $Amount);

Thank you so much...

[wim DC]

Try

nvl(c.Casual_Hours, '00:00')

So it will use '00:00' when Casual_Hours is null.

[newphpcoder]

what is nvl?

Thank you

[wim DC]

Ow just noticed it's a function which only exists in Oracle SQL.

Well, if you use mysql I suppose you can use one of those -> http://forums.mysql....59736#msg-59736

[newphpcoder]

Thank you so much...