SQL 語法將群組的資料全部列出來

我們都知道 GROUP 可以用 AVG、SUM、COUNT、MIN、MAX…等，

可以做數字、日期或是字串的運算。

但是如果群組不想要運算，要合起來全部都列出來呢 ?

通常我們就用程式跑迴圈的方式來將資料連接成一個字串。

我們知道 Oracle 有一個函數叫 concat 也就是將二個字串連接在一起，

這時候也可以用在 group 囉~~

介紹一個函數 wm_concat ，但重複資料不會排除，所以再加上 distinct 就更完美了。
欄位內容會以逗號來分隔。
不想用逗號來分隔的話，就只能用 replace 的方式來取代了。

範例：顯示部門內的所有員工資料。

SELECT gen03,wm_concat(gen01) FROM gen_file

GROUP BY gen03

ORDER BY 1

要注意，wm_concat 的欄位是以 CLOB 格式來呈現的，
所以如果有建立 view 或是直接在 p_query 使用的話，記得要轉成 VARCHAR 的格式。
原因就是 p_query 所有欄位都是依照 gaq_file.gaq03 欄位來宣告的。
用 CAST 將 CLOB 改為 VARCHAR 的格式。
範例：

SELECT gen03,cast(wm_concat(gen01) as varchar(255)) FROM gen_file

GROUP BY gen03

ORDER BY 1

另一個需注意就是 wm_concat 的資料是不能排序，
所以有可能資料所列出來的順序會不一樣。必需改為先 wm_concat 合併再 GROUP來處理。
範例：

SELECT gen03,max(cast(gen01 as varchar(255))) FROM (
SELECT gen03,wm_concat(gen01) OVER (PARTITION BY gen03 ORDER BY gen03,gen01) gen01 FROM gen_file
)
GROUP BY gen03

當站下線的成本該如何計算?

當站下線的意思就是，工單生產的途中要進行在製品的入庫。
可能是要抽原料來變更生產料號，可能是設計變更換新料號生產…情況。
因已經在製程中生產了，用挪料、退料的方式也是可行，
但所投入的人工、製費在成本計算，就會造成舊工單結案變調整金額，
新工單的在製金額也會低估，就不會是那麼的恰當。
需要進行在製成本的調整，料件的單位成本才能夠準確。
所以當站下線的料號就必須要再編一個新的料號，一般都是工單生產的料號+製程碼。

我們瞭解了當站下線的意思，報工有當站下線時製程移轉和 WIP 量是不會有問題的，
比較有問題的就是工單是不會自動結案，
另一個就是下線的庫存單位必須要和工單製程的轉出單位要一樣。
但是成本計算時在製金額是如何表示的呢?

在 TIPTOP 系統當站下線是以退料的方式來呈現的，
也就是在製的本期投入的數量和金額為負數，
和正常完工入庫是在轉出數量和金額概念是不同的。
再加上成本計算時沒辦法知道當站下線的金額是多少，所以我們就要附予單價啦~~

當站下線在成本計算前要先在 axct510 先填入材料、人工、製費、加工的單價，
然後要注意一點，如果是同月當站下線所入庫當月又被另一工單領用接下去生產的時候，
則需要在 axct001 庫存開帳補上此料件的前期單價資料，避免領用當線下線的料號單價為 0 ，
因 axcp500 執行的先後順序的關係，當站下站是退料並非入庫而計算月加權平均，
所以工單領用的單價就抓不到的關係。

最後成本計算後檢查相關資料時，
當站下線和拆件工單在 axcr370 成本計算後勾稽報表都會出現本期投入金額為負的錯誤訊息，
就把他當作是正常吧~~~

開啟 GDC 的 Debug 模式

找系統問題的時候，需要找畫面的欄位對應到資料庫那一個 Column、

action 按鈕對應是 4gl 程式那一個 action 名稱…等，這些除錯常常會遇到。

GDC 有提供一個 Debug 的功能，可以直接看到目前視窗所有欄位或按鈕的變數值 or 名稱。

1. 在程式集的捷徑加上參數 -aD (-M 是最小化開啟)。

GDC 就會多出二個按鈕。

2. 使用 ssh 或是 telnet 的方式來登入 TIPTOP，開啟你所要的畫面。

3. 將滑鼠游標移到你要看的上方，然後按下組合鍵 Ctrl + 滑鼠右鍵，就出現 Debug 視窗。

    然後你所點選的地方也會有紅色的標示框在那閃動著，就可以快速知道 action 的名稱。

    也試試看看其他欄位，可以顯示欄位的變數值喔~

讓 TIPTOP 下拉式選單可以動態的顯示

在 Genero Studio 設計 ComboBox 下拉式選單的時候，
通常都是直接在 4fd 檔上面定義固定的 item 項目。
然後再到 p_perlang 修改多國語言顯示的名稱。

提供一個方式可以讓 ComboBox 物件可以隨著其他欄位的變化，來改變下拉式選單的項目。
就是用 ui.ComboBox 的方法。

參考 Genero 的說明文件，ui.ComboBox 提供下式的 Function。
CLASS Method：
1. forname ：物件名稱

2. setDefaultInitializer：設定初始的項目

OBJECT Method：
1. addItem(name,text) ：增加項目，name 是值，text 是顯示名稱

2. getColumnName()：回傳欄位的名稱

3. getIndexOf(name)：回傳 name 是在項目的第幾個，沒有回傳 0

4. getItemCount()：回傳項目的數量

5. getItemName(index)：回傳第 index 個的項目 name 值

6. getItemText(index)：回傳第 index 個的項目 text 值

7. getTableName()：回傳欄位的 table_name 或是 FORMONLY

8. getTag()：回傳欄位的 tag 值

9. getTextOf(name) ：回傳 name 是在項目的第幾個的 text 值，沒有回傳 null

10. removeItem(name)：刪除名稱是 name 的項目

11. clear()：清除所有的項目

範例1：ComboBox 依開啟時可以顯示 1~5 的項目選單
方法1：
1. 在 4fd 檔中的 ComboBox 欄位 Initializer 屬性加上 cb_load 字串

