[Travel] 2020/09 宜蘭

幾米廣場

棲蘭神木園區

[Travel] 2020/09 台東

琵琶湖

金針山

鐵花村

龍田單車

鹿野高台

[Oracle] [Function] LEAD and LAG

I have a Product table:

If I would like to know PRODUCT_ID = 1000, and find its previous order date and next order date. We can make use of LEAD and LAG function to fulfill this requirement:

SELECT ORDER_DATE, PRODUCT_ID,
       LAG(ORDER_DATE) over (ORDER BY ORDER_DATE) AS PREVIOUS_RECORD,
       LEAD(ORDER_DATE) over (ORDER BY ORDER_DATE) AS NEXT_RECORD
FROM PRODUCT
WHERE PRODUCT_ID = 1000;

[Oracle] [Function] LAST_DAY

If today is 2020/09/05, you can using the following SQL statement to get the end day of this month:

SELECT to_char(last_day(to_date('20200905', 'YYYYMMDD')), 'YYYYMMDD') as last_day
FROM DUAL
;

 

If I would like to get the days left from 2020/09/05 in this month:

SELECT (last_day(to_date('20200905', 'YYYYMMDD')) - to_date('20200905', 'YYYYMMDD')) as Days_Left
FROM DUAL
;

Reference

[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions072.htm

[Google Sheet] How to download Google Sheet via command line as Excel file?

Requirement

How to download Google Sheet via command line as Excel file into specific directory?

How-To

In Windows:

powershell -command "& { (New-Object Net.WebClient).DownloadFile('https://docs.google.com/spreadsheets/d/xxxxx/export?format=xlsx', 'src/main/resources/source/batchConfigFile.xlsx') }"

In Mac OS:

curl -L "https://docs.google.com/spreadsheets/d/xxxxx/export?format=xlsx" > src/main/resources/source/batchConfigFile.xlsx

[Oracle] How to reset sequence value

How-To

Example

    PROCEDURE SP_RESET_SEQUENCE AS
        seq_exists VARCHAR2(1) := 'T';
    BEGIN
        SELECT CASE WHEN COUNT(*) > 0 THEN 'T' ELSE 'F' END
            INTO seq_exists
        FROM all_sequences
        WHERE sequence_name = 'SEQ_TEST' AND sequence_owner = 'TEST' ;

        IF seq_exists = 'T'
            THEN execute immediate 'DROP SEQUENCE TEST.SEQ_TEST';
        END IF;

        execute immediate 'create sequence TEST.SEQ_TEST start with 1 increment by 1 MAXVALUE 9999 cycle';

    END SP_RESET_SEQUENCE;

[DataGrip] [Database] How to print DBMSUOUTPUT message into console

Enable DBMSUOUTPUT in the Output Panel:

[Windows] 解決 Windows Server 2012 R2 輸入法預設中文問題

解決步驟

1. 到控制台新增語言：英文

2. 點選進階設定

3. 複寫預設輸入法為「英文」

4. 重開機後，打開命令提示字元，即可看到輸入法預設為英文

[Oracle] [DataGrip] How to connect to Oracle as SYSDBA

Problem

When I try to connect to Oracle using sys, I got the following error:

How-To

Using sys as sysdba as username is working fine.

[Oracle] ORA-06592 - CASE not found while executing CASE statement

Problem

When I try to execute the following function, I got ORA-06592 - CASE not found while executing CASE statement error:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

How-To

The error result from missing else clause, the function should be modified as bellows:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
            ELSE o_result := '';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

[Google Sheet] How to join double quote

How-To

Step 1. Check double quote value in ASCII Codes Table, i.e. char(34)

Step 2. Then you can using char(34) as bellows:

=concatenate(char(34), A1, char(34))

Reference

[1] https://infoinspired.com/google-docs/spreadsheet/join-double-quotes-with-text-in-google-sheets/

[SchemaSpy] How to customize css in SchemaSpy Document ?

Requirement
If I would like to customize css file in SchemaSpy, how to edit and assign new css file when I re-generate schema document?


How-To
1. Generate schema document firstly and find out the schemaSpy.css file.

2. Edit schemaSpy.css, ex. update background color in odd tr

tr.odd td.detail {
  background-color: #eaeaea;
}

3. Place schemaSpy.css file into specific directory, then assign it into -css parameter:

java -jar "C:\schemaspy\schemaSpy_5.0.0.jar" ^
     -dp "C:\schemaspy\ojdbc6.jar" ^
     -t orathin ^
     -db TEST_DB ^
     -s ap_tax ^
     -host localhost ^
     -port 1521 ^
     -u system ^
     -p password ^
     -schemas "ODS,TS" ^
     -charset UTF-8 ^
     -o "C:\schemaspy\schema_doc" ^
     -norows -hq -css "C:\schemaspy\schemaSpy.css" ^