Jump to content

Date / Week issues with AS400 DB2

sbrinley

By sbrinley
in Other RDBMS and SQL dialects

 Share

Recommended Posts

sbrinley Newbie

sbrinley

Posted
Posted

I'm having a bit of trouble working with dates and the AS400.  I'm writing a program with PHP that will allow users to see a customer requirements schedule based on different selections such as price, qty, labor, etc... Part of this program uses the week number (since users will select their date range based on weeks) 

 

My issue is this the current date format in field I'm using is 20100114 or YYYYMMDD which is stored as a numeric value and from this I would like to pull what week of the year it is.

If I simply try using WEEK($DATA)  where the $data is my actual column name, it won't work. The only way I've gotten this to work is by using the strsql command on the AS400 itself and converting the date with the following command:

 

WEEK(DATE(CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(DIGITS(LSDTE),5,2),'/'),SUBSTR(DIGITS(LSDTE),7,2)),'/'),SUBSTR(DIGITS(LSDTE),3,2))))

(the reason for so many concatenations is I can't concat more than 2 items at a time because of whatever reason on the AS400)

 

However when I try to do this with the ODBC connection in PHP it simply laughs at me.

 

This is the SQL I'm using in the PHP script that I'm simply testing with:

 

$SQL = "select LPROD, LQORD, LQSHP, LSDTE, WEEK(DATE(CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(DIGITS(LSDTE),5,2),'/'),SUBSTR(DIGITS(LSDTE),7,2)),'/'),SUBSTR(DIGITS(LSDTE),3,2))))
	FROM BPCS405CDF.ECL
	where LID = 'CL' and LPROD = 'D61120104GAT'";   

 

This query returns all the values except the week number even though it returns the value on the AS400.

 

So the answers I'm looking for are:

 

  • When you use a ODBC connection what is the SQL based on? In this case the AS400 or PHP's ODBC stuff?
  • If it is the AS400 any ideas why this won't work?Or evne a possible alternate solution

 

An alternate solution I can think of is creating a table that simply has the date and the conversation date along with the week and week day. However that is not a very reliable way and I would not like to do that if possible.

Link to comment
Share on other sites
  • 3 weeks later...
sbrinley Newbie

sbrinley

Posted
  • Author
Posted

For those of you that might need this information in the future I found out the following solutions:

 

I was not able to convert the string directly so what I did instead was create a new table that allowed me to quickly look up a BPCS date into a regular date or week number. After doing that I was able to make the script work very easily.

 

Also the answers to my 1 question is:

Question:  When you use a ODBC connection what is the SQL based on? In this case the AS400 or PHP's ODBC stuff?

Answer: Yes it is the ODBC connection however the ODBC connection might have capabilities that are not supported by the connected database it is also limited by the database as well. in this case I could use most functions but not functions that had specific limitations such as the concat function in this case.

 

Lastly here is the completed code for the final project:

 

<?php require_once("includes/functions.inc.php"); ?>
<!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN
http://www.w3.org/TR/html401/loose.dtd>
<html>
<head>
<title>13 Week Report Results</title>
</head>
<body>
<?PHP

$str_week = $_POST['str_week'];
$end_week = $_POST['end_week'];

$num_weeks = ($end_week + 1) - $str_week;

$str_week_inc = $str_week +1;
$str_date = weekDayToTime($str_week, date('Y'));
$end_date = cleanTime(518400 + weekDayToTime($end_week, date('Y')));;
$report_type = $_POST['report_type'];

$bpcs_date = date("Ymd", (weekDayToTime(($end_week + 1), date('Y')))); 

//echo $str_week . " str week<br />";
//echo $end_week . " end week<br />";
//echo $num_weeks . " num weeks<br />";
//echo $str_week_inc . " str week inc<br />";
//echo date("n/j/y", $str_date) . " str date<br />";
//echo date("n/j/y", $end_date) . " end date<br />";
//echo $bpcs_date . " bpcs date<br />";




switch ($report_type) {
case 1:
	$report_desc = 'Qty';
	break;
case 2:
	$report_desc = 'Machine Hours';
	break;
case 3:
	$report_desc = 'Sales Price';
	break;
case 4:
	$report_desc = 'Material Cost';
	break;
case 5:
	$report_desc = 'Labor';
	break;
}

/////////////////////////////////////////////////////////
//Database connection
////////////////////////////////////////////////////////
$server="XXXXXXXX"; #the name of the iSeries
$user="XXXXXXX"; #a valid username that will connect to the DB
$pass="XXXXXXX"; #a password for the username
#Connect to the Database with ODBC - $conn is defined and loaded using the "odbc_connect" PHP #directive.
$conn=odbc_connect("XXXXXX","XXXXXXX","XXXXXXXX"); 

#Check Connection
if ($conn == false) {
echo "Not able to connect to database...<br>";
}

///////////////////////////////////////////////////////////////////////////
// Query selection based on which items is selected from the drop down menu 
///////////////////////////////////////////////////////////////////////////

switch ($report_type) {
case 1:
///////////////////////////////////////
	// Based on qty
///////////////////////////////////////

	//MySQL data selection

		$query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, ';	
		$query .= "sum(case when wk_num < {$str_week_inc} then (LQORD - LQSHP) else 0 end) AS Current, ";	

	//Data selection based on number of weeks 
		while($str_week_inc < $end_week){
			$query .= "sum(case when wk_num = {$str_week_inc} then (LQORD - LQSHP) else 0 end) AS week_{$str_week_inc}, ";
			$str_week_inc++;
		}	

		$query .= "sum(case when wk_num = {$end_week} then (LQORD - LQSHP) else 0 end) AS week_{$end_week} ";

		$query .= "FROM (bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod)             
      					  LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate       
					WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} 
					  and bpcs405cdf.ecl.lprod not like 'P%'
					GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03
					ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD";
		break;

case 2:	
///////////////////////////////////////
	// Based on Run Hrs
///////////////////////////////////////

	//MySQL data selection
		$query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, ';
		$query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * rlab) else 0 end) AS Current, ";

	//Data selection based on number of weeks 
		while($str_week_inc < $end_week){
			$query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * rlab) else 0 end) AS week_{$str_week_inc}, ";
			$str_week_inc++;
		}	

		$query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * rlab) else 0 end) AS week_{$end_week} ";	
		$query .= "FROM ((bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod)             
      					  LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate) 
					  LEFT JOIN sblib.fg_run ON bpcs405cdf.ecl.lprod = sblib.fg_run.rprod     
					WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} 
					  and bpcs405cdf.ecl.lprod not like 'P%'
					GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03
					ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD";			
		break;