2. 在 4gl 檔中增加一個 Function
    FUNCTION cb_load(combox)
         DEFINE combox ui.ComboBox
         LET combox = ui.ComboBox.forname("ima12")
         CALL combox.clear()
         FOR g_num = 1 TO 5
             CALL combox.addItem(g_num ,g_num)
         END FOR
    END FUNCTION

方法2：
1. 在 OPEN WINDOW 之前加上：
     CALL ui.ComboBox.setDefaultInitializer("cb_load")

2. 同上增加 cb_load 的 Function

範例2：ComboBox 可以隨著某個欄位所選擇的，列出對應的選單
 1. 新增 cb_load 的 Function
    FUNCTION cb_load(combox)
         DEFINE combox ui.ComboBox
         DEFINE l_azf01   LIKE  azf_file.azf01,
                       l_azf03    LIKE azf_file.azf03
         LET combox = ui.ComboBox.forname("ima12")
         DECLARE ima12 CURSOR FOR
            SELECT azf01,azf03 FROM azf_file
                WHERE azf02 = g_ima.ima12
         FOREACH ima12  INTO l_azf01,l_azf03
                 CALL combox.addItem( l_azf01,l_azf03)
         END FOREACH
    END FUNCTION

2. 然後再 INPUT 段的 BEFORE FIELD 欄位的時候，呼叫就可以變化了
    BEFORE FIELD ima12
           CALL cb_load("")

當欄位 ima12 選擇是那一個的時候，跳到 ComboxBox 欄位的時候，
就會帶出不同的下拉式選單

總結：在 Genero 的 ComboBox 有一些可惜的地方，只能在查詢的時候才能夠輸入資料
(要開啟 queryEditabile )，不能提供新增的時候可以輸入選單以外的值。
再來就是如果是用此方式的話，多國語言的功能就不能再維護了，
如果是帶 Table 的資料多國語言就沒問題，但是是自定的話，當然也可以用 p_ze 來定義啦~~~
要注意的另一點，如果下拉式選單因其他欄位的變化而造成值是相同但是顯示的名稱不同，
再查詢的時候就要再多判斷要帶出來的顯示名稱，並上下筆做查詢的時候也要判斷。

Genero 的檔案管理指令

需要檔案管理的時候，都要再查一次 Genero 的文件，
所以就把常用的指令都整理一下啦~~

其實就是可以用 Genero 進行檔案或資料夾的新增、修改、刪除，或是權限設定的功能。
有時候報表的資料太多，或是其他系統做資料交換的時候，就可以轉文字檔的方式來匯出。

首先要記得在程式最上方加上 Library：
IMPORT os

再來利用 CALL Function RETURNING value 的方式來使用。
以下就是常用的 Function：

1. os.Path.separator() ：路徑的系統符號，傳回 / 或是 \ 符號

2. ps.Path.pathseparator() ：磁碟的系統符號，傳回  : 或是 ; 的符號

3. os.Path.exists(filename) ：傳回 true 或是 false

4. os.Path.basename(Fullname) ：去除路徑只回傳檔案名稱

5. os.Path.dirname(Fullname) ：去除檔案名稱只回傳路徑

6. os.Path.rootname(filename)：回傳路徑+檔案名稱

7.  os.Path.extension(filename)：回傳檔案的 extension

8.  os.Path.join ( "/u1", "topprod") ：回傳 /u1/topprod

9.  os.Path.chrwx(file,511) ：變更檔案的權限，回傳 true 或是 false，十進位的 511 = 八進位的 777

當然也可以不要用 os 的 Library ，大多的指令也是直接也可用 RUN 的指令來達成。

例：
LET l_cmd = "chmod 777 ", filename
RUN l_cmd

完整複製和還原 Oracle 的 Instance 資料

要將 Oracle 資料庫要複製到新的機器，或是將正式區的資料複製到測試區，
當然可以用 rman 的備份還原方式，或是用 impdp/expdp、imp/exp …等方式。
好處就是資料會重新整理過，資料就會連續性的匯入，效能會好一點。

不過也有更快速的方式，就是直接將 dbf 檔案複製過去，然後再重建 control file ，
好處就是速度快，也不用再重新設定，而且可以修改 instance 名稱。
但是在異機做還原時，只能限定相同的 Oracle 版本和版次才行。

以下範例就是將 TOPPROD 完整的資料複製到 TOPTEST 。

1. 依照  topprod 的 control file 的參數產生 trace 檔案：
    切換到 topprod： export ORACLE_SID=topprod
    使用 sys 使用登入  sqlplus sys/sys_dsc as sysdba
    執行：SQL> alter database backup controlfile to trace;
     再來就是看檔案產生的位置：
select c.value || '/' || d.instance_name || '_ora_' || to_char(a.spid,'fm99999') || '.trc' from v$process a, v$session b, v$parameter c, v$instance d where a.addr = b.paddr and b.audsid = userenv('sessionid') and c.name = 'user_dump_dest';

出現：/u2/oracle/diag/rdbms/topprod/topprod/trace/topprod_ora_28140.trc

