oracle (+) notation

ทำงานเหมือน OUTER JOIN ( มันออกมาก่อน ANSI standard จะประกาศว่ามี joins ) แม้แต่ oracle (11g Release 1 (11.1)เอง ยังแนะนำให้โค้ดใหม่ๆไปใช้ FROM .. OUTER JOIN

หลักการ : เครื่องหมายบวก ‘(+)’ อยู่ข้างที่ข้อมูลขาดหายไป (เป็น NULL)

จริงๆ มันก้อคือ LEFT OUTER JOIN/RIGHT OUTER JOIN ยุคโบร่ำโบราณนั่นแล

กฎการใช้ (+)

  • ไม่สามารถใช้ (+) operator ใน query block ที่มี FROM clause join syntax.
  • (+) operator สามารถเขียนไว้ใน WHERE clause หรือ, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view เท่านั้น
  • ถ้า A, B joined ด้วย multiple join conditions เราต้องใช้ (+) operator ในทุกๆ conditions ถ้าเราไม่ใช้ Oracle Database จะ return only rows resulting from a simple join, but without a warning or error ที่จะบอกว่า you do not have the results of an outer join.
  • The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
  • You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:

— The following statement is not valid:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;

However, the following self join is valid:

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id
ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;

  • (+) operator สามารถใช้กับ column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
  • WHERE condition ที่มี (+) operator ไม่สามารถ combined with condition อื่นๆ ด้วย OR operator
  • WHERE condition ไม่สามารถใช้ IN comparison condition เพื่อเปรียบเทียบ column marked with the (+) operator with an expression.

If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.

ref : nagul

[ excel ] vlookup

syntax

VLOOKUP(key, table_array, column to show, result search)

vlookup_example

 

image credits from about.com link is provided below

result search can be 0 ( ตรงตัว ) or 1 ( ใกล้เคียง )

example

=VLOOKUP(B2, entity!B2:D6709, 3, 0)

if we want to drag column to copy, don’t forget $.

example

=VLOOKUP(B2, entity!B$2:D$6709, 3, 0)

NOTE : the lookup column should be at the first column of table_array.

ref : http://spreadsheets.about.com/od/excelfunctions/ss/vlookup.htm
http://snasui.blogspot.com/2009/12/vlookup.html
http://www.excelexperttraining.com/forums/showthread.php?t=8405

TIPS

concatenate

=CONCATENATE(G34, "", H34)

 

SQL Join Cheat Sheet

useful naja

sqljoins_cheatsheetNatural Join คือ ไม่ต้องระบุว่าจะเอา column อะไรมาเป็นตัวเทียบเท่ากัน Database จะไปดูว่า column ใดที่ชื่อเหมือนกันในทั้ง 2 ตาราง  มันก็จะอนุมานเอา column นั้น มาเป็นใช้เป็นตัวเท่ากับ ในการ join ( ref )

javadoc troubleshooting

The below is some problem I faced when I due with javadoc. Therefore, I think it is good to  note it here.

Problem 1

C:\xxx\yyy.java:18: cannot access jxl.Cell
bad class file: C:\aaa\bbb\WEB-INF\lib\jxl.jar(jxl/Cell.class)
class file has wrong version 50.0, should be 49.0
Please remove or make sure it appears in the correct subdirectory of the classpath.
import jxl.Cell;

Solution

Use the right “javadoc” version. In this case we should use javadoc of jdk 6 ( 50.0 ), in stead of jdk 5 ( 49.0 ).

Note that in eclipse, we can set the “Javadoc command” from the “Generate Javadoc” menu.
Use the right javadoc program from there.

Problem 2

and another problem is that some jars may not have the doc attached.

That may affects if we generate the doc from the code that use annotation from the jar that has no doc attached. ( this is related with ‘package-list’ issue ).

The solution is point the -linkoffline to the doc folder, i.e.

-linkoffline file:/D:/sss/ttt/uuudoc . com.wordpress.dsin.mypackagename

VM option for larger heap space

-J-Xmx1024m

Encoding for Another language

-encoding "Shift-JIS" -docencoding "UTF-8" -charset "UTF-8"

mvn steps

[ERROR] Unknown lifecycle phase "build". You must specify a valid lifecycle phas
e or a goal in the format <plugin-prefix>:<goal> or <plugin-group-id>:<plugin-ar
tifact-id>[:<plugin-version>]:<goal>. Available lifecycle phases are: validate,
initialize, generate-sources, process-sources, generate-resources, process-resou
rces, compile, process-classes, generate-test-sources, process-test-sources, gen
erate-test-resources, process-test-resources, test-compile, process-test-classes
, test, prepare-package, package, pre-integration-test, integration-test, post-i
ntegration-test, verify, install, deploy, pre-clean, clean, post-clean, pre-site
, site, post-site, site-deploy. -> [Help 1]

or just simply

mvn install

The below command is used to create javadoc

mvn javadoc:javadoc