case 3:		
///////////////////////////////////////
	// Based on Price 
///////////////////////////////////////

	//MySQL data selection
		$query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, ';
		$query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * LNET) else 0 end) AS Current, ";

	//Data selection based on number of weeks 
		while($str_week_inc < $end_week){
			$query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * LNET) else 0 end) AS week_{$str_week_inc}, ";
			$str_week_inc++;
		}	

		$query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * LNET) else 0 end) AS week_{$end_week} ";	
		$query .= "FROM (bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod)             
      					  LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate       
					WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} 
					  and bpcs405cdf.ecl.lprod not like 'P%'
					GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03
					ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD";
		break;

case 4:
///////////////////////////////////////
	// Based on Material Cost
///////////////////////////////////////

	//MySQL data selection
		$query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, ';
		$query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS Current, ";

	//Data selection based on number of weeks 
		while($str_week_inc < $end_week){
			$query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$str_week_inc}, ";
			$str_week_inc++;
		}	                              

		$query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$end_week} ";	
		$query .= "FROM ((bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod)             
      					  LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate)             
      					  LEFT JOIN bpcs405cdf.cmf ON bpcs405cdf.ecl.lprod = bpcs405cdf.cmf.cfprod       
					WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} 
					  and bpcs405cdf.ecl.lprod not like 'P%' and bpcs405cdf.cmf.cfcset = 2 and bpcs405cdf.cmf.cfcbkt = 1 
					GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03
					ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD";
		break;		