2. 停止 topprod 資料庫，並複製檔案到 toptest
    關閉資料庫：shutdown immediate
    複製檔案：rsync /u2/oracle/oradata/topprod/* /u2/oracle/oradata/toptest
    先刪除 control01.ctl、control02.ctl …檔案，之後會再重建。

3.  建立新的 inittoptest.ora 參數檔案 (檔名必需是 init[資料庫名].ora ，且必須放在 dbs 目錄)
     此作業是要建立一個新的 Instance ，如果已經有建立就可以不用 3、4、5 的步驟。
     進入到 sqlplus 並執行：
     create pfile='/u2/oracle/product/11.2.0/dbhome_1/dbs/inittoptest.ora' from spfile; 

4. 修改 initoptest.ora 檔案，將所有的  topprod 都改為 toptest。

5. 切換到  toptest 資料庫，建立 spfile 資料，並啟動資料庫。

    到 toptest 資料庫：export ORACLE_SID=toptest

    進入 sqlplus 。

    執行：SQL> create spfile from pfile;

    使用 nomount 方式啟動資料庫：startup nomount 

    查看 instance name ：select instance_name from v$instance;

6. 複製 trace 檔案並修改為 sql 指令，再執行。

    修改 trc 檔：vi /u2/oracle/diag/rdbms/topprod/topprod/trace/topprod_ora_28140.trc

    修改 SET #1 的部份，並REUSE 改為SET，其餘全部刪除，只剩下如下所示：

 STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TOPTEST" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u2/oracle/oradata/toptest/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u2/oracle/oradata/toptest/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u2/oracle/oradata/toptest/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u2/oracle/oradata/toptest/system01.dbf',

  '/u2/oracle/oradata/toptest/sysaux01.dbf',

  '/u2/oracle/oradata/toptest/undotbs01.dbf',

  '/u2/oracle/oradata/toptest/users01.dbf',

  '/u2/oracle/oradata/toptest/dbs1-01.dbf',

  '/u2/oracle/oradata/toptest/dbs1-02.dbf',

  '/u2/oracle/oradata/toptest/temptabs.dbf',

  '/u2/oracle/oradata/toptest/rptdbs1-01.dbf'

CHARACTER SET AL32UTF8

   執行 trace 的 SQL 檔就會產生 control file： SQL>@/u2/oracle/trace.sql

7. 開啟資料庫。
    執行：SQL> alter database open resetlogs;  

    如果執行失敗的話，出現ORA-01194: file 1 needs more recovery to be consistent，
    就是日誌的問題，解決方式：
    執行： recover database using backup controlfile until cancel;
    出現 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    再輸入：/u2/oracle/oradata/toptest/system01.dbf

8. 再把 temporary 的 tablespace 重建，先建一個  TEMP1 然後再移轉再刪除即可。
    先建立：CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  '/u2/oracle/oradata/toptest/temp02.dbf' SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE   UNLIMITED
   TABLESPACE GROUP ''
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

  再切換為預設：alter database default temporary tablespace temp2;
  再刪除：DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

9. 完成，查看 datafile 。
    執行：select file_name,file_id,tablespace_name,status from dba_data_files;

10. 如果是複製到新的機器上或是新的 Instance 名稱，就要建立 toptest 資料庫的密碼檔案。     
      執行：orapwd file=orapwtopptest password=oracle entries=30

11. 新的機器上就要再設定監聽 listener.ora 和連線 tnsnames.ora的檔案，
      然後再重新啟動 Listener： lsnrctl reload listener 。

備援機制： TIPTOP 主機虛擬化

公司的 ERP 系統能夠允許中斷多久呢 ?
有沒有想過當 TIPTOP 主機掛了該怎麼辦，
中斷的時間的長短其實就取決在能夠花費多少金額。
最好就是 HA 再加上異地備援啦，當然也是所費不貲囉~~

在有限的資源下當然就是將 TIPTOP 主機虛擬化是最經濟的作法。
當 ERP 系統發生無法立即排除的情況時，
就可以馬上啟動虛擬的 TIPTOP 主機，再還原備份的檔案和資料庫，
就足以應付急迫的公司作業。

再來就是說明如何將目前的 TIPTOP 主機做線上遷移到虛擬主機，
Linux 的好處就是所有的設定都是文字檔，不像 windows 還有 register 註冊資料庫 。
所以只要檔案複製過去就可以使用了。
要先切換到 root 的使用者。

1. 把 TIPTOP 主機的資料 image 到檔案，先用 df -h 查看 partition 的空間大小，
    或是佔用的空間有多少。

2. 將磁碟、partition 或是建立空的映象檔做 image，以下二選一即可 。
    A.假設將 sda1 的 partition 做成 sda.img 映象檔，執行 dd if=/dev/sha1 of=/u2/sda1.img 。
        (此指令是破壞性磁碟指令，需注意。如果 sda1 為 100G，就會建立 100G 的檔案)
    B.或是建立空的 image 檔，再做資料同步複製也可以。
       建立空的 20 G 大小的 image 檔： dd if=/dev/zero of=/u2/sda1.img bs=1G count=20。
       格式化成 ext3 格式：mkfs.ext3 sda1.img。
       掛載到 \mnt 目錄： mount /u2/sda1.img /mnt。
       同步複製： rsync -av /u1/* /mnt。

3.  將檔案傳到虛擬主機上，然後轉換成 vhd 檔，利用 vhdtool.exe (需下載)做 convert。
     指令：   vhdtool /convert sda1.vhd。

4.  建立新的 guest virtual machine ， 建立傳統網路卡需綁網卡的 mac ，並掛載 vhd 檔。

5.  開機，可以開啟喔~~~完成一半了。

6.  再來就是修改設定檔了。我會做下列的修改。
     修改登入是 text mode： vi /etc/init.tab 的 id:5:initdefault: 將 5 改為 3。
     設定網卡： vi /etc/sysconfig/network-scripts/ifcfg-eth0
      (應該會有一個 bak 檔，直接複製就好 cp ifcfg-eth0.bak ifcfg-eth0 )
     修改開機掛載的磁碟，有可能原本是 sda1 變成 hda1，先用 fdisk -l 查看 。
     設定開機自動掛載： vi /etc/fstab，將 sda 改為 hda。

7.   如果虛擬主機的記憶體設定太小，Oracle 有可能會開不起來，
      因為 SGA 設定必須在 tmpfs (ram disk) 以內，
      查看 tmpfs 空間： df -h 。
      到原本的 TIPTOP 主機查看 Global SGA memory ，
      進入 sqlplus 執行 show parameter mem，查看 memory_target 和 memory_max_target。
      然後再修改 tmpfs 的大小，執行： vi /etc/fatab ，在tmpfs 加上 size=4G。

8. 重新啟動，完成。記得千萬要隔離二台主機的網路，不然 mac 位置一樣會造成異常。

9. 平時就保持 Standby 的狀態，有問題時，將備份的程式直接蓋過去，就不多說明了。

10. 再來就是將 DB 也直接 import 進去，備援主機就完成。

如果能夠將資料庫獨立出去是最好了，還原就快多了，
平常第二台 Oracle 主機待機，利用第一台 Oracle 主機的 redo log 或 archive log 做資料的同步。
資料有損失的時間點就可以大幅的減少。
只需要修改 oracle 的連線檔：/u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
就可以切換到第二台 Oracle 主機做存取。

如果想要把測試區和標準區的資料庫刪除來節省磁碟空間和記憶體空間的，
在 Oracle 10g 以後就有提供 drop database 的指令，
以 sys 身份用 sysdba 的方式來登入 sqlplus，
sqlplus sys/sys as sysdba
alter database close;        關閉 database
alter system enable restricted session;        改為 restrict 模式
drop database;        刪除資料庫
刪除資料庫會連同 dbf 檔和 control file、redo file 全部刪除。

因為是 RHEL 5.5 版架設在 Hyper-V 上，所以需安裝 Linux Integration Services v2.1。
# mkdir /media/cdrom
# mount /dev/cdrom /media/cdrom
# mkdir /opt/linux_ic_v21_rtm
# cp -R /media/cdrom/* /opt/linux_ic_v21_rtm
# cd /opt/linux_ic_v21_rtm
# make
# make install
# reboot
下載 adjtimex RPM 套件確保時間的準確性
# rpm -ivh /media/cdrom/Server/adjtimex-1.20-2.1.x86_64.rpm

因為是 P2V 所以需要將 /boot/grub/grub.conf 的 hda=noprobe hdb= norpobe 去掉，不然會無法開機。
然後再放 RHEL 5.5 或是 CentOS 5.5 的安裝光碟重新編譯 Kernel。
1. 使用安裝光碟開機
2. 進入救援模式，輸入 linux rescue
3. chroot /mnt/sysimage 指令將目前的檔案系統環境，由光碟掛載點切換回虛擬主機的硬碟掛載點
4. cd /boot 指令切換目錄
5. ls /lib/modules」指令或「cat /etc/grub.conf」指令查看檔案內容來得知Linux核心版本
6. mkinitrd –v –f initrd-2.6.18-194.el15.img 2.6.18-194.el15
7. 重新開機

取消螢幕保護
修改 /etc/rc.d/rc.local 加入 setterm -powersave off -blank 0

備註：如果想要修改 IP ，需修改以下的檔案：
/etc/hosts
/etc/sysconfig/network-scripts/ifcfg-eth0
/etc/sysconfig/network
/etc/resolv.conf
/u1/genero/fgl/etc/fglprofile
/u1/genero/fgl.ws/etc/fglprofile
/u1/genero/fgl.dev/etc/fglprofile
/u1/topprod/tiptop/bin/tiptop_env
/u1/usr/tiptop/.rhosts
license.sh 或是修改 cl_user.4gl

其他 License 相關問題就不在此說明了。

常用的 PL/SQL 函數備忘

常用的 PL/SQL 函數備忘，以免日後要用到的時候要再花時間查。
後續有常用的 SQL 函數的話會再補充。

ABS(數值) ：取絕對值

CIEL(數值) ：無條件進位

FLOOR(數值) ：無條件捨去

MOD(數值1,數值2) ：取餘數

POWER(數值1,數值2) ：次方

ROUND(數值,小數位) ：四捨五入

SIGN(數值) ：判斷正負值，正 = 1 ，負 = -1，零 = 0

SQRT(數值) ：平方根

LPAD(字串,長度,字元) ：向左補字元

RPAD(字串,長度,字元) ：向右補字元

LTRIM(字串,字元) ：去除最左邊的連續字元或空白

RTRIM(字串,字元) ：去除最右邊的連續字元或空白

LOWER(字串) ：轉小寫

UPPER(字串) ：轉大寫

REPLACE(字串1,字串2,字串3) ：取代字串1裡面有內容是字串2，改為字串3

SUBSTR(字串,開始,第幾個) ：取字串的部份內容

INSTR(字串1,字串2) ：找出字串1裡面有字串2的第一次出現位置

GREATEST(數值1,數值2) ：取二數的最大值，有時候還蠻好用的

LEAST(數值1,數值2) ：取二數的最小值，有時候還蠻好用的

常用的 Genero 指令備忘

一些常用的 Genero 函數和指令備忘，避免找的時候又花了不少的時間。

Genero 函數：

ARG_VAL(數值) ：呼叫其他程式時的參數值：參數1、參數2…

NUM_ARGS( ) ：回傳總共有多少個參數

ARR_CURR() ：目前畫面單身所選擇第 n 筆資料。

ARR_COUNT() ：目前畫面單身的全部筆數。

SCR_LINE() ：目前在畫面的第幾行，但資料超過單身的資料量要小心，
                          改用 ARR_CURR() 會比較好

SET_COUNT(數值) ：設定有多少的陣列要顯示或是輸入

DOWNSHIFT(字串) ：字串轉為小寫字母

UPSHIFT( 字串) ：字串轉為大寫字母

FGL_LASTKEY() ：最近的鍵盤輸入鍵值的 ASCII 值

FGL_KEYVAL(字元) ：字元改為 ASCII

GET_FLDBUF(欄位名) ：抓取 CONSTRUCT 所輸入的欄位內容

FIELD_TOUCHED(欄位名)： 改變了畫面中的欄位時就回傳 TRUE

INFIELD(欄位名) ：在指定的欄位時，回傳 TRUE

FGL_GETENV(字串) ：環境變數值，用在判斷是 GUI 介面或是 EasyFlow 的環境

FGL_DRAWBOX(高, 寬, 線粗細, 邊距, [顏色] ) ：畫方框，(測試是沒有效果?)
                              顏色：0-白,1-黃,2-黃,3-紅,4-青,5-綠,6-藍,7-黑


Genero 指令：

LENGTH(字串) ：回傳字串的長度值

ASCII + 空白 + 數字 ：將 ASCII 轉換為字元

ORD(字元) ：將字元轉換為 ASCII

CURRENT ：回傳目前的系統日期 + 時間 + 毫秒，例：2012-11-06 12:00:00 500

DATE ：回傳目前的系統日期 (月  日  年) ，例： NOV 6 2012

TODAY ：回傳目前的系統日期 (年 / 月 / 日)，例： 12/11/06

TIME ：回傳目前的系統時間

WEEKDAY(日期) ：回傳是星期幾，星期日是 0

YEAR(日期)： 回傳年度

MONTH(日期)： 回傳月份

DAY(日期)： 回傳月的第幾天

MDY(月,日,年) ：回傳所指定的日期格式

MOD ：二數相除取餘數

** ： A 的 B 次方

LSTR(字串或數值) ：不知道作用，不過數字可以靠左對齊

SFMT(字串,參數…) ：將字串的參數內容，依參數值替換其位置，
                                  範例：DISPLAY SFMT("One = %1, Two = %2.",1,2)

EXTEND( ) ：將日期的格式轉換為完整日期+時間格式，
                    範例：DISPLAY EXTEND ( TODAY, YEAR TO FRACTION(4) )

UNITS ：將日期的格式做運算，範例： DISPLAY TODAY + (1 UNITS MONTH)
                 但是如果是 10/31 要加一個月，會顯示成空白，
                 提供 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 參數配合使用

USING ：格式化數值欄位，例： DISPLAY 1000 USING '#,##&.&&'
                 #  有數字才顯示，不然空白
                 & 有數字顯示，無數字顯示 0
                 * 有數字顯示，無數字顯示 *
                 < 有數字顯示，無數字為 NULL
                 -  有數字顯示，正數為空白，負數顯示 -
                 + 有數字顯示，正數為 +，負數顯示 -
                 $ 有數字顯示，靠右第一個顯示 $ ，其餘向左空白
                 ( )  有數字顯示，負數才顯示 (數值)

CLIPPED ：去除字串結尾空白，開頭的空白是不去除的

SPACES ：空幾格，例： DISPLAY 10 SPACES

SQLERRMESSAGE ：顯示資料庫的 SQL 執行的訊息，正確顯示 NULL

SQLSTATE ：顯示 SQL 執行的訊息，正確顯示 NULL

字串和數值的處理： + 加，- 減，* 乘，/ 除
                                    (逗號) 字串連接 (數字會前方補空白)，|| 字串連接 (數字沒有空白)

字串連接：, (逗號) 數字會有空白 或 || (Pipe) 數字不會有空白

算數運算：+-*/ 就不多說了

