Search by Element and print RANum and position()

XML:

<InvoiceAdj>
  <RAInfo RANumType="79">
     <RANum>RANumType_79</RANum>
  </RAInfo>
  <RAInfo RANumType="82">
     <RANum>RANumType_82</RANum>
  </RAInfo>
  <RAInfo RANumType="85">
     <RANum>RANumType_85</RANum>
  </RAInfo>
  <RAInfo RANumType="88">
     <RANum>RANumType_88</RANum>
  </RAInfo>
  <RAInfo RANumType="79">
     <RANum>RANumType_79_1</RANum>
  </RAInfo>
  <RAInfo RANumType="82">
     <RANum>RANumType_82_1</RANum>
  </RAInfo>
  <RAInfo RANumType="85">
     <RANum>RANumType_85_1</RANum>
  </RAInfo>
  <RAInfo RANumType="88">
     <RANum>RANumType_88_1</RANum>
  </RAInfo>
</InvoiceAdj>

XSLT: Search by attribute RANum = 'RANumType_88'

<xsl:for-each select="InvoiceAdj/RAInfo">
<xsl:choose>
  <xsl:when test="RANum = 'RANumType_88'">
    <tr>
      <td><xsl:value-of select="RANum"/></td>
      <td><xsl:value-of select="position()"/></td>
      <td></td>
    </tr>
  </xsl:when>
</xsl:choose>                
</xsl:for-each>

HTML:

<tr>
<td>RANumType_88</td>
<td>4</td>
<td></td>
</tr>

Search by Attribute and print RANum and position()

XML:

<InvoiceAdj>
<RAInfo RANumType="79">
   <RANum>RANumType_79</RANum>
</RAInfo>
<RAInfo RANumType="82">
   <RANum>RANumType_82</RANum>
</RAInfo>
<RAInfo RANumType="85">
   <RANum>RANumType_85</RANum>
</RAInfo>
<RAInfo RANumType="88">
   <RANum>RANumType_88</RANum>
</RAInfo>
<RAInfo RANumType="79">
   <RANum>RANumType_79_1</RANum>
</RAInfo>
<RAInfo RANumType="82">
   <RANum>RANumType_82_1</RANum>
</RAInfo>
<RAInfo RANumType="85">
   <RANum>RANumType_85_1</RANum>
</RAInfo>
<RAInfo RANumType="88">
   <RANum>RANumType_88_1</RANum>
</RAInfo>
</InvoiceAdj>

XSLT: Search by attribute RANumType = 79 or 88

<xsl:for-each select="InvoiceAdj/RAInfo">
<xsl:choose>
<xsl:when test="@RANumType = 79 or @RANumType = 88">
  <tr>
    <td><xsl:value-of select="RANum"/></td>
    <td><xsl:value-of select="position()"/></td>
    <td></td>
  </tr>
</xsl:when>
</xsl:choose>      
</xsl:for-each>

HTML:

<tr>
<td>RANumType_79</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>RANumType_88</td>
<td>4</td>
<td></td>
</tr>
<tr>
<td>RANumType_79_1</td>
<td>5</td>
<td></td>
</tr>
<tr>
<td>RANumType_88_1</td>
<td>8</td>
<td></td>
</tr>

xsl:for-each sample:

xml: <InvoiceAdj AdjType="CS" AdjNumType="BP">

1. <xsl:for-each select="InvoiceAdj">

2. <xsl:for-each select="InvoiceAdj[@AdjType='CS']">

3. <xsl:for-each select="InvoiceAdj[@AdjType='CS' and @AdjNumType = 'BP']">

Legal filter operators are:

1. =  (equal)
2. != (not equal)
3. &lt; less than
4. &gt; greater than

XSLT Examples

• Increment variable in for-each loop: No Can Do
  
• Search by Attribute and print RANum and position()
• Search by Element and print RANum and position()
• 
  

Select between dates in Oracle SQL

SQL> with t as (select to_date('01/01/2007','dd/mm/yyyy') as update_date from dual union all
 2             select to_date('01/01/2007','dd/mm/yyyy') from dual union all
 3             select to_date('03/01/2007','dd/mm/yyyy') from dual union all
 4             select to_date('03/01/2007','dd/mm/yyyy') from dual union all
 5             select to_date('03/01/2007','dd/mm/yyyy') from dual union all
 6             select to_date('04/01/2007','dd/mm/yyyy') from dual union all
 7             select to_date('05/01/2007','dd/mm/yyyy') from dual union all
 8             select to_date('07/01/2007','dd/mm/yyyy') from dual union all
 9             select to_date('07/01/2007','dd/mm/yyyy') from dual)
