Both MySQL and PHP/Java support the Unicode, namely UTF-8. To be able to store into and retrieve these Unicode characters in MySQL database, one would need to do following things:
MySQL needs to be informed through PHP/Java. So, following code needs to be placed just after the connection. If the connection is centralized, this code is better to be in that centralized file:
PHP: mysql_query("SET NAMES 'UTF8'");
Java: stmt.execute("SET NAMES 'utf8'");
For all your PHP files generates HTML scripts, please include following code at top of the file:
header("Content-Type: text/html; charset=UTF-8");
For all your HTML files, following script is needed in the head of HTML:
‹ meta http-equiv="Content-Type" content="text/html; charset=UTF-8" ›
Done. Please note, in first piece of PHP code, it is UTF8 and in second PHP code and last HTML script it is UTF-8. Else, it won’t work properly.
If your codes include any of following piece, simply remove it:
PHP: mysql_query("SET CHARACTER SET 'UTF8'");
Java: stmt.execute("SET CHARACTER SET 'utf8'");
CSS and Link
CSS can be written two ways. The first way has selector and second way does not have selector but class selectors only.
First way:
sty
{ color: #333333;
}
First way covers every situation of class selectors. One for all. However, if a particular situation, say “hover” is needed to be excluded from the uniform of selector, it can be added as an overwrite:
sty
{ color: #333333;
}
sty:hover
{ color: #CCCCCC;
}
Second way:
sty:link
{ color: #333333;
}
sty:visited
{ color: #FFFFFF;
}
sty:hover
{ color: #CCCCCC;
}
sty:active
{ color: #333333;
}
Using second way, one must specify every situation. Else, it would inherited from the rule that is used by the element in which the link exists. For 2nd way, there is an order requirement: LoVe/HAte, else, it won't work.
The CSS can be not only used for link, but text as well. However, to enable class selectors work properly, the anchor must be exist:
‹a href="http://koncordpartners.blogspot.com" class="sty"›
This is the anchor started with letter “a”. If no link needed, use this href: href="#".
‹/a›
It also means applying class at table or cell/td level won'd work.
http://www.projectseven.com/tutorials/css/pseudoclasses/
First way:
sty
{ color: #333333;
}
First way covers every situation of class selectors. One for all. However, if a particular situation, say “hover” is needed to be excluded from the uniform of selector, it can be added as an overwrite:
sty
{ color: #333333;
}
sty:hover
{ color: #CCCCCC;
}
Second way:
sty:link
{ color: #333333;
}
sty:visited
{ color: #FFFFFF;
}
sty:hover
{ color: #CCCCCC;
}
sty:active
{ color: #333333;
}
Using second way, one must specify every situation. Else, it would inherited from the rule that is used by the element in which the link exists. For 2nd way, there is an order requirement: LoVe/HAte, else, it won't work.
The CSS can be not only used for link, but text as well. However, to enable class selectors work properly, the anchor must be exist:
‹a href="http://koncordpartners.blogspot.com" class="sty"›
This is the anchor started with letter “a”. If no link needed, use this href: href="#".
‹/a›
It also means applying class at table or cell/td level won'd work.
http://www.projectseven.com/tutorials/css/pseudoclasses/
Number of Elements of Array
Number of elements of array is very usual, which can be used as control for the loop or other purposes. Function to get it in JavaScript is varArray.length. In PHP it is count($ varArray) or sizeof($ varArray). Since sizeof() is just an alias of count(), it is suggested using count(), since sizeof() has other meaning in other languages.
The problem happens when array is undefined/not exist. In JavaScript .length would generate a fatal error. So, for save play, it is suggested always using follow codes:
var len = 0;
if ('undefined'!=typeof(varArray)) len = varArray.length;
In PHP count() would generate a return value of 0 together with a non-fatal error message, which would cause even more serious problem because of its non-fatal nature. Fortunately, PHP provides an easy non-fatal error message suppress method “@”:
@count($varArray);
It is very useful because it enables you to always associate @ with count(). And please do so.
For multidimensional array, the usage will be exactly same; and can be used for particular dimension, such as varArray[i] or $varArray[i].
The problem happens when array is undefined/not exist. In JavaScript .length would generate a fatal error. So, for save play, it is suggested always using follow codes:
var len = 0;
if ('undefined'!=typeof(varArray)) len = varArray.length;
In PHP count() would generate a return value of 0 together with a non-fatal error message, which would cause even more serious problem because of its non-fatal nature. Fortunately, PHP provides an easy non-fatal error message suppress method “@”:
@count($varArray);
It is very useful because it enables you to always associate @ with count(). And please do so.
For multidimensional array, the usage will be exactly same; and can be used for particular dimension, such as varArray[i] or $varArray[i].
Test Various “Nothings” in PHP
Following php codes is to test six different “nothing”variables together with popular functions, plus number 1 as control.
‹?php
$undefinedVar;
$nullVar = null;
$strEmpty = "";
$strChanged = "initial value";
$numZero = 0;
$numChanged = 1;
$numOne = 1;
$strChanged = null;
$numChanged = null;
echo "‹br/›11. undefinedVar: (".$undefinedVar.")";
echo "‹br/›12. nullVar: (".$nullVar.")";
echo "‹br/›13. strEmpty: (".$strEmpty.")";
echo "‹br/›14. strChanged: (".$strChanged.")";
echo "‹br/›15. numZero: (".$numZero.")";
echo "‹br/›16. numChanged: (".$numChanged.")";
echo "‹br/›21. is_null-undefinedVar: (".is_null($undefinedVar).")";
echo "‹br/›22. is_null-nullVar: (".is_null($nullVar).")";
echo "‹br/›23. is_null-strEmpty: (".is_null($strEmpty).")";
echo "‹br/›24. is_null-strChanged: (".is_null($strChanged).")";
echo "‹br/›25. is_null-numZero: (".is_null($numZero).")";
echo "‹br/›26. is_null-numChanged: (".is_null($numChanged).")";
echo "‹br/›31. isset-undefinedVar: (".isset($undefinedVar).")";
echo "‹br/›32. isset-nullVar: (".isset($nullVar).")";
echo "‹br/›33. isset-strEmpty: (".isset($strEmpty).")";
echo "‹br/›34. isset-strChanged: (".isset($strChanged).")";
echo "‹br/›35. isset-numZero: (".isset($numZero).")";
echo "‹br/›36. isset-numChanged: (".isset($numChanged).")";
echo "‹br/›47. TRUE: (".TRUE.")";
echo "‹br/›48. FALSE: (".FALSE.")";
echo "‹br/›51. empty-undefinedVar: (".empty($undefinedVar).")";
echo "‹br/›52. empty-nullVar: (".empty($nullVar).")";
echo "‹br/›53. empty-strEmpty: (".empty($strEmpty).")";
echo "‹br/›54. empty-strChanged: (".empty($strChanged).")";
echo "‹br/›55. empty-numZero: (".empty($numZero).")";
echo "‹br/›56. empty-numChanged: (".empty($numChanged).")";
echo "‹br/›57. empty-numOne: (".empty($numOne).")";
echo "‹br/›61. defined-undefinedVar: (".defined($undefinedVar).")";
echo "‹br/›62. defined-nullVar: (".defined($nullVar).")";
echo "‹br/›63. defined-strEmpty: (".defined($strEmpty).")";
echo "‹br/›64. defined-strChanged: (".defined($strChanged).")";
echo "‹br/›65. defined-numZero: (".defined($numZero).")";
echo "‹br/›66. defined-numChanged: (".defined($numChanged).")";
echo "‹br/›71. strlen-trim-undefinedVar: (".strlen(trim($undefinedVar)).")";
echo "‹br/›72. strlen-trim-nullVar: (".strlen(trim($nullVar)).")";
echo "‹br/›73. strlen-trim-strEmpty: (".strlen(trim($strEmpty)).")";
echo "‹br/›74. strlen-trim-strChanged: (".strlen(trim($strChanged)).")";
echo "‹br/›75. strlen-trim-numZero: (".strlen(trim($numZero)).")";
echo "‹br/›76. strlen-trim-numChanged: (".strlen(trim($numChanged)).")";
?›
unset() does not work for any one of them. Therefore, it had been excluded.
Test results in both Firefox and IE are identical, as follows:
11. undefinedVar: ()
12. nullVar: ()
13. strEmpty: ()
14. strChanged: ()
15. numZero: (0)
16. numChanged: ()
21. is_null-undefinedVar: (1)
22. is_null-nullVar: (1)
23. is_null-strEmpty: ()
24. is_null-strChanged: (1)
25. is_null-numZero: ()
26. is_null-numChanged: (1)
31. isset-undefinedVar: ()
32. isset-nullVar: ()
33. isset-strEmpty: (1)
34. isset-strChanged: ()
35. isset-numZero: (1)
36. isset-numChanged: ()
47. TRUE: (1)
48. FALSE: ()
51. empty-undefinedVar: (1)
52. empty-nullVar: (1)
53. empty-strEmpty: (1)
54. empty-strChanged: (1)
55. empty-numZero: (1)
56. empty-numChanged: (1)
57. empty-numOne: ()
61. defined-undefinedVar: ()
62. defined-nullVar: ()
63. defined-strEmpty: ()
64. defined-strChanged: ()
65. defined-numZero: ()
66. defined-numChanged: ()
71. strlen-trim-undefinedVar: (0)
72. strlen-trim-nullVar: (0)
73. strlen-trim-strEmpty: (0)
74. strlen-trim-strChanged: (0)
75. strlen-trim-numZero: (1)
76. strlen-trim-numChanged: (0)
Observed, included but not limited to this test:
1. Results in Firefox and IE are exactly same. However, if an null variable passed from JavaScript to PHP, in IE, it would be shown as a string "null". In a real case scenario, a variable is to pass from form feeding in HTML/JavaScript to PHP. However, it had only be assigned with value “null”. It supposes an integer data type. Unfortunately, in is_null() in PHP, it results not null, while echo shows “null” in IE and nothing in Firefox.
2. unset() does not work at all for all of them.
3. is_null() works fine. It however regards empty string as not null, which should be.
4. isset() works fine. It regards empty string as set.
5. is_null() is just opposite to isset().
6. empty() works for all, including regarding number 0 as empty.
7. defined() is to detect if a constant string exists, which does works fine here, since no constant string here.
8. strlen(trim()) does work fine except number 0, which should be.
Conclusion:
First of all, never use null in JavaScript where it supposed to be a string. Use '' instead. This is because in IE, when JavaScript passes string to PHP, null would become 'null'.
1. If one want to include everything, null, empty string, number 0, and even string “0”, the best approach is to use if(empty($var)).
2. If one wants to include null and empty string and exclude number 0, if(0==strlen(trim($var)) might be best approach. However, this approach is unable to detect the string "null".
3. If one wants to include null and number 0 and exclude empty string, the best approach maybe if(is_null($var) || 0==$var).
4. If one wants to only include null and exclude empty string and number 0, the best approach is if(is_null($var)). However, since both JavaScript and PHP does not tell the data type when declare a variable, it somehow easily to be mixed up the undefined variable or null variable with empty string. It is therefore suggested extra caution shall be applied to exclude empty string.
5. In most case people dealing with null would include undefined variable, null variable, empty string in JavaScript and PHP. For special case mentioned above for IE, it would includes string "null" as well, which isn't covered by Conclustion 2. So, the possible most save approach is put two together:
if(0==strlen(trim($var)) || "null"==$var)
6. To deal with undefined array, please refer to http://koncordpartners.blogspot.com/2009/12/number-of-elements-of-array.html.
http://ca2.php.net/manual/en/function.empty.php
‹?php
$undefinedVar;
$nullVar = null;
$strEmpty = "";
$strChanged = "initial value";
$numZero = 0;
$numChanged = 1;
$numOne = 1;
$strChanged = null;
$numChanged = null;
echo "‹br/›11. undefinedVar: (".$undefinedVar.")";
echo "‹br/›12. nullVar: (".$nullVar.")";
echo "‹br/›13. strEmpty: (".$strEmpty.")";
echo "‹br/›14. strChanged: (".$strChanged.")";
echo "‹br/›15. numZero: (".$numZero.")";
echo "‹br/›16. numChanged: (".$numChanged.")";
echo "‹br/›21. is_null-undefinedVar: (".is_null($undefinedVar).")";
echo "‹br/›22. is_null-nullVar: (".is_null($nullVar).")";
echo "‹br/›23. is_null-strEmpty: (".is_null($strEmpty).")";
echo "‹br/›24. is_null-strChanged: (".is_null($strChanged).")";
echo "‹br/›25. is_null-numZero: (".is_null($numZero).")";
echo "‹br/›26. is_null-numChanged: (".is_null($numChanged).")";
echo "‹br/›31. isset-undefinedVar: (".isset($undefinedVar).")";
echo "‹br/›32. isset-nullVar: (".isset($nullVar).")";
echo "‹br/›33. isset-strEmpty: (".isset($strEmpty).")";
echo "‹br/›34. isset-strChanged: (".isset($strChanged).")";
echo "‹br/›35. isset-numZero: (".isset($numZero).")";
echo "‹br/›36. isset-numChanged: (".isset($numChanged).")";
echo "‹br/›47. TRUE: (".TRUE.")";
echo "‹br/›48. FALSE: (".FALSE.")";
echo "‹br/›51. empty-undefinedVar: (".empty($undefinedVar).")";
echo "‹br/›52. empty-nullVar: (".empty($nullVar).")";
echo "‹br/›53. empty-strEmpty: (".empty($strEmpty).")";
echo "‹br/›54. empty-strChanged: (".empty($strChanged).")";
echo "‹br/›55. empty-numZero: (".empty($numZero).")";
echo "‹br/›56. empty-numChanged: (".empty($numChanged).")";
echo "‹br/›57. empty-numOne: (".empty($numOne).")";
echo "‹br/›61. defined-undefinedVar: (".defined($undefinedVar).")";
echo "‹br/›62. defined-nullVar: (".defined($nullVar).")";
echo "‹br/›63. defined-strEmpty: (".defined($strEmpty).")";
echo "‹br/›64. defined-strChanged: (".defined($strChanged).")";
echo "‹br/›65. defined-numZero: (".defined($numZero).")";
echo "‹br/›66. defined-numChanged: (".defined($numChanged).")";
echo "‹br/›71. strlen-trim-undefinedVar: (".strlen(trim($undefinedVar)).")";
echo "‹br/›72. strlen-trim-nullVar: (".strlen(trim($nullVar)).")";
echo "‹br/›73. strlen-trim-strEmpty: (".strlen(trim($strEmpty)).")";
echo "‹br/›74. strlen-trim-strChanged: (".strlen(trim($strChanged)).")";
echo "‹br/›75. strlen-trim-numZero: (".strlen(trim($numZero)).")";
echo "‹br/›76. strlen-trim-numChanged: (".strlen(trim($numChanged)).")";
?›
unset() does not work for any one of them. Therefore, it had been excluded.
Test results in both Firefox and IE are identical, as follows:
11. undefinedVar: ()
12. nullVar: ()
13. strEmpty: ()
14. strChanged: ()
15. numZero: (0)
16. numChanged: ()
21. is_null-undefinedVar: (1)
22. is_null-nullVar: (1)
23. is_null-strEmpty: ()
24. is_null-strChanged: (1)
25. is_null-numZero: ()
26. is_null-numChanged: (1)
31. isset-undefinedVar: ()
32. isset-nullVar: ()
33. isset-strEmpty: (1)
34. isset-strChanged: ()
35. isset-numZero: (1)
36. isset-numChanged: ()
47. TRUE: (1)
48. FALSE: ()
51. empty-undefinedVar: (1)
52. empty-nullVar: (1)
53. empty-strEmpty: (1)
54. empty-strChanged: (1)
55. empty-numZero: (1)
56. empty-numChanged: (1)
57. empty-numOne: ()
61. defined-undefinedVar: ()
62. defined-nullVar: ()
63. defined-strEmpty: ()
64. defined-strChanged: ()
65. defined-numZero: ()
66. defined-numChanged: ()
71. strlen-trim-undefinedVar: (0)
72. strlen-trim-nullVar: (0)
73. strlen-trim-strEmpty: (0)
74. strlen-trim-strChanged: (0)
75. strlen-trim-numZero: (1)
76. strlen-trim-numChanged: (0)
Observed, included but not limited to this test:
1. Results in Firefox and IE are exactly same. However, if an null variable passed from JavaScript to PHP, in IE, it would be shown as a string "null". In a real case scenario, a variable is to pass from form feeding in HTML/JavaScript to PHP. However, it had only be assigned with value “null”. It supposes an integer data type. Unfortunately, in is_null() in PHP, it results not null, while echo shows “null” in IE and nothing in Firefox.
2. unset() does not work at all for all of them.
3. is_null() works fine. It however regards empty string as not null, which should be.
4. isset() works fine. It regards empty string as set.
5. is_null() is just opposite to isset().
6. empty() works for all, including regarding number 0 as empty.
7. defined() is to detect if a constant string exists, which does works fine here, since no constant string here.
8. strlen(trim()) does work fine except number 0, which should be.
Conclusion:
First of all, never use null in JavaScript where it supposed to be a string. Use '' instead. This is because in IE, when JavaScript passes string to PHP, null would become 'null'.
1. If one want to include everything, null, empty string, number 0, and even string “0”, the best approach is to use if(empty($var)).
2. If one wants to include null and empty string and exclude number 0, if(0==strlen(trim($var)) might be best approach. However, this approach is unable to detect the string "null".
3. If one wants to include null and number 0 and exclude empty string, the best approach maybe if(is_null($var) || 0==$var).
4. If one wants to only include null and exclude empty string and number 0, the best approach is if(is_null($var)). However, since both JavaScript and PHP does not tell the data type when declare a variable, it somehow easily to be mixed up the undefined variable or null variable with empty string. It is therefore suggested extra caution shall be applied to exclude empty string.
5. In most case people dealing with null would include undefined variable, null variable, empty string in JavaScript and PHP. For special case mentioned above for IE, it would includes string "null" as well, which isn't covered by Conclustion 2. So, the possible most save approach is put two together:
if(0==strlen(trim($var)) || "null"==$var)
6. To deal with undefined array, please refer to http://koncordpartners.blogspot.com/2009/12/number-of-elements-of-array.html.
http://ca2.php.net/manual/en/function.empty.php
Are “Title” and "Top" Keywords in JavaScript?
No, actually they is not. However, please do not use them as name or id in HTML. Following problem has been notices:
‹input id=”title” type=”text” value”Title is forbidden” ›
When try to get value by document.getElementById('title').value, it generate null in IE. If one exchanges the “title” with other word, it works fine.
In Chrome, one case use top() as function name, it does not work while it works fine with IE and Firefox.
Very special!
http://aptana.com/reference/html/api/JSKeywords.index.html
‹input id=”title” type=”text” value”Title is forbidden” ›
When try to get value by document.getElementById('title').value, it generate null in IE. If one exchanges the “title” with other word, it works fine.
In Chrome, one case use top() as function name, it does not work while it works fine with IE and Firefox.
Very special!
http://aptana.com/reference/html/api/JSKeywords.index.html
Days in the Month in T-SQL
Number of days in the month are same as the last day’s date number in the month. Unlike PL/SQL, in T-SQL there is no function called last_day(). Following are two methods to get it:
Method 1, if you know the date:
DECLARE @Date datetime
SET @Date = '2009/12/10'
SELECT DAY(DATEADD(d,-1,dateadd(m,1,@Date))) AS 'Last day of the month'
Method 2, if you know the year and month number:
SELECT DAY(DATEADD(D, -1, DATEADD(M, 1, CONVERT(DATETIME, (CONVERT(VARCHAR, Yer) + '-' + CONVERT(VARCHAR, Mth) + '-01'))))) AS 'Last day of the month'
http://www.bigresource.com/MS_SQL-last_day-function-s5q435mI.html
Method 1, if you know the date:
DECLARE @Date datetime
SET @Date = '2009/12/10'
SELECT DAY(DATEADD(d,-1,dateadd(m,1,@Date))) AS 'Last day of the month'
Method 2, if you know the year and month number:
SELECT DAY(DATEADD(D, -1, DATEADD(M, 1, CONVERT(DATETIME, (CONVERT(VARCHAR, Yer) + '-' + CONVERT(VARCHAR, Mth) + '-01'))))) AS 'Last day of the month'
http://www.bigresource.com/MS_SQL-last_day-function-s5q435mI.html
Contain Data Layout in SQL Part for Reporting Services
Business Intelligence in both ORACLE and SQL Server has offered standard tools and settings, such as cube and dimensions. However, for complicated reports, these tools may not be very useful. The orthodox approach to this issue is to have a customized OLAP database built. In some case, even with OLAP database, complicated queries still needed. This issue does also cause the further problem of data layout in report. Here is an approach to deal with these issues. Basically, this approach does contain the task of data layout in SQL part to avoid complex programming in Stored Procedures.
Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.
The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……
The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.
Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q
Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.
Next step is to use GROUP BY:
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:
WITH Distinct_Q
AS
(…
)
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
Note, DATEADD(HH, -1, Entry_Date) is used again here.
We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q
The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].
The final step to to add ORDER BY at the end:
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.
Here is completed SQL script:
WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.
http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html
Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.
The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……
The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.
Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q
Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.
Next step is to use GROUP BY:
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:
WITH Distinct_Q
AS
(…
)
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
Note, DATEADD(HH, -1, Entry_Date) is used again here.
We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q
The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].
The final step to to add ORDER BY at the end:
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.
Here is completed SQL script:
WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.
http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html
Structure in Web Development (1)
This is study notes of web development involving the relationship and value passing between technologies.
HTML, Hyper Text Markup Language, as its name suggested is a markup language, which does formatting for the document; such as font, border format for cells in table, etc. There are two parts in HTML, head section and body section. Head contains all metadata, controls and the body is the document to be presented in web browser. When URL called by browser, whole HTML file is downloaded in the browser’ computer, which means it is a client side process.
To be easily and programmatically changed or updated, all formats can be centrally managed by a separate file, with extension of .css. This is the file of Cascading Style Sheets, or CSS. The inner link between two files is done through the following declaration to be included in the head of HTML:
‹link rel="stylesheet" type="text/css" href="cssFilename.css"›
In addition, in each division of HTML, “class=”classInCSS”” is needed to point the format class in CSS file. There is no value passing between two files.
Both HTML and CSS do not have programming functionality. To add the programming functionality, JavaScript can be added in HTML. Since programming functions are not of the presentation, it therefore locates in the head of HTML. However, when JavaScript consists too much contents, it can be separated as a file with extension of .js. The inner link between two files is done through the following declaration to be included in the head of HTML:
‹script type='text/javascript' language='Javascript' src='javascriptFileName.js' ›‹/script›
When JavaScript file is called by the HTML in browser’s computer, the whole file is downloaded too. So, JavaScript is also the client side process. Since contents of HTML may need to be changed frequently, separation of HTML and JavaScript may help to improve the refresh speed because programming functions may not change so frequently. This is another reason why JavaScript needs to be an independent file. However, this reason may not sustainable in DHTML scenario, to be mentioned late. At this stage, we deal with the HTML with static contents.
Functions in JavaScript need to be invoked. It can be done through window events, such as mouse move, mouse over, mouse out, mouse click or HTML page on load, etc; or through the calling from another funcation. Since HTML content is static, when value in HTML need to be passed to JavaScript, it is no need to be dealt specifically. Programmer can just use that value when write JavaScript file. In other words, there is no value passed from HTML to JavaScript, apart from invoking action. However, if value passing from JavaScript to HTML is needed, it can be done through “documnt.write()” and “.innerHTML =” methods. “documnt.write()” is located within the body of HTML. Since HTML body is for presentation purpose, to use “documnt.write()” one much first declare it is of JavaScript scripts:
‹script type="text/javascript"›
document.write(' javascriptVariable ');
‹/script›
The major difference between “documnt.write()” and “.innerHTML =” is “documnt.write()” does not need trigger to be active, simply because it is located in the body of HTML. When HTML page loaded, it is invoked. On contrast, “.innerHTML =” is located in JavaScript section/file, it needs to be called either by window events or other functions. Generally, “.innerHTML =” method is recommended. For instance:
document.getElementById('aDivision').innerHTML = javascriptVariable;
By this way, the value javascriptVariable generated from JavaScript functions is delivered to a HTML division called “aDivision”. Since HTML is only the markup language, there will be no variable involved in pure HTML. Variables need to be declared in JavaScript. Indeed, by this way, HTML can have variables. In addition, by this way, the delivered can be not only a value of single variable, but also more complicated contents:
var dynamicTable = ‘‹table›‹tr›‹td›’ + javascriptVariable + ‘‹/td›‹/tr›‹/table›’;
document.getElementById('aDivision').innerHTML = dynamicTable;
Please note, variable dynamicTable now contains some HTML codes, as string. The actual delivered now is no longer the single value but a complete table. This is very much same concept of Dynamic SQL if one is familiar with the database. This indeed is the basic concept of Dynamic HTML, or DHTML, for which the contents together with associated HTML codes are dynamically generated by the running of JavaScript functions.
Each HTML together with its associated CSS and JavaScript files are an independent program. If there are more than one HTML pages in a website, how they are structured? Since HTML and CSS files do not really involve the value passing, Multiple HTML pages can share a single CSS file. One HTML can have more than one JavaScript files by declare both of them in the head:
‹script type='text/javascript' language='Javascript' src='javascriptFile_1.js' ›‹/script›
‹script type='text/javascript' language='Javascript' src='javascriptFile_2.js' ›‹/script›
When a function is called, the browser will first search if “this” file have this function, if not, it would automatically goes to other JavaScript files declared in same HTML file.
It is not recommended to share JavaScript file between HTML pages. Because invoking any function is through window events, such as on page load etc. Sharing JavaScript file just confuses the computer and yourself, on which page load, for instance. Window events such as “onload” is very much HTML page specified and is not generic. However, for those generic functions with absolutely no window events involved, it can be aggregated into a JavaScript file to be shared by multiple HTML files.
Since each HTML page is an independent program, access to other’s functions or passing value between HTML pages is not recommended, for the sake of programming convention as well as security concern. In special cases like one HTML page (child) was opened by another HTML page (parent), it can be done with limitation. For instance:
In parent JavaScript file:
var windowChild = window.open(url); // “url” is the string variable.
windowChild.close();
In this example, parent JavaScript opens a new HTML window and close it immediately.
In child JavaScript file:
var valueFromParent = window.opener.valuePassToChild;
Child HTML can thus access the value of a variable in parent HTML. In case the child window is opened within the iFrame of parent, it is :
var valueFromParent = parent.valuePassToChild;
Please take special note, this syntax is also true for object reference passing through HTML pages. We know that the reference acts as pointer to point to original variable rather than make a copy, any using of the reference, valueFromParent here, is indeed referencing back to parent HTML ‘s object. It does generate the issue of speed. For details, see http://koncordpartners.blogspot.com/. So, please do apply this to the object other than simple variable, such as array.
Following is the method when parent window calls functions located in iFrame child:
document.getElementById('iFrameID').contentWindow
.functionInIFrame(parameter);
If one include this into another function, that function indeed can be called by same or other iFrame windows:
In parent JavaScript file:
function crossWindow(parameter)
{ document.getElementById('iFrameName').contentWindow
.functionInIFrame(parameter);
};
In iFrame file:
crossWindow(parameter);
Unfortunate, the string iFrameName should not be variable, otherwise dynamic function will be involved. Furthermore, dynamic reference passing does never work properly. It therefore dynamic reference and dynamic function should be avoid. In this example, one can use “if” statement to pick up right inner function:
function crossWindow(option, parameter)
{ if (1==option) document.getElementById('iFrameName_1').contentWindow
.functionInIFrame(parameter)
else document.getElementById('iFrameName_2').contentWindow
.functionInIFrame(parameter);
};
When “parameter” is reference to variable located parent window, things become little complex. JavaScript indeed regards it is about the variable in iFrame window. One needs to declare parameter locally first:
var parameter = parent.paraInParent;
What about in iFrame child window, calling corssWindow() function is through dynamic insertion? Something like:
window.onload = function()
{ document.getElementById('dynamicContent').innerHTML = '‹a href="#" onclick="parent.crossWindow(‘ + parameter + ‘)"›Click here to run function in parent window‹/a›';
};
No, it would never work properly if parameter is the reference referring back to another variable in parent window.
(To be continued)
HTML, Hyper Text Markup Language, as its name suggested is a markup language, which does formatting for the document; such as font, border format for cells in table, etc. There are two parts in HTML, head section and body section. Head contains all metadata, controls and the body is the document to be presented in web browser. When URL called by browser, whole HTML file is downloaded in the browser’ computer, which means it is a client side process.
To be easily and programmatically changed or updated, all formats can be centrally managed by a separate file, with extension of .css. This is the file of Cascading Style Sheets, or CSS. The inner link between two files is done through the following declaration to be included in the head of HTML:
‹link rel="stylesheet" type="text/css" href="cssFilename.css"›
In addition, in each division of HTML, “class=”classInCSS”” is needed to point the format class in CSS file. There is no value passing between two files.
Both HTML and CSS do not have programming functionality. To add the programming functionality, JavaScript can be added in HTML. Since programming functions are not of the presentation, it therefore locates in the head of HTML. However, when JavaScript consists too much contents, it can be separated as a file with extension of .js. The inner link between two files is done through the following declaration to be included in the head of HTML:
‹script type='text/javascript' language='Javascript' src='javascriptFileName.js' ›‹/script›
When JavaScript file is called by the HTML in browser’s computer, the whole file is downloaded too. So, JavaScript is also the client side process. Since contents of HTML may need to be changed frequently, separation of HTML and JavaScript may help to improve the refresh speed because programming functions may not change so frequently. This is another reason why JavaScript needs to be an independent file. However, this reason may not sustainable in DHTML scenario, to be mentioned late. At this stage, we deal with the HTML with static contents.
Functions in JavaScript need to be invoked. It can be done through window events, such as mouse move, mouse over, mouse out, mouse click or HTML page on load, etc; or through the calling from another funcation. Since HTML content is static, when value in HTML need to be passed to JavaScript, it is no need to be dealt specifically. Programmer can just use that value when write JavaScript file. In other words, there is no value passed from HTML to JavaScript, apart from invoking action. However, if value passing from JavaScript to HTML is needed, it can be done through “documnt.write()” and “.innerHTML =” methods. “documnt.write()” is located within the body of HTML. Since HTML body is for presentation purpose, to use “documnt.write()” one much first declare it is of JavaScript scripts:
‹script type="text/javascript"›
document.write(' javascriptVariable ');
‹/script›
The major difference between “documnt.write()” and “.innerHTML =” is “documnt.write()” does not need trigger to be active, simply because it is located in the body of HTML. When HTML page loaded, it is invoked. On contrast, “.innerHTML =” is located in JavaScript section/file, it needs to be called either by window events or other functions. Generally, “.innerHTML =” method is recommended. For instance:
document.getElementById('aDivision').innerHTML = javascriptVariable;
By this way, the value javascriptVariable generated from JavaScript functions is delivered to a HTML division called “aDivision”. Since HTML is only the markup language, there will be no variable involved in pure HTML. Variables need to be declared in JavaScript. Indeed, by this way, HTML can have variables. In addition, by this way, the delivered can be not only a value of single variable, but also more complicated contents:
var dynamicTable = ‘‹table›‹tr›‹td›’ + javascriptVariable + ‘‹/td›‹/tr›‹/table›’;
document.getElementById('aDivision').innerHTML = dynamicTable;
Please note, variable dynamicTable now contains some HTML codes, as string. The actual delivered now is no longer the single value but a complete table. This is very much same concept of Dynamic SQL if one is familiar with the database. This indeed is the basic concept of Dynamic HTML, or DHTML, for which the contents together with associated HTML codes are dynamically generated by the running of JavaScript functions.
Each HTML together with its associated CSS and JavaScript files are an independent program. If there are more than one HTML pages in a website, how they are structured? Since HTML and CSS files do not really involve the value passing, Multiple HTML pages can share a single CSS file. One HTML can have more than one JavaScript files by declare both of them in the head:
‹script type='text/javascript' language='Javascript' src='javascriptFile_1.js' ›‹/script›
‹script type='text/javascript' language='Javascript' src='javascriptFile_2.js' ›‹/script›
When a function is called, the browser will first search if “this” file have this function, if not, it would automatically goes to other JavaScript files declared in same HTML file.
It is not recommended to share JavaScript file between HTML pages. Because invoking any function is through window events, such as on page load etc. Sharing JavaScript file just confuses the computer and yourself, on which page load, for instance. Window events such as “onload” is very much HTML page specified and is not generic. However, for those generic functions with absolutely no window events involved, it can be aggregated into a JavaScript file to be shared by multiple HTML files.
Since each HTML page is an independent program, access to other’s functions or passing value between HTML pages is not recommended, for the sake of programming convention as well as security concern. In special cases like one HTML page (child) was opened by another HTML page (parent), it can be done with limitation. For instance:
In parent JavaScript file:
var windowChild = window.open(url); // “url” is the string variable.
windowChild.close();
In this example, parent JavaScript opens a new HTML window and close it immediately.
In child JavaScript file:
var valueFromParent = window.opener.valuePassToChild;
Child HTML can thus access the value of a variable in parent HTML. In case the child window is opened within the iFrame of parent, it is :
var valueFromParent = parent.valuePassToChild;
Please take special note, this syntax is also true for object reference passing through HTML pages. We know that the reference acts as pointer to point to original variable rather than make a copy, any using of the reference, valueFromParent here, is indeed referencing back to parent HTML ‘s object. It does generate the issue of speed. For details, see http://koncordpartners.blogspot.com/. So, please do apply this to the object other than simple variable, such as array.
Following is the method when parent window calls functions located in iFrame child:
document.getElementById('iFrameID').contentWindow
.functionInIFrame(parameter);
If one include this into another function, that function indeed can be called by same or other iFrame windows:
In parent JavaScript file:
function crossWindow(parameter)
{ document.getElementById('iFrameName').contentWindow
.functionInIFrame(parameter);
};
In iFrame file:
crossWindow(parameter);
Unfortunate, the string iFrameName should not be variable, otherwise dynamic function will be involved. Furthermore, dynamic reference passing does never work properly. It therefore dynamic reference and dynamic function should be avoid. In this example, one can use “if” statement to pick up right inner function:
function crossWindow(option, parameter)
{ if (1==option) document.getElementById('iFrameName_1').contentWindow
.functionInIFrame(parameter)
else document.getElementById('iFrameName_2').contentWindow
.functionInIFrame(parameter);
};
When “parameter” is reference to variable located parent window, things become little complex. JavaScript indeed regards it is about the variable in iFrame window. One needs to declare parameter locally first:
var parameter = parent.paraInParent;
What about in iFrame child window, calling corssWindow() function is through dynamic insertion? Something like:
window.onload = function()
{ document.getElementById('dynamicContent').innerHTML = '‹a href="#" onclick="parent.crossWindow(‘ + parameter + ‘)"›Click here to run function in parent window‹/a›';
};
No, it would never work properly if parameter is the reference referring back to another variable in parent window.
(To be continued)
Issues of Array in JavaScript
It is noticed the initialization of a variable when declared as an element of array in not allowed. For instance:
var arr = [1, 2, 3];
var num = arr[0];
Assigning value to variable num "num = arr[0]; " needs to be done separately.
Since JavaScript arrays are assigned by reference, when array to be passed across iFrame, an issue of speed was met, especially for Mozilla Firefox.
Scripts in iFrame page:
var newArray = parent.arr;
When calling newArray[0] immediate after above declaration, it often generates error of "newArray is not defined". HTML pages are actual independent programs (stateless), it is no wonder reference across programs facing such kind of problem. It is therefore suggested avoiding passing array across parent page with iFrame page. Alternatively, slice array into single dimensional variable will do.
The reason behind this issue is this syntax of passing value is also true for object reference passing through HTML pages. We know that the reference acts as pointer to point to original variable rather than make a copy, any using of the reference is indeed reference back to parent HTML's object. It does cause a complex process behind the scent.
For same reason, if one wants to turn array in JavaScript, the best approach is to turn it into a string first, then pass the string to php, then use explode() to turn it back to array in php.
http://www.webdeveloper.com/forum/archive/index.php/t-93920.html
var arr = [1, 2, 3];
var num = arr[0];
Assigning value to variable num "num = arr[0]; " needs to be done separately.
Since JavaScript arrays are assigned by reference, when array to be passed across iFrame, an issue of speed was met, especially for Mozilla Firefox.
Scripts in iFrame page:
var newArray = parent.arr;
When calling newArray[0] immediate after above declaration, it often generates error of "newArray is not defined". HTML pages are actual independent programs (stateless), it is no wonder reference across programs facing such kind of problem. It is therefore suggested avoiding passing array across parent page with iFrame page. Alternatively, slice array into single dimensional variable will do.
The reason behind this issue is this syntax of passing value is also true for object reference passing through HTML pages. We know that the reference acts as pointer to point to original variable rather than make a copy, any using of the reference is indeed reference back to parent HTML's object. It does cause a complex process behind the scent.
For same reason, if one wants to turn array in JavaScript, the best approach is to turn it into a string first, then pass the string to php, then use explode() to turn it back to array in php.
http://www.webdeveloper.com/forum/archive/index.php/t-93920.html
iFrame Height Issue
Changing height of iFrame from JavaScript function becomes painful task in new versions of both IE and Firefox. However, if one uses iFrame, adjusting its height to fit its contents is essential. The problem is the height of content is unknown when dramatic feeding required. The “orthodox” method to accomplish this task is to open iFrame with any height first, then adjust it on onLoad event. Width is less painful because for those iFrame to fit complete screen width, width=“100%” can be defined when iFrame is defined.
Basic idea of this “orthodox” approach includes two steps, detect the correct height and resize to it.
Detecting methods include:
- document.documentElement.clientHeight
- contentWindow.document.body.scrollHeight
Resize method is:
- document.getElementById('myIframe').style.height = newHeight
Basically, this approach does not work. There are several problems around it, such as assignment to .style.height does not work in Firefox. More serious problem is contentWindow.document.body.scrollHeight will return permission denial error message if host page and iFrame content page are not in the same domain; more precisely, the scheme, hostname and port match.
Another approach is to find the correct height before opening the iFrame. It is hard to detect the height of page to be inside of iFrame. However, since iFrame can have its own scroll bar, it may not be necessary to detect content’s height. The best approach might be to find available screen height of user after your title head. Following methods have been tested in Firefox and IE:
It appears Internet Explorer does not response to any of detections.
Following is the codes to apply this approach:
Codes in head:
‹script type="text/javascript"›
var url = "http://www.google.com/" // URL for your iFrame.
var titleHeight = 240;
var frameHeight = Math.max((screen.availHeight ? (screen.availHeight-titleHeight) : (window.innerHeight ? (window.innerHeight-titleHeight) : document.body.clientHeight)), 768);
window.document.onload = function {document.getElementById('linkIframe').style.height = frameHeight};
‹/script›
Codes in body:
‹script type="text/javascript"›
document.write('‹iframe id="linkIframe" width="100%" height="' + frameHeight + '" src="' + url + '" name="content"›‹/iframe›');
‹/script›
Basic idea of this “orthodox” approach includes two steps, detect the correct height and resize to it.
Detecting methods include:
- document.documentElement.clientHeight
- contentWindow.document.body.scrollHeight
Resize method is:
- document.getElementById('myIframe').style.height = newHeight
Basically, this approach does not work. There are several problems around it, such as assignment to .style.height does not work in Firefox. More serious problem is contentWindow.document.body.scrollHeight will return permission denial error message if host page and iFrame content page are not in the same domain; more precisely, the scheme, hostname and port match.
Another approach is to find the correct height before opening the iFrame. It is hard to detect the height of page to be inside of iFrame. However, since iFrame can have its own scroll bar, it may not be necessary to detect content’s height. The best approach might be to find available screen height of user after your title head. Following methods have been tested in Firefox and IE:
It appears Internet Explorer does not response to any of detections.
Following is the codes to apply this approach:
Codes in head:
‹script type="text/javascript"›
var url = "http://www.google.com/" // URL for your iFrame.
var titleHeight = 240;
var frameHeight = Math.max((screen.availHeight ? (screen.availHeight-titleHeight) : (window.innerHeight ? (window.innerHeight-titleHeight) : document.body.clientHeight)), 768);
window.document.onload = function {document.getElementById('linkIframe').style.height = frameHeight};
‹/script›
Codes in body:
‹script type="text/javascript"›
document.write('‹iframe id="linkIframe" width="100%" height="' + frameHeight + '" src="' + url + '" name="content"›‹/iframe›');
‹/script›
Clearing Obstacles for MS Excel Application Upgrading to 2007
,Recently an Excel application wrote in 2003 version needs to be converted into 2007 following the network infrastructure upgrade. It is noticed there are three parts needed to be attended.
In VBA micro of old application, value of attribute FileFormat for ActiveWorkbook.SaveAs was xlNormal. It is no longer allowed in Excel 2007. The FileFormat now needs to be explicitly expressed. Common values are:
51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)
In this case, value of 52 is used. For more information, please refer to http://www.rondebruin.nl/saveas.htm.
File name is also an issue. This application automatically archives data everyday into a file, of which there is a space in file’s name. Now VBA in Excel 2007 converted the space to ‘%’ sign. Accordingly, the archiving file’s name needs to be changed, as well as the file type changed from .xls to .xlsm.
Originally archiving function involved large chunk of cells being copied from one worksheet to another. After conversion to Excel 2007, it is noticed the file’s size increase drastically without apparent reason. The educated guessing is somehow the application accumulated its clipboard during the automatic archiving process which involved copy-paste and save-as. However, nothing was found either in worksheets or clipboard. Web search shows no such error had been reported to Microsoft. After rewrote codes in this part to remove format as part of clipboard, the problem is resolved, although the reason is still not be found.
In VBA micro of old application, value of attribute FileFormat for ActiveWorkbook.SaveAs was xlNormal. It is no longer allowed in Excel 2007. The FileFormat now needs to be explicitly expressed. Common values are:
51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)
In this case, value of 52 is used. For more information, please refer to http://www.rondebruin.nl/saveas.htm.
File name is also an issue. This application automatically archives data everyday into a file, of which there is a space in file’s name. Now VBA in Excel 2007 converted the space to ‘%’ sign. Accordingly, the archiving file’s name needs to be changed, as well as the file type changed from .xls to .xlsm.
Originally archiving function involved large chunk of cells being copied from one worksheet to another. After conversion to Excel 2007, it is noticed the file’s size increase drastically without apparent reason. The educated guessing is somehow the application accumulated its clipboard during the automatic archiving process which involved copy-paste and save-as. However, nothing was found either in worksheets or clipboard. Web search shows no such error had been reported to Microsoft. After rewrote codes in this part to remove format as part of clipboard, the problem is resolved, although the reason is still not be found.
Clearing Obstacles for Automation in MS Access
There are many obstructive dialogue windows require manual response, which does really slow down the automation process. In one special case, MS Access is used to construct and update a considerable huge data warehouse. The update automation macro consists of over a hundred queries. The dialogue windows effectively destroy the automation itself.
Many of these dialogue windows can be set in Access Options. However, a special window cannot be resolved through setting. The message is “There isn't enough disk space or memory to undo the data changes this action query is about to make.” This is caused by running action queries on a large table.
Support of Microsoft regards this as an error message: http://support.microsoft.com/kb/161329, which also provides a number of solutions. Method 3, Setting the UseTransaction Property in an Action Query is particularily helpful:
If a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:
1. Open the query in Design view.
2. On the View menu, click Properties.
3. Click an empty space in the upper half of the query window to display the Query Properties dialog box.
4. Set the UseTransaction property to No.
5. Save the query and close it.
Most important, you would need to set no warnings, as follows:
1. In your first Macro row, right click mouse to insert a new row.
2. Click "Show All Actions" in Show/Hide menu.
3. In Action of your inserted first row, select SetWarnings.
4. Change Arguments value of the first row to "No".
5. Click Save.
Now you are ok to run Macros without any warning.
Many of these dialogue windows can be set in Access Options. However, a special window cannot be resolved through setting. The message is “There isn't enough disk space or memory to undo the data changes this action query is about to make.” This is caused by running action queries on a large table.
Support of Microsoft regards this as an error message: http://support.microsoft.com/kb/161329, which also provides a number of solutions. Method 3, Setting the UseTransaction Property in an Action Query is particularily helpful:
If a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:
1. Open the query in Design view.
2. On the View menu, click Properties.
3. Click an empty space in the upper half of the query window to display the Query Properties dialog box.
4. Set the UseTransaction property to No.
5. Save the query and close it.
Most important, you would need to set no warnings, as follows:
1. In your first Macro row, right click mouse to insert a new row.
2. Click "Show All Actions" in Show/Hide menu.
3. In Action of your inserted first row, select SetWarnings.
4. Change Arguments value of the first row to "No".
5. Click Save.
Now you are ok to run Macros without any warning.
A Good IT Certification Study Website
IT Exams is very good IT certification study website, if not the best. It links to a bunch of text books and dump tests, which are complete free. Unfortunately, it only has Java and Oracle certification information.
IT Exams' address is http://itexams.weebly.com/.
IT Exams' address is http://itexams.weebly.com/.
Temporarily Disable and Re-enable the Constraints in Oracle
SQL command script files to disable and enable all constraints:
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Software Patch Installation Batch Files
The example here is to install an Oracle database application patch. The basic structure of patch installation scripts shall include two folders and two files:
Folder 1: Logs
Folder 2: Scripts
File 1: Readme.txt
File 2: Setup.bat (or other name)
Of course, there would be some SQL script files in Scripts folder. Log files in Logs folder would be generated by these script files in Scripts folder. The working procedure is to run installer batch file (here Setup.bat), which will call other batch files (we do not have here) and/or script files stored in Scripts folder. In there example, the batch file open sqlplus.exe followed by user id, password and the SQL scripts (in file), or package to run.
Following is the example of batch file:
@echo off
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: PLEASE REVIEW AND ADJUST ENVIRONMENT VARIABLES BETWEEN < >
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CLS
set NETALIAS=
set USERID=
set PASSWORD=
set COMMONID=
set COMPASS=
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: ORACLE_VERSION are 9.2 (for 9i) or 10.2.0 (for 10g)
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ORACLE_VERSION=
set ORACLE_HOME=C:\oracle\product\%ORACLE_VERSION%\db_1
set SQLUTIL=%ORACLE_HOME%\bin\sqlplus.exe
set FOLDERPATH=%cd%
echo #####################################################
echo.
echo You are just about to run SCRAMBLING SCRIPT on database
echo.
echo [**** %NETALIAS% ****]
echo.
echo Schema
echo.
echo [**** %USERID% ****]
echo.
echo If this is not correct Database/Schema then
echo.
echo press CTL + C to cancel the script.....
echo.
echo #####################################################
pause
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Truncate.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Create_Tables.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.spec"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.body"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Cleanup.sql"
pause
Note: NETALIAS, USERID following “set” commend are variables. %...% is the way to use these variables.
Following is the Truncate.sql:
PROMPT Truncating data. Please wait...
TRUNCATE TABLE BATCH_BUF$RECON$DETAIL;
TRUNCATE TABLE BATCH_BUF$VALU$SUMMARY_PEN;
-- Enable/Disable primary, unique and foreign key constraints alter table BATCH_BUF$VALU$SUMMARY_PEN disable constraint BATCH_BUF$VALU$SUMMARY_PEN$FK1;
EXIT;
Folder 1: Logs
Folder 2: Scripts
File 1: Readme.txt
File 2: Setup.bat (or other name)
Of course, there would be some SQL script files in Scripts folder. Log files in Logs folder would be generated by these script files in Scripts folder. The working procedure is to run installer batch file (here Setup.bat), which will call other batch files (we do not have here) and/or script files stored in Scripts folder. In there example, the batch file open sqlplus.exe followed by user id, password and the SQL scripts (in file), or package to run.
Following is the example of batch file:
@echo off
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: PLEASE REVIEW AND ADJUST ENVIRONMENT VARIABLES BETWEEN < >
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CLS
set NETALIAS=
set USERID=
set PASSWORD=
set COMMONID=
set COMPASS=
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: ORACLE_VERSION are 9.2 (for 9i) or 10.2.0 (for 10g)
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ORACLE_VERSION=
set ORACLE_HOME=C:\oracle\product\%ORACLE_VERSION%\db_1
set SQLUTIL=%ORACLE_HOME%\bin\sqlplus.exe
set FOLDERPATH=%cd%
echo #####################################################
echo.
echo You are just about to run SCRAMBLING SCRIPT on database
echo.
echo [**** %NETALIAS% ****]
echo.
echo Schema
echo.
echo [**** %USERID% ****]
echo.
echo If this is not correct Database/Schema then
echo.
echo press CTL + C to cancel the script.....
echo.
echo #####################################################
pause
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Truncate.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Create_Tables.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.spec"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.body"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Cleanup.sql"
pause
Note: NETALIAS, USERID following “set” commend are variables. %...% is the way to use these variables.
Following is the Truncate.sql:
PROMPT Truncating data. Please wait...
TRUNCATE TABLE BATCH_BUF$RECON$DETAIL;
TRUNCATE TABLE BATCH_BUF$VALU$SUMMARY_PEN;
-- Enable/Disable primary, unique and foreign key constraints alter table BATCH_BUF$VALU$SUMMARY_PEN disable constraint BATCH_BUF$VALU$SUMMARY_PEN$FK1;
EXIT;
A Privacy Issue for Google AdSense
Google AdSense allows readers to gain the Publisher ID through viewing web page's source code in HTML. This may lead to privacy leaking to some degree.
Here is a case to show how people can explore such public available information. Blog site blog.dwnews.com is a popular political forum amongst overseas Chinese communities. The owner of this forum tries to create a forum without bias and welcomes people having different options to debate in the forum. As the result, the debates are always so ferocious between groups of pro-democracy and pro- Chinese Government. In aims to avoid the possible trouble with Chinese Government, many bloggers are anonymous there.
One blogger through Google AdSense' publisher ID found out several popular blogs were actually run by a same person, or presumably the same person because the AdSense Publisher IDs for these blogs are the same. He attached accordingly based on his finding, followed by a series of incidents. At the end of it, one of them declared he leaves the site permanently, and the account of the other one, ranked number one in current activities, had been cancelled by the webmaster. What lucky here is, both of them did not reside in China and we did not see someone ends up in the jail.
That was in June 2009, two months ago.
Here is a case to show how people can explore such public available information. Blog site blog.dwnews.com is a popular political forum amongst overseas Chinese communities. The owner of this forum tries to create a forum without bias and welcomes people having different options to debate in the forum. As the result, the debates are always so ferocious between groups of pro-democracy and pro- Chinese Government. In aims to avoid the possible trouble with Chinese Government, many bloggers are anonymous there.
One blogger through Google AdSense' publisher ID found out several popular blogs were actually run by a same person, or presumably the same person because the AdSense Publisher IDs for these blogs are the same. He attached accordingly based on his finding, followed by a series of incidents. At the end of it, one of them declared he leaves the site permanently, and the account of the other one, ranked number one in current activities, had been cancelled by the webmaster. What lucky here is, both of them did not reside in China and we did not see someone ends up in the jail.
That was in June 2009, two months ago.
A Practical Case of Transpose Query
Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.
Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:
1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:
SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.
2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:
SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.
3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:
SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Put everything together:
WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Done.
Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:
1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:
SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.
2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:
SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.
3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:
SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Put everything together:
WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Done.
Koncord Applied Excel Functions
We have uploaded some applied Excel functions. These are some basic functions, but very much useful in day-to-day working. This Excel file include some functions of string parsing, dates, telephone number parsing, and coordinates parsing.
Following is the link:
Koncord Applied Excel Functions
Following is the Terms of Services of Koncord Partners: http://koncordpartners.blogspot.com/2010/06/terms-of-services.html
Following is the link:
Koncord Applied Excel Functions
Following is the Terms of Services of Koncord Partners: http://koncordpartners.blogspot.com/2010/06/terms-of-services.html
Launch Koncord Homepage Online
We launched two size of Koncord Homepage online. Address is as follows:
Koncord Homepage
All you need to do is just set it as your homepage for your browsers.
Koncord Homepage
All you need to do is just set it as your homepage for your browsers.
Local Address Error in HTML for Firefox
If you include the address like this “C:\Documents and Settings\...” in your HTML page, it would be Ok for IE to open it. However, it would be a problem for Firefox, sometimes. Following is the error message:
There is easy way to fix it – simply add “file:\\\” in front of your local address. It would look like: "file:\\\C:\Documents and Settings\..."
http://www.codingforums.com/archive/index.php/t-53382.html
There is easy way to fix it – simply add “file:\\\” in front of your local address. It would look like: "file:\\\C:\Documents and Settings\..."
http://www.codingforums.com/archive/index.php/t-53382.html
First Beta Version of Koncord Homepage Is Free to Download
We have prepared a single HTML page which can be used as a Homepage for your browsers. This page consists of popular websites, such as Gmail, Hotmail, etc. Of course, it also has built-in Koncord Private Search Engine.
This homepage is just like favourites in IE and bookmarks in Firefox.
Here is free download site:
Download from Uploading.com
The address is: http://uploading.com/files/7Z17FDD2/Koncord.html.html
This homepage is just like favourites in IE and bookmarks in Firefox.
Here is free download site:
Download from Uploading.com
The address is: http://uploading.com/files/7Z17FDD2/Koncord.html.html
About ISO Image
An ISO image is an archive file (also known as a disk image) of an CD/DVD in a format defined by the International Organization for Standardization (ISO).
So, it is a virtual CD. There are three actions in relation to ISO image file.
1. Archiving a file or a folder into an ISO image file. This some times called create an ISO image file, or burn into an ISO image. The source files can be a CD/DVD as well. Currently many software installer directly accept this kind of virtual CD. For instance, installing an Linux OS on VMware Server, the VMware Server would prefer you have .iso files.
2. Turn ISO image back to real CD/DVD. Some time this procedure called burn an ISO image, burn an ISO CD/DVD, burn ISO image into CD/DVD.
3. Using or reading ISO image file. When you use an real CD/DVD, you would need a CD/DVD ROM Drive. So, to use ISO image file, the virtual CD/DVD, you will need a virtual CD ROM too. It is also called mount an ISO image, mount ISO file, etc.
Many times, verbs Create, Burn and Mount are interchangeable. It just create confusion what they are talking about. Software of ISO Image may not having all of these three functions. Installer software of course covers the third function mentioned above. So, if you have software installation files already sit in your hard disk, and want to install it, but your installer software could only accept the ISO or CD (this process sometimes called “network installation”), what you got to do?
Option A: Burn your files into a CD.
Option B: Archive your files into a ISO image file. The best software for this purpose is Folder2Iso at http://depositfiles.com/en/files/789051.
http://en.wikipedia.org/wiki/.iso
http://www.online-tech-tips.com/computer-tips/how-to-create-mount-and-burn-iso-image-files-for-free/
http://www.petri.co.il/mount_iso_files_in_windows_vista.htm
http://en.wikipedia.org/wiki/List_of_ISO_image_software
So, it is a virtual CD. There are three actions in relation to ISO image file.
1. Archiving a file or a folder into an ISO image file. This some times called create an ISO image file, or burn into an ISO image. The source files can be a CD/DVD as well. Currently many software installer directly accept this kind of virtual CD. For instance, installing an Linux OS on VMware Server, the VMware Server would prefer you have .iso files.
2. Turn ISO image back to real CD/DVD. Some time this procedure called burn an ISO image, burn an ISO CD/DVD, burn ISO image into CD/DVD.
3. Using or reading ISO image file. When you use an real CD/DVD, you would need a CD/DVD ROM Drive. So, to use ISO image file, the virtual CD/DVD, you will need a virtual CD ROM too. It is also called mount an ISO image, mount ISO file, etc.
Many times, verbs Create, Burn and Mount are interchangeable. It just create confusion what they are talking about. Software of ISO Image may not having all of these three functions. Installer software of course covers the third function mentioned above. So, if you have software installation files already sit in your hard disk, and want to install it, but your installer software could only accept the ISO or CD (this process sometimes called “network installation”), what you got to do?
Option A: Burn your files into a CD.
Option B: Archive your files into a ISO image file. The best software for this purpose is Folder2Iso at http://depositfiles.com/en/files/789051.
http://en.wikipedia.org/wiki/.iso
http://www.online-tech-tips.com/computer-tips/how-to-create-mount-and-burn-iso-image-files-for-free/
http://www.petri.co.il/mount_iso_files_in_windows_vista.htm
http://en.wikipedia.org/wiki/List_of_ISO_image_software
Another Urban Legend?
Just heard a story. A guy bought a desktop of the most popular PC brand with a most popular PC operating system, a year ago. The OS’s version is that has a very bad reputation, you know. He found a “black process” or processes run on his PC, which has following features:
1. The process does not registered in Task Manager, neither in CPU nor in Memory.
2. It runs regularly every day from around 1:00 am, and stops in next morning. However, every Saturday is bad. It runs until around 1:00 pm, sometime last to 8:00 pm. It does not start or end at exactly same time everyday. You would see this speed makes the PC completely useless.
3. It occupies huge amount of CPU and Memory, which makes a noticeable and considerable slow of response from PC. For instance, Ctrl + Alt + Del may take up to five minutes to enable Task Manager appears. The total RAM is 2G. When black process running and when no other applications opens (just re-started PC), Task Manager shows total 2% being used and around 20% Memory being used, yet, the PC is as slow as any mouse movement can take 2 minutes.
4. Total amount of 50G Hard Disk space could not be accounted for, given the fact of total 110G Hard Disk space has been used including all hidden files and recycle bin. When black process runs, the Hard Disk is flashing. The total Hard Disk space if around 460G for this driver (C:\).
5. Black process running does not require the Internet connection.
6. Black process runs before user logins into OS. Restarting computer, either softly or hardly does not stop its running. It has been noticed Safe Mode could not stop it either.
7. Do not know if it was caused by the black process or system bug, when black process runs, SOMETIMES Ctrl + Alt + Del generate an error message in pop-up window: “Unable to create a security option. Login failed”.
8. It was believed as virus infection. However, he failed to find out if this contention can be confirmed after he tried much anti-virus software.
9. No harm has been noticed apart from the slow response from PC.
10. It has been confirmed this black process is not any of the scheduled system maintenance.
Based on these limited information, it is hard to make conclusion. However, it looks like a system bug or an urban legend – someone is conducting a secret SETI@home like distributed computing scheme.
http://en.wikipedia.org/wiki/SETI@home
1. The process does not registered in Task Manager, neither in CPU nor in Memory.
2. It runs regularly every day from around 1:00 am, and stops in next morning. However, every Saturday is bad. It runs until around 1:00 pm, sometime last to 8:00 pm. It does not start or end at exactly same time everyday. You would see this speed makes the PC completely useless.
3. It occupies huge amount of CPU and Memory, which makes a noticeable and considerable slow of response from PC. For instance, Ctrl + Alt + Del may take up to five minutes to enable Task Manager appears. The total RAM is 2G. When black process running and when no other applications opens (just re-started PC), Task Manager shows total 2% being used and around 20% Memory being used, yet, the PC is as slow as any mouse movement can take 2 minutes.
4. Total amount of 50G Hard Disk space could not be accounted for, given the fact of total 110G Hard Disk space has been used including all hidden files and recycle bin. When black process runs, the Hard Disk is flashing. The total Hard Disk space if around 460G for this driver (C:\).
5. Black process running does not require the Internet connection.
6. Black process runs before user logins into OS. Restarting computer, either softly or hardly does not stop its running. It has been noticed Safe Mode could not stop it either.
7. Do not know if it was caused by the black process or system bug, when black process runs, SOMETIMES Ctrl + Alt + Del generate an error message in pop-up window: “Unable to create a security option. Login failed”.
8. It was believed as virus infection. However, he failed to find out if this contention can be confirmed after he tried much anti-virus software.
9. No harm has been noticed apart from the slow response from PC.
10. It has been confirmed this black process is not any of the scheduled system maintenance.
Based on these limited information, it is hard to make conclusion. However, it looks like a system bug or an urban legend – someone is conducting a secret SETI@home like distributed computing scheme.
http://en.wikipedia.org/wiki/SETI@home
Encoding of Chinese Characters
In computer text applications, the GB encoding scheme most often renders simplified Chinese characters, while Big5 most often renders traditional characters. Although neither encoding has an explicit connection with a specific character set, the lack of a one-to-one mapping between the simplified and traditional sets established a de facto linkage.
Since simplified Chinese conflated many characters into one and since the initial version of the GB encoding scheme, known as GB2312-80, contained only one code point for each character, it is impossible to use GB2312 to map to the bigger set of traditional characters. It is theoretically possible to use Big5 code to map to the smaller set of simplified character glyphs, although there is little market for such a product. Newer and alternative forms of GB have support for traditional characters. In particular, mainland authorities have now established GB 18030 as the official encoding standard for use in all mainland software publications. The encoding contains all East Asian characters included in Unicode 3.0. As such, GB 18030 encoding contains both simplified and traditional characters found in Big-5 and GB, as well as all characters found in Japanese and Korean encodings.
Unicode deals with the issue of simplified and traditional characters as part of the project of Han unification by including code points for each. This was rendered necessary by the fact that the linkage between simplified characters and traditional characters is not one-to-one. While this means that a Unicode system can display both simplified and traditional characters, it also means that different localization files are needed for each type.
The Chinese characters used in modern Japanese have also undergone simplification, but generally to a lesser extent than with simplified Chinese, it's worth mentioning that Japanese writing system reduced the number of Chinese characters in daily use, which was also part of the Japanese language reforms, thus, a number of complex characters were written phonetically. Reconciling these different character sets in Unicode became part of the controversial process of Han unification. Not surprisingly, some of the Chinese characters used in Japan are neither 'traditional' nor 'simplified'. In this case, these characters cannot be found in traditional/simplified Chinese dictionaries.
As a conclusion, GB18030 is the better choice for both Simplified Chinese and Traditional Chinese. Accordingly, in Google's language settings, zh-Hans/lang_zh_Hans is for Simplified Chinese and zh-Hant/lang_zh_Hant is for Traditional Chinese.
http://en.wikipedia.org/wiki/Simplified_Chinese_characters
http://www.google.com/coop/docs/cse/resultsxml.html#chineseSearch
Since simplified Chinese conflated many characters into one and since the initial version of the GB encoding scheme, known as GB2312-80, contained only one code point for each character, it is impossible to use GB2312 to map to the bigger set of traditional characters. It is theoretically possible to use Big5 code to map to the smaller set of simplified character glyphs, although there is little market for such a product. Newer and alternative forms of GB have support for traditional characters. In particular, mainland authorities have now established GB 18030 as the official encoding standard for use in all mainland software publications. The encoding contains all East Asian characters included in Unicode 3.0. As such, GB 18030 encoding contains both simplified and traditional characters found in Big-5 and GB, as well as all characters found in Japanese and Korean encodings.
Unicode deals with the issue of simplified and traditional characters as part of the project of Han unification by including code points for each. This was rendered necessary by the fact that the linkage between simplified characters and traditional characters is not one-to-one. While this means that a Unicode system can display both simplified and traditional characters, it also means that different localization files are needed for each type.
The Chinese characters used in modern Japanese have also undergone simplification, but generally to a lesser extent than with simplified Chinese, it's worth mentioning that Japanese writing system reduced the number of Chinese characters in daily use, which was also part of the Japanese language reforms, thus, a number of complex characters were written phonetically. Reconciling these different character sets in Unicode became part of the controversial process of Han unification. Not surprisingly, some of the Chinese characters used in Japan are neither 'traditional' nor 'simplified'. In this case, these characters cannot be found in traditional/simplified Chinese dictionaries.
As a conclusion, GB18030 is the better choice for both Simplified Chinese and Traditional Chinese. Accordingly, in Google's language settings, zh-Hans/lang_zh_Hans is for Simplified Chinese and zh-Hant/lang_zh_Hant is for Traditional Chinese.
http://en.wikipedia.org/wiki/Simplified_Chinese_characters
http://www.google.com/coop/docs/cse/resultsxml.html#chineseSearch
Why You Need a Private Search Engine
By create your own private search engine, you can put all your regular visiting websites into a single interface html page, then set it as your homepage. If you like, you can even set automatic login by a single click.
http://esupport.icewarp.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=51
http://esupport.icewarp.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=51
Set Language in Private Search Engine
Our previous posting Create Your Own Search Engine (http://koncordpartners.blogspot.com/2009/07/create-your-own-search-engine.html) could only display search results in English. Actually, the codes in that posting only have 4 parameters, you can add much more to suit your own needs. These 4 Google WebSearch Query Parameters are:
cx - Required. The cx parameter specifies a unique code that identifies a custom search engine. You must specify a Custom Search Engine using the cx parameter to retrieve search results from that CSE.
ie - Optional. The ie parameter sets the character encoding scheme that should be used to interpret the query string. The default ie value is latin1.
q - Optional. The q parameter specifies the search query entered by the user. Even though this parameter is optional, you must specify a value for at least one of the query parameters (as_epq, as_lq, as_oq, as_q, as_rq) to get search results.
sa - Required. It is indeed you submit the search query to Google search engine.
Today, we add three more parameters to show how the Simplified Chinese and Traditional Chinese can be interchangeable as search result.
hl - Optional. The hl parameter specifies the interface language (host language) of your user interface. To improve the performance and the quality of your search results, you are strongly encouraged to set this parameter explicitly. However, a successful search engine does not rely only on the parameters, but also settings of search engine itself. That is why you may also need your own account. “Site language”, is not only the site language, but also the search engine host language (the display language other than search result). Naturally, it should be set as English. “Your site encoding”, which is the searching language, should be GB18030 in this example.
lr - Optional. The lr (language restrict) parameter restricts search results to documents written in a particular language. Google WebSearch determines the language of a document by analyzing:
- the top-level domain (TLD) of the document's URL
- language meta tags within the document
- the primary language used in the body text of the document
c2coff - Optional. The c2coff parameter enables or disables the Simplified and Traditional Chinese Search feature. Please note, this parameter is only related to Chinese. For other language, no need to use this. The default value for this parameter is "0" (zero), meaning that the feature is enabled. Values for the c2coff parameter are: 1 is disabled, and 0 is enabled.
Following is codes with these two parameters. It means the search will be restricted to only websites wrote in three languages: US English, Simplified Chinese and Traditional Chinese.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
This is private search engine using Google facility
</title>
</head>
<body>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input type="hidden" value="GB18030" name="ie">
<input type="hidden" value="lang_en|lang_zh-Hans|lang_zh-Hant" name="lr">
<input type="hidden" value="0" name="c2coff">
<p align="center">
<img alt="Google" src="http://www.google.com/intl/en_us/images/logo.gif" />
</p>
<p align="center">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</p>
</form>
<p align="center">
Koncord Private Search Engine Based on New Google Technology
</p>
</body>
</html>
http://www.google.com/coop/docs/cse/resultsxml.html
cx - Required. The cx parameter specifies a unique code that identifies a custom search engine. You must specify a Custom Search Engine using the cx parameter to retrieve search results from that CSE.
ie - Optional. The ie parameter sets the character encoding scheme that should be used to interpret the query string. The default ie value is latin1.
q - Optional. The q parameter specifies the search query entered by the user. Even though this parameter is optional, you must specify a value for at least one of the query parameters (as_epq, as_lq, as_oq, as_q, as_rq) to get search results.
sa - Required. It is indeed you submit the search query to Google search engine.
Today, we add three more parameters to show how the Simplified Chinese and Traditional Chinese can be interchangeable as search result.
hl - Optional. The hl parameter specifies the interface language (host language) of your user interface. To improve the performance and the quality of your search results, you are strongly encouraged to set this parameter explicitly. However, a successful search engine does not rely only on the parameters, but also settings of search engine itself. That is why you may also need your own account. “Site language”, is not only the site language, but also the search engine host language (the display language other than search result). Naturally, it should be set as English. “Your site encoding”, which is the searching language, should be GB18030 in this example.
lr - Optional. The lr (language restrict) parameter restricts search results to documents written in a particular language. Google WebSearch determines the language of a document by analyzing:
- the top-level domain (TLD) of the document's URL
- language meta tags within the document
- the primary language used in the body text of the document
c2coff - Optional. The c2coff parameter enables or disables the Simplified and Traditional Chinese Search feature. Please note, this parameter is only related to Chinese. For other language, no need to use this. The default value for this parameter is "0" (zero), meaning that the feature is enabled. Values for the c2coff parameter are: 1 is disabled, and 0 is enabled.
Following is codes with these two parameters. It means the search will be restricted to only websites wrote in three languages: US English, Simplified Chinese and Traditional Chinese.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
This is private search engine using Google facility
</title>
</head>
<body>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input type="hidden" value="GB18030" name="ie">
<input type="hidden" value="lang_en|lang_zh-Hans|lang_zh-Hant" name="lr">
<input type="hidden" value="0" name="c2coff">
<p align="center">
<img alt="Google" src="http://www.google.com/intl/en_us/images/logo.gif" />
</p>
<p align="center">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</p>
</form>
<p align="center">
Koncord Private Search Engine Based on New Google Technology
</p>
</body>
</html>
http://www.google.com/coop/docs/cse/resultsxml.html
Create Your Own Search Engine
Following is the most basic HTML code to create the custom search engine based on Google’s technology. Actually, it just passes parameters to Google’s search engine. If you have your own AdSense account, replace it with yours. You can always change the words and contents as you wish. You may also wish to save it as home page for your browser.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
This is private search engine using Google facility
</title>
</head>
<body>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input type="hidden" value="ISO-8859-1" name="ie">
<p align="center">
<img alt="Google" src="http://www.google.com/intl/en_us/images/logo.gif" />
</p>
<p align="center">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</p>
</form>
<p align="center">
Koncord Private Search Engine Based on New Google Technology
</p>
</body>
</html>
Following is the most essential part of the HTML. Save it as HTML format and replace “<“ with “<” since blogspot.com using HTML and it is not possible to publish .
<html>
<body>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</form>
</body>
</html>
That is what looks like:
http://www.google.com/coop/docs/cse/resultsxml.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
This is private search engine using Google facility
</title>
</head>
<body>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input type="hidden" value="ISO-8859-1" name="ie">
<p align="center">
<img alt="Google" src="http://www.google.com/intl/en_us/images/logo.gif" />
</p>
<p align="center">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</p>
</form>
<p align="center">
Koncord Private Search Engine Based on New Google Technology
</p>
</body>
</html>
Following is the most essential part of the HTML. Save it as HTML format and replace “<“ with “<” since blogspot.com using HTML and it is not possible to publish .
<html>
<body>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</form>
</body>
</html>
That is what looks like:
http://www.google.com/coop/docs/cse/resultsxml.html
Install Oracle Enterprise Linux on VMware Server
Atul Kumar has provided very clear procedures how to download and install VMware 2.0 and Oracle Enterprise Linux for Oracle database in http://onlineappsdba.com/index.php/2009/01/26/download-install-vmware-20-and-oracle-enterprise-linux-for-oracle-database/.
This article is served as an supplement to Atul Kumar's procedure.
1.Download and Unzip Oracle Enterprise Linux 5
Click http://koncordpartners.blogspot.com/2009/07/download-oracle-enterprise-linux.html to learn more about this. Please download Oracle Enterprise Linux 5 instead of JeSO. Please do only unzip and do NOT extract files out from ISO files. The VMware will read ISO files directly.
2.Download and Install VMware Server on Windows Machine
Click http://koncordpartners.blogspot.com/2009/07/installation-of-vmwares-vmware-server.html to learn more about this.
3.Configure Linux Machine on VMware Server
First, create a local folder/driver to store VM related files. This can be a folder in your C:\ Driver. In Atul Kumar's example, it is E:\ Driver. Then, copy your unzipped Linux files into this folder. The Linux files should be in iso format. Now, you can start Atul Kumar's procedure No.3.
After click “Use an ISO Image”, please point to the first ISO disc file, such as “Enterprise-R5-U3-Sever-i386-disc1.iso”.
4.Install Oracle Enterprise Linux 5 on VMware Server 2.0
Atul Kumar's procedure No.4 indeed is another article: http://onlineappsdba.com/index.php/2009/01/28/install-oracle-enterprise-linux-52-on-vmware-server-20/.
In VMware, click your VM in Inventory at left hand side, you will see Console appears on the middle pane. Click it, if you have not install Console, install it now.
After finish install iso disc 1, you will be prompted to change CDROM: Please insert Enterprise Linux disc 2 to continue. You need to:
a) Press Ctrl Alt. This enable you switch from Linux to Windows. Ctrl G will enable you switch from Windows to Linus.
b) Move mouse to the top of Linux installation window, click Devices.
c) You need to first disconnect the previous disc you have done, by click Disconnect...(the name of your previous Linux disc), next to CD/DVD Drive 1.
d) A pop-up window will ask you to confirm this action. Click Yes.
e) Move mouse to the top of Linux installation window, click Devices.
f) Click CD/DVD Drive 1.
g) Click Connect to Disk Image File (iso).
h) Find “Enterprise-R5-U3-Sever-i386-disc2.iso”.
Do the same for rest discs.
5.Install 10g Database or Oracle Apps 11i/R12 on Oracle Enterprise Linux
Sure, it is not limited to 10g Database or Oracle Apps 11i/R12 only.
http://onlineappsdba.com/index.php/2009/01/26/download-install-vmware-20-and-oracle-enterprise-linux-for-oracle-database/
http://onlineappsdba.com/index.php/2009/01/28/install-oracle-enterprise-linux-52-on-vmware-server-20/
http://onlineappsdba.com/index.php/2007/03/04/install-oracle-enterprise-linux-on-vmware/
This article is served as an supplement to Atul Kumar's procedure.
1.Download and Unzip Oracle Enterprise Linux 5
Click http://koncordpartners.blogspot.com/2009/07/download-oracle-enterprise-linux.html to learn more about this. Please download Oracle Enterprise Linux 5 instead of JeSO. Please do only unzip and do NOT extract files out from ISO files. The VMware will read ISO files directly.
2.Download and Install VMware Server on Windows Machine
Click http://koncordpartners.blogspot.com/2009/07/installation-of-vmwares-vmware-server.html to learn more about this.
3.Configure Linux Machine on VMware Server
First, create a local folder/driver to store VM related files. This can be a folder in your C:\ Driver. In Atul Kumar's example, it is E:\ Driver. Then, copy your unzipped Linux files into this folder. The Linux files should be in iso format. Now, you can start Atul Kumar's procedure No.3.
After click “Use an ISO Image”, please point to the first ISO disc file, such as “Enterprise-R5-U3-Sever-i386-disc1.iso”.
4.Install Oracle Enterprise Linux 5 on VMware Server 2.0
Atul Kumar's procedure No.4 indeed is another article: http://onlineappsdba.com/index.php/2009/01/28/install-oracle-enterprise-linux-52-on-vmware-server-20/.
In VMware, click your VM in Inventory at left hand side, you will see Console appears on the middle pane. Click it, if you have not install Console, install it now.
After finish install iso disc 1, you will be prompted to change CDROM: Please insert Enterprise Linux disc 2 to continue. You need to:
a) Press Ctrl Alt. This enable you switch from Linux to Windows. Ctrl G will enable you switch from Windows to Linus.
b) Move mouse to the top of Linux installation window, click Devices.
c) You need to first disconnect the previous disc you have done, by click Disconnect...(the name of your previous Linux disc), next to CD/DVD Drive 1.
d) A pop-up window will ask you to confirm this action. Click Yes.
e) Move mouse to the top of Linux installation window, click Devices.
f) Click CD/DVD Drive 1.
g) Click Connect to Disk Image File (iso).
h) Find “Enterprise-R5-U3-Sever-i386-disc2.iso”.
Do the same for rest discs.
5.Install 10g Database or Oracle Apps 11i/R12 on Oracle Enterprise Linux
Sure, it is not limited to 10g Database or Oracle Apps 11i/R12 only.
http://onlineappsdba.com/index.php/2009/01/26/download-install-vmware-20-and-oracle-enterprise-linux-for-oracle-database/
http://onlineappsdba.com/index.php/2009/01/28/install-oracle-enterprise-linux-52-on-vmware-server-20/
http://onlineappsdba.com/index.php/2007/03/04/install-oracle-enterprise-linux-on-vmware/
Installation of VMware's VMware Server
First, what is it? VMware Server is a software enable you to create a multiple virtual machines within your existing operating system. It is not a virtual machine itself.
VMware Server is a Virtual Private Server (Server Virtualization). A VPS, also referred to as Virtual Dedicated Server (VDS), is a method of partitioning a physical server computer into multiple servers such that each has the appearance and capabilities of running on its own dedicated machine. Each virtual server can run its own full-fledged operating system, and each server can be independently rebooted. The physical server boots normally. It then runs a program that boots each virtual server within a virtualization environment. The virtual servers have no direct access to hardware and are usually booted from a disk image. A disk image is a single file or storage device containing the complete contents and structure representing a data storage medium or device, such as a hard drive, CD, or DVD. Some disk imaging utilities omit unused file space from source media, or compress the disk they represent to reduce storage requirements, though these are typically referred to as archive files, as they are not literally disk images. The most common format of disk image is ISO.
Install and run VMware Server as an application on top of a host Windows or Linux operating system. A thin virtualization layer partitions the physical server so you can run multiple virtual machines simultaneously on a single server. Computing resources of the physical server are treated as a uniform pool of resources that can be allocated to virtual machines in a controlled manner.
VMware Server isolates each virtual machine from its host and other virtual machines, leaving it unaffected if another virtual machine crashes. Your data does not leak across virtual machines and your applications can only communicate over configured network connections. VMware Server encapsulates a virtual machine environment as a set of files, which are easy to back-up, move and copy.
According to Vmware, VMware Server is typically used in the following scenarios:
■Assessing virtualization for the 1st time
■Evaluating software using virtual machines
■Test and development of software and IT environments
Following matrix can help to determine if VMware Server is suitable:
.jpg)
https://www.vmware.com/freedownload/login.php?product=server20 is the site for download. A free registration is needed. Go to your email account to activiate before download. In “Binaries”, select the option in according with the current operating system. During installation process, you may also prompt to enter serial number, which can be find in the same email.
A icon of VMware Server Home Page will be created. However, when click this icon, someone might be found https://[localhost, usually it is your computer name; no square brackets]:8333/ui/# could not be acceessed. In this case, you would need to try again and set allow intranet.
Some may find following message:
There is a problem with this website's security certificate.
The security certificate presented by this website was not issued by a trusted certificate authority.
Security certificate problems may indicate an attempt to fool you or intercept any data you send to the server.
We recommend that you close this webpage and do not continue to this website.
- Click here to close this webpage.
- Continue to this website (not recommended).
- More information.
Please click “Continue to this website (not recommended).”
Then, when you try to access VMware Server, you may get an error message saying "Access Denied" or ask you to enter the Login Name and Password in a pop-up window of VMware Infrastructure Web Access. Please use your Window's Login Name and Password. Remember, you must have Administrator previlege in your PC.
By now, you should have no problem to download, install and login the VMware Server.
Rating? Five Stars from us.
http://www.vmware.com/products/server/overview.html
http://en.wikipedia.org/wiki/Server_virtualization
http://en.wikipedia.org/wiki/Disk_image
http://www.electrictoolbox.com/access-denied-vmware-infrastructure-web-access/
VMware Server is a Virtual Private Server (Server Virtualization). A VPS, also referred to as Virtual Dedicated Server (VDS), is a method of partitioning a physical server computer into multiple servers such that each has the appearance and capabilities of running on its own dedicated machine. Each virtual server can run its own full-fledged operating system, and each server can be independently rebooted. The physical server boots normally. It then runs a program that boots each virtual server within a virtualization environment. The virtual servers have no direct access to hardware and are usually booted from a disk image. A disk image is a single file or storage device containing the complete contents and structure representing a data storage medium or device, such as a hard drive, CD, or DVD. Some disk imaging utilities omit unused file space from source media, or compress the disk they represent to reduce storage requirements, though these are typically referred to as archive files, as they are not literally disk images. The most common format of disk image is ISO.
Install and run VMware Server as an application on top of a host Windows or Linux operating system. A thin virtualization layer partitions the physical server so you can run multiple virtual machines simultaneously on a single server. Computing resources of the physical server are treated as a uniform pool of resources that can be allocated to virtual machines in a controlled manner.
VMware Server isolates each virtual machine from its host and other virtual machines, leaving it unaffected if another virtual machine crashes. Your data does not leak across virtual machines and your applications can only communicate over configured network connections. VMware Server encapsulates a virtual machine environment as a set of files, which are easy to back-up, move and copy.
According to Vmware, VMware Server is typically used in the following scenarios:
■Assessing virtualization for the 1st time
■Evaluating software using virtual machines
■Test and development of software and IT environments
Following matrix can help to determine if VMware Server is suitable:
.jpg)
https://www.vmware.com/freedownload/login.php?product=server20 is the site for download. A free registration is needed. Go to your email account to activiate before download. In “Binaries”, select the option in according with the current operating system. During installation process, you may also prompt to enter serial number, which can be find in the same email.
A icon of VMware Server Home Page will be created. However, when click this icon, someone might be found https://[localhost, usually it is your computer name; no square brackets]:8333/ui/# could not be acceessed. In this case, you would need to try again and set allow intranet.
Some may find following message:
There is a problem with this website's security certificate.
The security certificate presented by this website was not issued by a trusted certificate authority.
Security certificate problems may indicate an attempt to fool you or intercept any data you send to the server.
We recommend that you close this webpage and do not continue to this website.
- Click here to close this webpage.
- Continue to this website (not recommended).
- More information.
Please click “Continue to this website (not recommended).”
Then, when you try to access VMware Server, you may get an error message saying "Access Denied" or ask you to enter the Login Name and Password in a pop-up window of VMware Infrastructure Web Access. Please use your Window's Login Name and Password. Remember, you must have Administrator previlege in your PC.
By now, you should have no problem to download, install and login the VMware Server.
Rating? Five Stars from us.
http://www.vmware.com/products/server/overview.html
http://en.wikipedia.org/wiki/Server_virtualization
http://en.wikipedia.org/wiki/Disk_image
http://www.electrictoolbox.com/access-denied-vmware-infrastructure-web-access/
Detect a Leap Year with PL/SQL
We have seen some very complicated ways to detect leap year. Follow functions are probably the most elegant method we can find:
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in number.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( nYr in number
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(nYr)||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in date.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( dDate in date
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(extract(year from dDate))||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
Following is Don Burleson’s scripts:
create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
return TRUE;
else
return FALSE; -- otherwise year is not a leap year, return false
end if;
end;
http://www.dba-oracle.com/t_detect_leap_year_function.htm
http://www.dba-oracle.com/oracle_news/2005_8_23_function_detect_leap_year.htm
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in number.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( nYr in number
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(nYr)||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in date.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( dDate in date
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(extract(year from dDate))||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
Following is Don Burleson’s scripts:
create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
return TRUE;
else
return FALSE; -- otherwise year is not a leap year, return false
end if;
end;
http://www.dba-oracle.com/t_detect_leap_year_function.htm
http://www.dba-oracle.com/oracle_news/2005_8_23_function_detect_leap_year.htm
Download Oracle VM Template
Oracle VM and VMware are same things, because they all provide a virtual machine. And no, they are different. Oracle VM is based on the open-source Xen hypervisor technology, supports both Windows and Linux guests and includes an integrated Web browser based management console. Oracle VM features fully tested and certified Oracle Applications stack in an enterprise virtualization environment. However, that's not to say Oracle products won't run on the other virtualization servers. If the purpose to install the VM is not only for Oracle products, one should consider VMware or VirtualBox.
When go to http://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome, there are two options to chose, Oracle VM and Oracle VM Templates. The difference between them is the Oracle VM Templates are “fully configured software stack by offering pre-installed and pre-configured software images. Use of Oracle VM Templates eliminates the installation and configuration costs, and reduces the ongoing maintenance costs helping organizations achieve faster time to market and lower cost of operations. Oracle VM Templates of many key Oracle products are available for download, including Oracle Database, Enterprise Linux, Fusion Middleware, and many more.” At least these are Oracle’s objectives, if one knows how to download and how to install. One must remember Oracle VM Template could not work standalone. It must work with the Oracle VM Maanger.
Following options might be most selected:
- Oracle VM Templates for Oracle Database Media Pack for x86 (32 bit) 2.2.0.0.0 B51904-02
- Oracle VM Templates for Oracle Enterprise Linux 5 Media Pack for x86 (32 bit) 2.2.0.0.0 B52024-03
There are a few abbreviations worthwhile to know:
HVM – stands for Hardware Virtual Machine, and is usually used in reference to AMD’s and Intel’s Virtualization technology included in their new processors (Intel VT and AMD Pacifica).
PV – stands for paravirtualization, a virtualization technique that presents a software interface to virtual machines that is similar but not identical to that of the underlying hardware.
http://en.wikipedia.org/wiki/Virtual_machine
http://en.wikipedia.org/wiki/Oracle_VM
http://www.oracle.com/technology/products/vm/templates/index.html
http://www.crucialp.com/blog/2008/04/21/what-is-hvm/
When go to http://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome, there are two options to chose, Oracle VM and Oracle VM Templates. The difference between them is the Oracle VM Templates are “fully configured software stack by offering pre-installed and pre-configured software images. Use of Oracle VM Templates eliminates the installation and configuration costs, and reduces the ongoing maintenance costs helping organizations achieve faster time to market and lower cost of operations. Oracle VM Templates of many key Oracle products are available for download, including Oracle Database, Enterprise Linux, Fusion Middleware, and many more.” At least these are Oracle’s objectives, if one knows how to download and how to install. One must remember Oracle VM Template could not work standalone. It must work with the Oracle VM Maanger.
Following options might be most selected:
- Oracle VM Templates for Oracle Database Media Pack for x86 (32 bit) 2.2.0.0.0 B51904-02
- Oracle VM Templates for Oracle Enterprise Linux 5 Media Pack for x86 (32 bit) 2.2.0.0.0 B52024-03
There are a few abbreviations worthwhile to know:
HVM – stands for Hardware Virtual Machine, and is usually used in reference to AMD’s and Intel’s Virtualization technology included in their new processors (Intel VT and AMD Pacifica).
PV – stands for paravirtualization, a virtualization technique that presents a software interface to virtual machines that is similar but not identical to that of the underlying hardware.
http://en.wikipedia.org/wiki/Virtual_machine
http://en.wikipedia.org/wiki/Oracle_VM
http://www.oracle.com/technology/products/vm/templates/index.html
http://www.crucialp.com/blog/2008/04/21/what-is-hvm/
Download Oracle Enterprise Linux
So, you have problem to install an Oracle product in your PC. That is right, many operating systems are not certified by Oracle products, which means you are unable to install these Oracle products on this OS, or they won't work properly after installation. The Oracle products are indeed written to run on UNIX or Linux systems. So you decision to install Oracle Enterprise Linux is a right approach.
To download Oracle Enterprise Linux,
1. You can go directly to following URL: http://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome.
2. Fill your details. Many Oracle products are free to download. Same is true for Oracle Enterprise Linux.
3. In next screen, pick Enterprise Linux in “Select a Product Pack”. In “Platform”, pick up what you want. You have three options: IA64, x86 32 bit and x86 64bit. IA64 is the formal name of Itanium, a family of 64-bit Intel microprocessors that implement the Intel Itanium architecture. If you do not know what your computer is, check here: http://support.microsoft.com/kb/827218.
4. Don’t know what version to download? Here are comparisons: http://distrowatch.com/table.php?distribution=oracle. If the purpose is to install Oracle Enterprise Linux based on Oracle VM Templates, the best option would be Oracle Enterprise Linux JeOS for Building Oracle VM templates Media Pack v5 for x86 (32 bit).
http://www.oracle.com/technology/software/products/virtualization/vm_jeos.html
To download Oracle Enterprise Linux,
1. You can go directly to following URL: http://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome.
2. Fill your details. Many Oracle products are free to download. Same is true for Oracle Enterprise Linux.
3. In next screen, pick Enterprise Linux in “Select a Product Pack”. In “Platform”, pick up what you want. You have three options: IA64, x86 32 bit and x86 64bit. IA64 is the formal name of Itanium, a family of 64-bit Intel microprocessors that implement the Intel Itanium architecture. If you do not know what your computer is, check here: http://support.microsoft.com/kb/827218.
4. Don’t know what version to download? Here are comparisons: http://distrowatch.com/table.php?distribution=oracle. If the purpose is to install Oracle Enterprise Linux based on Oracle VM Templates, the best option would be Oracle Enterprise Linux JeOS for Building Oracle VM templates Media Pack v5 for x86 (32 bit).
http://www.oracle.com/technology/software/products/virtualization/vm_jeos.html
Date Format
People in United States use date format of mm/dd/yyyy. British, Australia and New Zealand use dd/mm/yyyy. Format of yyyy-mm-dd is an ISO 8601 international standard, and is favored by European countries and all Chinese spoken countries. Canada is THE country currently using all of these three formats. Format of yyyy-mm-dd is official format for Canada. Because they are a Commonwealth member country, they also use British format. And because they are heavily influenced by United States, in day-to-day life Canadians use American format most.
It is for sure IT industry should use yyyy-mm-dd format, not because it is an ISO 8601 standard, but also it is only meaningful format when you conduct textual sorting. For instance, when you use date as part of file name, it can be directly sorted by Windows Explorer.
In Microsoft Excel, if yyyy-mm-dd format is needed, please go to Format Cells, chose Locale as Chinese (Hong Kong S.A.R). It can be found in Type.
Sunday is traditionally regarded as the first day of the week. Different industries may have their own standard. However, the International Organization for Standardization (ISO) specifies that the week begins with Monday, which is also the standard for IT industry. So, day 1 in a week is Monday. The week ends at 23:59:59 of Sunday. Beware, Microsoft Excel's function weekday() does use Sunday as day 1 in a week. In addition, in Microsoft SQL Server DATEPART(WEEKDAY, dateToCheck) does also generate 2 for Monday.
Look at following three times:
Time 1: 2000-01-01 11:59:59 AM
Time 2: 2000-01-01 12:00:00
Time 3: 2000-01-01 12:00:01 PM
How many seconds between Time 1 and Time 2 above? How many seconds between Time 2 and Time 3? Are you sure? Would you think again, please? We human being just would like to confuse ourselves. Then look at these:
Time a: 2000-01-01 11:59:59 PM
Time b: 2000-01-01 0:00:00
Time c: 2000-01-01 0:00:01 AM
Time d: 2000-01-01 0:59:59 AM
Time e: 2000-01-01 1:00:00 PM
Time e: 2000-01-01 1:00:01 AM
So, if it is possible, please use military time (24 hour time system) instead of 12 hour time system.
Look at following three times:
Time 4: 2000-01-01 00:00:00
Time 5: 2000-01-01 24:00:00
Time 6: 2000-01-02 00:00:00
Are they at same time? Indeed, the format of Time 5 would never exist in any official document, except in some railway time tables, where 24:00:00 used only to describe the arrival time. Time 4 and Time 6 are often confused, though there is 24 hours difference. However, for the purpose of reporting period ending time, it is suggested to use format either as 1999-12-31 24:00:00 or 1999-12-31 23:59:59, but not 2000-01-01 00:00:00. This is particularly important for week report, because the reader could not tell from the glance at the date. For instance, 2010-04-12 00:00:00, how can you directly tell it is one second from 2010-04-11 00:00:01 or 2010-04-12 23:59:59? No, both are incorrect. The correct answer is it is one second from 2010-04-12 00:00:01 or 2010-04-11 23:59:59.
When the first week starts in a year? ISO 8601 provides the first week in a year starts from a Monday of a week with includes the first Thursday in that year. Therefore, the first week must include the January 4th. This approach does indeed emphasis the majority of the days in first week are in new year. This approach can also be used to determine the first week of a fiscal year as well as the first week of a month.
http://en.wikipedia.org/wiki/Date_and_time_notation_by_country
http://en.wikipedia.org/wiki/Calendar_date
http://en.wikipedia.org/wiki/ISO_8601
http://24hourtime.info/the-24-hour-time-system/
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/12-hour_clock
It is for sure IT industry should use yyyy-mm-dd format, not because it is an ISO 8601 standard, but also it is only meaningful format when you conduct textual sorting. For instance, when you use date as part of file name, it can be directly sorted by Windows Explorer.
In Microsoft Excel, if yyyy-mm-dd format is needed, please go to Format Cells, chose Locale as Chinese (Hong Kong S.A.R). It can be found in Type.
Sunday is traditionally regarded as the first day of the week. Different industries may have their own standard. However, the International Organization for Standardization (ISO) specifies that the week begins with Monday, which is also the standard for IT industry. So, day 1 in a week is Monday. The week ends at 23:59:59 of Sunday. Beware, Microsoft Excel's function weekday() does use Sunday as day 1 in a week. In addition, in Microsoft SQL Server DATEPART(WEEKDAY, dateToCheck) does also generate 2 for Monday.
Look at following three times:
Time 1: 2000-01-01 11:59:59 AM
Time 2: 2000-01-01 12:00:00
Time 3: 2000-01-01 12:00:01 PM
How many seconds between Time 1 and Time 2 above? How many seconds between Time 2 and Time 3? Are you sure? Would you think again, please? We human being just would like to confuse ourselves. Then look at these:
Time a: 2000-01-01 11:59:59 PM
Time b: 2000-01-01 0:00:00
Time c: 2000-01-01 0:00:01 AM
Time d: 2000-01-01 0:59:59 AM
Time e: 2000-01-01 1:00:00 PM
Time e: 2000-01-01 1:00:01 AM
So, if it is possible, please use military time (24 hour time system) instead of 12 hour time system.
Look at following three times:
Time 4: 2000-01-01 00:00:00
Time 5: 2000-01-01 24:00:00
Time 6: 2000-01-02 00:00:00
Are they at same time? Indeed, the format of Time 5 would never exist in any official document, except in some railway time tables, where 24:00:00 used only to describe the arrival time. Time 4 and Time 6 are often confused, though there is 24 hours difference. However, for the purpose of reporting period ending time, it is suggested to use format either as 1999-12-31 24:00:00 or 1999-12-31 23:59:59, but not 2000-01-01 00:00:00. This is particularly important for week report, because the reader could not tell from the glance at the date. For instance, 2010-04-12 00:00:00, how can you directly tell it is one second from 2010-04-11 00:00:01 or 2010-04-12 23:59:59? No, both are incorrect. The correct answer is it is one second from 2010-04-12 00:00:01 or 2010-04-11 23:59:59.
When the first week starts in a year? ISO 8601 provides the first week in a year starts from a Monday of a week with includes the first Thursday in that year. Therefore, the first week must include the January 4th. This approach does indeed emphasis the majority of the days in first week are in new year. This approach can also be used to determine the first week of a fiscal year as well as the first week of a month.
http://en.wikipedia.org/wiki/Date_and_time_notation_by_country
http://en.wikipedia.org/wiki/Calendar_date
http://en.wikipedia.org/wiki/ISO_8601
http://24hourtime.info/the-24-hour-time-system/
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/12-hour_clock
Subscribe to:
Posts (Atom)
Posts