case 5:
///////////////////////////////////////
	// Based on Labor Cost
///////////////////////////////////////

	//MySQL data selection
		$query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, ';
		$query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS Current, ";

	//Data selection based on number of weeks 
		while($str_week_inc < $end_week){
			$query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$str_week_inc}, ";
			$str_week_inc++;
		}	                              

		$query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$end_week} ";	
		$query .= "FROM ((bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod)             
      					  LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate)             
      					  LEFT JOIN bpcs405cdf.cmf ON bpcs405cdf.ecl.lprod = bpcs405cdf.cmf.cfprod       
					WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} 
					  and bpcs405cdf.ecl.lprod not like 'P%' and bpcs405cdf.cmf.cfcset = 2 and bpcs405cdf.cmf.cfcbkt = 3 
					GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03
					ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD";
		break;		
}


///////////////////////////////////////////////////////////////////////////
// End of Query selection based on which items is selected from the drop down menu 
///////////////////////////////////////////////////////////////////////////

// execute the data selection 	 
$result = odbc_exec($conn, $query);
  if (!$result) {
	print("Execution failed:\n");
	print("   State: ".odbc_error($con)."\n");
	print("   Error: ".odbc_errormsg($con)."\n");
  } else {
//	print("Execution was successful.\n");
  }

// Data printed into a table 
echo "<h3>" . $num_weeks . " Weeks Requirements Based on " . $report_desc . "(";
echo date("n/j/y", $str_date) . " - " . date("n/j/y", $end_date) . ")</h3>";
echo "<table border='1' cellspacing='0' bordercolor='#000000'><tr>";
echo "<tr>
		<td><b>Area</b></td>
		<td><b>Item</b></td>
		<td><b>Description</b></td>
		<td><b>Current</b></td>";
// While loop to display table headers for weeks			
$str_week_inc2 = $str_week +1;
while($str_week_inc2 < $end_week +1){
	// old header date
	//$header_date = date("n/j/y", (weekDayToTime($str_week_inc2, date('Y'))));
	//echo "<td><b>Week " . $str_week_inc2 . " (". $header_date . ")</b></td>";

	// new header date
	$header_date = date("M-d", (weekDayToTime($str_week_inc2, date('Y'))));
	echo "<td><b>" .$header_date . "</b></td>";

	$str_week_inc2++;
}
// Close table header
echo "</tr><tr>";
// Pull in Data	
while ($row = odbc_fetch_array($result)) {
	while(list($fieldname, $fieldvalue) = each($row)) {
		if(is_numeric($fieldvalue)){
			if($report_type > 2){
				echo "<td>$" . number_format($fieldvalue, 2, '.', ',') . "</td>";
			}else{
				echo "<td>" . number_format($fieldvalue, 2, '.', ',') . "</td>";
			}
		}else{
			echo "<td>" . $fieldvalue . "</td>";
		}
	}
	echo "</tr><tr>";
}
// Close table 
echo "</tr></table>";


// Close ODBC connection
odbc_close($conn);
?>
</body>
</html>

 

Link to comment
Share on other sites
  • 4 years later...
jdelamarre Newbie

jdelamarre

Posted
Posted

you can create a function:

/* ************************************************** */

SET PATH "QSYS","QSYS2","QGPL" ;
 
CREATE FUNCTION QGPL.TO_DATE (
 PARAM_DATE DECIMAL(8, 0) )
 RETURNS DATE  
 LANGUAGE SQL
 SPECIFIC QGPL.TO_DATE
 DETERMINISTIC
 CONTAINS SQL
 CALLED ON NULL INPUT
 RETURN DATE ( CASE WHEN PARAM_DATE IS NULL OR PARAM_DATE = 0 THEN '9999-01-01' ELSE DATE ( INSERT ( INSERT ( PARAM_DATE , 5 , 0 , '-' ) , 8 , 0 , '-' ) ) END )  ;
/* ************************************************** */

 

and use WEEK() function

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.