邏輯判斷：AND、OR、NOT 也不多說了

其他配合 OUTPUT 列印的指令就不再多加介紹：
PAGENO
LINENO
WORKWARP
COLUMN


詳細的內容請參考 4js 的網站

比對二個 Table 的資料, 並進行資料更新

系統做資料同步，新的資料就用 Insert ，原本的資料就用 Update 指令，
當然也可以全部資料 delete 掉再全部重新 Insert 進去，
從 Oracle 10g 的版本開始就提供了一個 Marge 的指令，可以解決很多資料比對上的問題。
Merge 指令的缺點就是，只能二個 Table 進行資料異動。
優點是使用 Update 更新時要注意找不到資料會更新為 Null 的情況，用 Merge 可以避免。

例如：TableA 要依據 TableB 的欄位更新過來，一般都會寫：
            update TableA set (ColumnA2,ColumnA3) =
             (select ColumnB2,ColumnB3 from TableB where ColumnB1 = ColumnA1)
            但是如果要再增加判斷 TableB 的 ColumnB2 不是空的才更新，這樣要改成：
            update TableA set (ColumnA2,ColumnA3) =
             (select nvl(ColumnB2,ColumnA1),ColumnB3 from TableB where ColumnB1 = ColumnA1)

有了 Merge 的指令就可以比較有直覺的 SQL 語法了。
merge into TableA a  a using TableB b  on (a.ColumnA1= b.ColumnB1)
when matched then
   update set a.ColumnA2= b.ColumnB2, a.ColumnA3 = b.ColumnB3 where b.ColumnB2 is not null