10  -- end of test data
11  select to_char(x.UPDATE_DATE, 'YYYY-MM-DD') as update_date, DECODE(t.update_date, NULL, 0, count (*)) as count
12  from (select to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')+(rownum-1) as update_date
13        from   dual
14        connect by rownum <= (to_date('2007-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') -
15                              to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) x
16        LEFT OUTER JOIN t ON (x.update_date = t.update_date)
17  where x.UPDATE_DATE >= to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
18  and x.UPDATE_DATE < color="navy">'2007-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
19  group by to_char(x.UPDATE_DATE, 'YYYY-MM-DD'), t.update_date
20  order by 1
21  /

UPDATE_DAT      COUNT
---------- ----------
2007-01-01          2
2007-01-02          0
2007-01-03          3
2007-01-04          1
2007-01-05          1

Oracle Triggers

CREATE OR REPLACE TRIGGER EBP.INSERT_EBP_TRADING_PARTNERS
BEFORE INSERT
ON EBP.EBP_TRADING_PARTNERS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
v_fax_subject_line VARCHAR2(60);
BEGIN
tmpVar := 0;

SELECT SUBJECT_LINE INTO v_fax_subject_line FROM EBP_BILLERS WHERE BILLER_ID = :NEW.BILLER_ID;

IF :NEW.FAX_SUBJECT IS NULL THEN
:NEW.FAX_SUBJECT := v_fax_subject_line;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Warning: The Biller_Id does not exist in EBP_BILLERS table.');

END ;
/

CONCAT Strings in Oracle v.s. MySQL

Oracle uses the CONCAT(string1, string2) function or the || operator. The Oracle CONCAT function can only take two strings so the above example would not be possible as there are three strings to be joined (FirstName, ' ' and LastName). To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server / Access.

-- Oracle
SELECT FirstName || ' ' || LastName As FullName FROM Customers

MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Customers

文昌帝君陰騭文

文昌帝君陰騭文

帝 君曰。吾一十七世為士大夫身。未嘗虐民酷吏。救人之難。濟人之急。憫人之孤。容人之過。廣行陰騭。上格蒼穹。人能如我存心。天必錫汝以福。於是訓於人曰。 昔于公治獄。大興駟馬之門。竇氏濟人。高折五枝之桂。救蟻中狀元之選。埋蛇享宰相之榮。欲廣福田。須憑心地。行時時之方便。作種種之陰功。利物利人。修善 修福。正直代天行化。慈祥為國救民。忠主孝親。敬兄信友。或奉真朝斗。或拜佛念經。報答四恩。廣行三教。濟急如濟涸轍之魚。救危如救密羅之雀。矜弧恤寡。 敬老憐貧。措衣食周道路之饑寒。施棺槨免屍骸之暴露。家富提攜親戚。歲饑賑濟鄰朋。斗稱須要公平。不可輕出重入。奴婢待之寬恕。豈宜備責苛求。印造經文。 創修寺院。捨藥材以拯疾苦。施茶水以解渴煩。或買物而放生。或持齋而戒殺。舉步常看蟲蟻。禁火莫燒山林。點夜燈以照人行。造河船以濟人渡。勿登山而網禽 鳥。勿臨水而毒魚蝦。勿宰耕牛。勿棄字紙。勿謀人之財產。勿妒人之技能。勿淫人之妻女。勿唆人之爭訟。勿壞人之名利。勿破人之婚姻。勿因私讎。使人兄弟不 和。勿因小利。使人父子不睦。勿倚權勢而辱善良。勿恃富豪而欺窮困。善人則親近之。助德行於身心。惡人則遠避之。杜災秧於眉睫。常須隱惡揚善。不可口是心 非。剪礙道之荊榛。除當途之瓦石。修數百年崎嶇之路。造千萬人來往之橋。垂訓以格人非。捐貲以成人美。作事須循天理。出言要順人心。見先哲於羹牆。慎獨知 於衾影。諸惡莫作。眾善奉行。永無惡曜加臨。常有吉神擁護。近報則在自己。遠報則在兒孫。百福駢臻。千祥雲集。豈不從陰騭中得來者哉。