when not matched then
   insert (ColumnA1,ColumnA2,ColumnA3) values (ColumnB1,ColumnB2,ColumnB3)

Marge 指令要如何變化不彷就研究試試看吧~~

將 CONSTRUCT 所輸入的欄位拆開來

通常報表要判斷多個欄位的時候，
都是用 CONSTRUCT BY NAME tm.wc ON 欄位1，欄位2，欄位3…
有時候欄位1要另外判斷，欄位2又要另外判斷的時候怎麼辦，
這樣 tm.wc 已經組好 SQL 的語法，總不能用字串去拆吧，可能會有錯誤的情況。
當然你也可以分寫開三次 CONSTRUCT ，只是使用者要按三次確定才行。

山不轉路轉，就變化一下吧~~
這時候 GET_FLDBUF 的指令就可以派上用場了。
只是抓出來的卻是使用者輸入查詢的字串，SQL 根本不能拿來用。

只在原本的 CONSTRUCT 加上：
  AFTER CONSTRUCT
          LET tm.wc1 = GET_FLDBUF(imd01)
          LET tm.wc2 = GET_FLDBUF(sfa01)
          LET tm.wc3 = GET_FLDBUF(sfa03)

再來就是增加三個 CONSTRUCT，這樣就分別對應到 tm.wc1, tm.wc2, tm.wc3 。
之後的 SQL 就只要抓 wc1,wc2,wc3 的值就好了。

      CONSTRUCT BY NAME tm.wc1 ON imd01
         BEFORE CONSTRUCT
            DISPLAY tm.wc1 TO FORMONLY.imd01
            EXIT CONSTRUCT
      END CONSTRUCT
     
      CONSTRUCT BY NAME tm.wc2 ON sfa01
         BEFORE CONSTRUCT
            DISPLAY tm.wc2 TO FORMONLY.sfa01
            EXIT CONSTRUCT
      END CONSTRUCT

      CONSTRUCT BY NAME tm.wc3 ON sfa03
         BEFORE CONSTRUCT
            DISPLAY tm.wc3 TO FORMONLY.sfa03
            EXIT CONSTRUCT
      END CONSTRUCT

雖然是很白痴的用法，不過至少也是可以解決不少特殊報表的需求。

使用 COM 方式實現 Excel 資料匯入匯出的功能

對使用者來說，在 Excel 整理大量的資料還是比較方便又快速的，
傳統 TIPTOP 的匯入 Excel 資料的方式都是需要先轉為文字檔，
利用分隔符號來區分資料的欄位，但是這樣已是古代的作業方式了。

通常應用程式開發工具可以使用 COM 的方式是跟 Excel Application 進行通訊，
GDC 是 Clinet 端的軟體，所以可以使用 COM 的方式來讀取 Excel 的資料，
參考 4js 提供的 Genero 使用說明，找到 ui,interface.frontcall() 的使用方式。

建立 COM
1:CALL ui.interface.frontcall("WinCOM","CreateInstance",[program],[handle]
  A. program - 系統中註冊的 COM 名稱
  B. handle - 回傳狀態 -1 表示有錯誤，此值在 API 可以使用

使用指定的方法
2:CALL ui.interface.frontcall("WinCOM","CallMethod",[handle,method,arg1,...],[result])
  A.handle - 使用宣告的 handle 值。
  B. method - 函數的名稱。
  C. arg1 - 傳送給方法用的參數
  D. result - 回傳狀態 -1 表示有錯誤，0 表示沒有錯誤

讀取屬性值
3.CALL ui.interface.frontcall("WinCOM","GetProperty",[Handle,member],[result]
  A. handle - 使用宣告的 handle 值
  B. member - 取得屬性的
  C. result - 回傳狀態 -1 表示有錯誤，0 表示沒有錯誤

設定屬性值
4.CALL ui.interface.frontcall("WinCOM","SetProperty",[handle,member,value],[result]
  A. handle - 使用宣告的 handle 值
  B. member - 設定屬性的名稱
  C. value - 屬性的值。
  D. result - 回傳狀態 -1 表示有錯誤，0 表示沒有錯誤

錯誤訊息內容
5.CALL ui.interface.frontcall("WinCOM","GetError",[],[result]
  A. result - 錯誤的說明，如果沒有錯誤就為 Null

關閉 COM
6.CALL ui.interface.frontcall("WinCOM","ReleaseInstance",[handle],[result])
  A. handle - 使用宣告的 handle 值
  B. result - 回傳狀態 -1 表示有錯誤，0 表示沒有錯誤

以下就為 4GL 程式 Excel 資料匯入的範例：
DEFINE l_excelapp INTEGER,
               l_excelwb INTEGER,
               l_result INTEGER,
               l_str STRING,
               l_filename STRING,
               l_target STRING,
               l_range STRING

DEFINE l_i LIKE type_file.num5,
               l_j LIKE type_file.num5,
               l_column LIKE type_file.chr10

LET l_excelapp = -1
LET l_excelwb = -1

# 選擇檔案的位置和檔案名稱
LET l_filename = cl_browse_file()
# 建立 Excel Application 的 COM
CALL ui.interface.frontcall("WinCOM","CreateInstance",["Excel.Application"],[l_excelapp])
# 開啟所選擇的 Excel 檔案
CALL ui.interface.frontcall("WinCOM","CallMethod",[l_excelapp,"WorkBooks.Open",l_filename],[l_excelwb])
# 設定 Excel 要顯示
CALL ui.interface.frontcall("WinCOM","SetProperty",[l_excelapp,"Visible",true],[l_result])
# 讀取 Excel 的行數
CALL ui.interface.frontcall("WinCOM","GetProperty",[l_excelwb,'activesheet.UsedRange.Rows.Count'],[l_result])

FOR l_i=1 TO l_result
   LET l_column = l_i
   # 所要讀取的欄位，A 列第 n 行
   LET l_range = 'activesheet.Range("A',l_column,'").Value'
   # 讀取欄位的值
   CALL ui.interface.frontcall("WinCOM","GetProperty",[l_excelwb,l_range],[l_str])
   LET g_bmd[l_i].bmd01 = l_str
   # 所要讀取的欄位，B 列第 n 行
   LET l_range = 'activesheet.Range("B',l_column,'").Value'
   # 讀取欄位的值
   CALL ui.interface.frontcall("WinCOM","GetProperty",[l_excelwb,l_range],[l_str])
   LET g_bmd[l_i].bmd02 = l_str
END FOR

# 關閉 Excel 的 檔案和 COM
CALL ui.interface.frontcall("WinCOM","CallMethod",[l_excelapp,"WorkBooks.Close"],[l_excelwb])
CALL ui.interface.frontcall("WinCOM","ReleaseInstance",[l_excelapp],[l_result])

在畫面加上資料清單,從此不必再下一筆查了

用過 TIPTOP 的剛開始最不習慣的就是查詢資料都要下一筆、下一筆的查詢，
不但查詢耗時，而且要匯出 Excel 需要跑報表才可以列出清單，
讓使用者感覺操作不是那麼的流暢和便利。

可以在程式上面加上資料清單囉~~
而且調整的程式的部份不會很多，大約半小時就可以完成一支程式。

以動態料件數量明細查詢 aimq102 為例：

當在主畫面的時候匯出 Excel 可以匯出庫存明細，

在資料清單匯出 Excel 則匯出資料清單資料，且 Double Click 就會跳到主畫面。

在資料清單選擇料件時，下面的庫存明細也會跟著更新。

這樣是不是就方便許多，也不需要另外在做報表程式做明細資料的匯出。

修改程式步驟：

1. 修改畫面檔，增加新的頁籤，並指定 action ，記得 Main 也要加上 action。

2. 再來就是修改程式了，需要增加二個 Function。

    q102_list_fill() 在顯示資料清單用的。

    q102_bp1(p_ud) 在跳到資料清單時等待操作用的，才能停留畫面在資料清單。                                           

                          如果需要能夠在資料清單做過帳、確認的動作，也是加在此 Function。

3. 最後就是一些小調整，讓功能更完整啦，
   可以在主畫面查詢到第幾筆，資料清單也可以移動到那一筆。
   還有匯出 Excel 依畫面在主畫面還是資料清單來匯出到 Excel。
   需要修改 q102_menu()、q102_bp(p_ud)、q102_cs()、全域變數。

不用登出重新載入 .profile, 隨意切換正式或測試環境

常常會遇到一種情況，TIPTOP 要切換到測試環境或是正式環境時，

一般都是離開再重新登入；或是使用 su 再登入一次。

不過還是會有使用上的混亂和麻煩。

其實可以重新 load user profile 讓設定重新載入一次。

以下是範例畫面說明。

1. 如果登入時輸入 1，進入 topprod 的環境。

2. 重新載入 .profile 的檔案，使用 「點」「空格」「.profile 的位置」 就可以了，下圖為範例。

3. 出現為 toptest 代表已經切換成功囉~~ 直接執行 .profile 或是 sh .profile 都是沒有用的，因為子 shell  對當前的 shell 是沒有作用的。

SQL進行全文搜索,找出所有Table相關字的欄位出來

應該有很多人感到非常的不便就是找尋資料庫的所有 Table ，可能是在某個欄位的資料。
TIPTOP 標準不含 synonym 的話也有 3千多個 Table ，總不能一個一個去 SELECT 吧~
另外 5.X 版就開始有所謂的營運中心，也就是 Table 欄位會多了 PLANT、LEGAL 欄位，
也有許多非 PLANT、LEGAL 的欄位記錄著營運中心的資料。
所以複製到測試資料庫時，當測試資料進行"還原"時，會有可能造成還原到正式的資料庫。
EX：測試資料庫在 AP刪除時，會還原正式資料庫的 rvv20 欄位，變成未匹配。

遇到此狀況時測試資料庫就需要好好的"檢視"一番..........

以下是將資料庫進行全文的檢索 SQL 語法。

create table xxxx as select imk01,imk02,imk09 from imk_file where rownum = 0;   --先建一個暫存 xxx 的 Table 存放搜尋出來的值

declare
    str varchar2(1000);
    num number;
    begin
    for i in(select column_name,table_name from user_tab_cols where data_type in('CHAR','VARCHAR','VARCHAR2'))       -- 列出此 OWNER 的所有欄位出來
    loop
      str:='insert into xxxx select '''||i.table_name||''','''||i.column_name||''',count(*) from '||i.table_name||' where '||i.column_name||'='||'''DS1''';              -- 將查詢有DS1的欄位筆數寫入暫存 xxx Table
      execute immediate str ;                                   -- 轉換並執行
      commit;
   end loop;
   end;

接下來只要執行以下的查詢 SQL 指令，就可以知道有多少筆資料存在那些 Table 裡面了。
再進行整批的資料更新，將營運中心改為測試資料庫的營運中心。

select 'update '||imk01||' set '||imk02||'= ''DS1'';',imk09 from xxxx
where imk09 <> 0
order by imk09

隨著資料庫越來越大，當然要把資料量大的 Table 獨立出來更新，不然會造成資料庫 IO 滿載，系統會變很慢，以下是用迴圈的方式更新資料，減少資料大批量更新。

declare nums number;
begin
for i in(select distinct tlf01 from TLF_FILE)
loop
   update TLF_FILE set TLF20 = 'DS1' where TLF01 = i.tlf01;
commit;
end loop;
end;

這樣完全不會影響到正式資料庫的測試資料就完成囉~

讓 TIPTOP Web-GDC 也可以修改字型大小

有些人會認為 TIPTOP 的字怎麼這麼小(預設是9 pt)，是可以讓 GDC 預設字型大小改為更大一點，只要在 gdc.exe 後面加上參數 –a即可，不過如果是 Web ActiveX GDC呢，就不能用參數去改了。

 

有找到如何可以改 GDC 參數的功能。

在 /u1/genero/gas/tpl/fglgdcdefault.html 檔，有以下的參數提供修改。

    function configureGDC() {
      //uncomment following line to enable Admin Mode
      gdc.setAdmin(true);          // 開啟 Option 功能 

      //uncomment following line to enable Debug Mode
      //gdc.setDebug(true);         // 開啟 Debug功能

      //uncomment and modify the following line if you want to change the ping timeout (120 sec by default)
      //gdc.setPingTimeOut(120);   // 開啟 ping time out 功能

       //uncomment and modify the following line if you want to change the proxy (using system settings by default)

      //gdc.setProxy("proxy:3128")  // 開啟 proxy 功能

      // this is to ensure that any popup window will appear in front of the browser

      gdc.setFocus();             // 設為視窗動作中
    }

存檔後重新開啟。

就可以修改文字大小囉~~

讓 Oracle 也可以連結 SQL Server 讀取資料

要如何讓Oracle也可以整合 MS SQL Server 呢? 在Oracle有提供一套工具，10g版本稱之為 Oracle Transparent Gateway，到了11g之後改名為 Oracle Database Gateway，如果有購買Oracle DB的話，官方是可以免費提供使用的，不需額外購買，主要是讓Oracle可以做 DB-Link 。提供了許多異質資料庫的整合，詳細可查閱 Oracle Database Gateway 文件說明檔。

到 Oracle 官方網站下載，然後安裝 Oracle Database Gateway，選擇你所需要連結的資料庫類型。

再來就是預設會幫你建立一個連結設定

然後直接安裝就可以了，要一段時間

再來就是修改二個重要的檔案，首先是在 oracle\product\11.2.0\ts_1\dg4msql\admin，會有一個initdg4msql.ora檔。

修改為

HS_FDS_CONNECT_INFO=127.0.0.1//ERP

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

然後再修改檔案名稱initdg4msql.ora改為自已喜歡的 initerp.ora。

第二個檔案就是修改 oracle\product\11.2.0\tg_1\NETWORK\ADMIN\ 裡面的listener.ora檔。

直接在最下面加上

SID_LIST_LISTENER=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=erp)

         (ORACLE_HOME=D:\Oracle\product\11.2.0\tg_1)

         (PROGRAM=dg4msql)

      )

  )

 之後再重新啟動 LISTENER。

lsnrctl stop (停止)

lsnrctl start (啟動)

這樣就完成了 Oracle Database Gateway 的安裝和設定了。

再來就是第二部份，Oracle主機的部份。

首先就是要先建立連線服務的名稱，修改Oracle主機 oracle\product\11.2.0\dbhome_1\network\admin 的tnsnames.ora檔。

在最底下增加

ERP  =

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))

    (CONNECT_DATA=(SID=erp))

    (HS=OK)

  )

 最後就是建立DB-Link，在SQL PLUS下指令，我試sa帳號好像不能使用，不過還是建議建立一個帳號比較好。

CREATE PUBLIC DATABASE LINK ERP CONNECT TO user IDENTIFIED BY password USING 'erp';

測試看看吧~~

select * from table_name@erp;

有結果啦~~

需要注意 Oracle 的所有 Object 英文是不區分大小寫的，但是 SQL Server 是有大小寫之分，
當執行 SQL 指令時， Oracle 會自動轉換為大寫的方式來執行，
必需要加上雙引號 " 的方式才能夠找到正確的 Table 名稱。
select * from "table_name"@erp;

fglWrt - 查詢 Tiptop 線上使用人數和狀況

要如何查詢目前有誰在 TIPTOP 系統線上呢?
還有目前總共有多少人在用，
當上線人數已滿的話，要如何把沒有在使用的 User 踢掉呢?

我想這個是 Tiptop 系統維護人員有時會很頭大吧!

fglWrt 指令可以做到以上這幾個問題喔~~
以下是 fglWrt 的所有參數：

Usage  : fglWrt [options]
 -l [license]: Installs a license.
 -v|-V       : Shows version information.
 -u          : Checks active users.
 -k key      : Installation key for license validation.
 -d          : Deletes current license.
 -i          : Clears the list of registered user sessions.
 -a opt      : Check or view options, possible options are:
               ps           : Shows processes on this machine.
               env          : Shows current environment.
               cpu          : Shows number of CPU in the computer.
               hostname     : Shows name of this machine.
               info license : Shows license information.
               info stat    : Shows statitics of license server.
               info users   : Shows all registered active users.
               info up      : Shows if license server is up.

查詢目前的線上人數和使用者：
fglWrt -a info users

要踢掉沒有在使用的 User ：
fglWrt -i  

注意：我是直接使用這個指令，把 session 全部清除，正在 key 單的人會跳開，
所以執行前需看有沒有人 lock table 才能執行。
只是清除 server 上 genero 的 session 數而已，
所以不會影響已登入的 User (不會斷線或關閉程式)，
這時就通知比較好的 User 快搶 session 吧~~~

重新建立 TIPTOP 測試區的環境

相信小地方修改，或是日積月累的修改程式，或是變更 Database Schema 時，
正式區和測試區的 4GL 程式和 Database Schema 會有不一樣的情況，
這時要如何快速的把正式區完全的蓋到測試區呢?

首先，把正式區的程式整個目錄 copy 到測試區的目錄，
記得不能全部複製喔~~ 只能複被各模組、共用程式、副程式。

1. \cp -r /u1/topprod/tiptop/a* /u1/toptest/tiptop  (先前一定要加 \ 喔，不然會一直問要不要覆蓋)
2. \cp -r /u1/topprod/tiptop/lib /u1/toptest/tiptop
3. \cp -r /u1/topprod/tiptop/sub /u1/toptest/tiptop
4. \cp -r /u1/topprod/tiptop/qry /u1/toptest/tiptop
5. \cp -r /u1/topprod/tiptop/g* /u1/toptest/tiptop

再來就是把全部客製程式都 copy 過去。

6. \cp -r /u1/topprod/topcust/c* /u1/toptest/topcust

程式的部份，這樣就不需要再重新 rebuild 囉~~整個動作應該是不會超過 5 分鐘。

接下來就是資料庫的部份：

首先把所有的測試資料庫都刪除吧~~ (包含 ds)
在 sqlplus 下執行 drop user ds cascade

還原正式區的 ds 資料庫到測試區的 ds 資料庫。
1. 建立空的 ds 資料庫： createdb ds 1
2. 匯入正式區 ds 備份檔到測試區的 ds DB：
       imp system/manager@toptest fromuser=ds touser=ds ignore = y file=ds.dmp log=ds.log
3. 匯入公司的資料到測試區，步驟同 1~2 。
4. 要記得修改 /u1/genero.run/etc/fglprofile.test 喔~~
    通常 createdb 參數是 1 的話，fglprofile.xxxx 檔是不會修改的。
dbi.database.aaa.source= "toptest"
dbi.database.aaa.username= "aaa"
dbi.database.aaa.password= "aaa"
dbi.database.aaa.schema= "aaa"
dbi.database.aaa.ora.prefetch.rows = 1
5. 完成，記得重新登入 udm7 試試看吧~

如果不行的話，要修改 zx_file 的 zx08 欄位，看預設的營運中心是否和測試資料庫一致。

資料庫要看資料的大小而定，不過應該是1個小時內可以搞定吧~~~

TIPTOP 程式/ Oracle 資料庫備份-集中備份篇

還記得之前有一篇文章：TIPTOP 主機定期備份 Oracle 資料 & TIPTOP 程式, 備份篇 嗎?
相信應該足夠使用了吧，
但還是有不足的部份，需要將 TIPTOP 主機設定 cron job ，然後再 cr 主機再設定排程工作，
實在是不符合統一集中備份的方式。

其實只要安裝 Oracle Client ，就可以將 Oracle 資料 export 到遠端的電腦，
這樣就可以一個指令，就可以完整的將全部的資料都在遠端的電腦上執行囉~~

以下是備份 Oracle 資料的批次檔，先轉換 Database 的字集，會有警告訊息，但還是可以正常匯出資料。

set NLS_LANG=TRADITIONAL CHINESE.AL32UTF8
exp system/sys_dsc@topprod owner=%1 log=d:\backup\tiptop\exp\%1.log file=d:\backup\tiptop\exp\%1.dmp

再來就是用 ftp 軟體去抓取 Tiptop 主機的程式檔案到本機電腦。

最後就是執行 RAR 的批次指令囉，
就可以在專屬的備份主機，主動的抓取 TITPOP 程式和 Oracle 資料庫來備份啦~