TO_CHAR()函

报错信息:ORA-01722: 无效数字

问题SQL:


SELECT A.*,
B.FUND_CODE AS FUND_CODE,
 B.FUND_FULLNAME AS Fund_Fullname
FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B
WHERE A.ENTITY_ID=B.FUND_ID

/*
    此处A.ENTITY_ID的类型为VARCHAR;
    B.FUND_ID的类型为NUMBER;
    两者类型不匹配所以报错。
*/

更改后正确SQL:

SELECT A.*,
B.FUND_CODE AS FUND_CODE,
 B.FUND_FULLNAME AS Fund_Fullname
FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B
WHERE A.ENTITY_ID=TO_CHAR(B.FUND_ID)

/*
    类型转换函数to_char()
*/

拓展:

  1. 将日期型转换为字符串TO_CHAR()
    SELECT 
    TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS AM')
    FROM DUAL
  2. 将数字型转换为字符串TO_CHAR()
    SELECT 
    TO_CHAR(123.45678,'$99999.999') 
    FROM DUAL;
  3. 将字符换转换为日期TO_DATE()
     
    SELECT 
    TO_DATE ('20100913', 'YYYY-MM-DD')
    FROM DUAL;
  4. 将字符串转换为数字TO_NUMBER()
    SELECT 
    TO_NUMBER('01') 
    FROM DUAL;
    详见:OraclePL/SQL开发基础:类型转换函数(侵删)

 

 

 

如下SQL需查找并统计年假未休,现报错ORA-00942,请协助修改, 并优化oracle如下SQL性能,提高执行效率和减少资源占用: WITH EMP_BASE AS ( SELECT EMP_CODE, EMP_NAME, ANNUAL_LEAVE, YEAR, DECODE(HPB.WORK_PLACE, 0, '一园', 1, '二园', 2, '三园','','NULL', HPB.WORK_PLACE) AS PLANT, HCP.SF_LSHR_NO_GET(EMP_CODE, 14) AS DIV, HCP.SF_LSHR_NO_GET(EMP_CODE, 15) AS DEPT_NAME, HCP.SF_LSHR_NO_GET(EMP_CODE, 13) AS CLASS_NAME, SUBSTR(HCP.SF_LSHR_NO_GET(EMP_CODE, 15), LENGTH(HCP.SF_LSHR_NO_GET(EMP_CODE, 15))-5) AS DEPT_NO, HCP.SF_LSHR_NO_GET(EMP_CODE, 2) AS SEGMENT_NO, HCP.SF_LSHR_NO_GET(EMP_CODE, 9) AS EMAIL_FIELD9, HCP.SF_LSHR_NO_GET(EMP_CODE, 10) AS EMAIL_FIELD10, HCP.SF_LSHR_NO_GET(EMP_CODE, 11) AS EMAIL_FIELD11 FROM HCP.v_clk_tz_annual_leave JOIN HCP.TZ_EMP_IN HPB ON EMP_CODE=HPB.EMPCODE WHERE ANNUAL_LEAVE > 0 AND YEAR = TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy')) AND ((TO_CHAR(SYSDATE, 'mm') IN ('04', '05', '06', '07', '08', '09') AND TRUNC(SYSDATE) IN (SELECT MAX(B.A) FROM (SELECT TRUNC(SYSDATE, 'mm') + ROWNUM - 1 A FROM DBA_OBJECTS WHERE ROWNUM < 32) B WHERE TO_CHAR(B.A, 'day') = 'friday ')) OR (TO_CHAR(SYSDATE, 'mm') IN ('10', '11') AND TO_CHAR(SYSDATE, 'Day') = 'Friday ') OR TO_CHAR(SYSDATE, 'mm') = '12') ), LOOKUP_PREP AS ( SELECT REVERSE2, MEANING, HCP.SF_LSHR_NO_GET(MEANING, 8) AS V_EMAIL FROM HCP.KS_LOOKUP_VALUE WHERE LOOKUP_TYPE = 'DEPT_PEOPLE' AND ENABLED = 'Y' ), DEPT_PEOPLE AS ( SELECT E.EMP_CODE, L.V_EMAIL, ROW_NUMBER() OVER ( PARTITION BY E.EMP_CODE ORDER BY CASE WHEN L.REVERSE2 = E.DEPT_NO THEN 1 ELSE 2 END ) AS rnk FROM EMP_BASE E JOIN LOOKUP_PREP L ON L.REVERSE2 IN (E.DEPT_NO, E.SEGMENT_NO) ), GROUPED_DATA AS ( SELECT E.PLANT, E.DIV, E.DEPT_NAME, E.CLASS_NAME, COUNT(E.EMP_CODE) AS UNPEOPLE, SUM(E.ANNUAL_LEAVE) AS UNDAY, MAX(D.V_EMAIL) || ';' || HCP.SF_LSHR_NO_GET(MAX(E.EMAIL_FIELD9), 8) AS TOEMAIL, HCP.SF_LSHR_NO_GET(MAX(E.EMAIL_FIELD10), 8) || ';' || HCP.SF_LSHR_NO_GET(MAX(E.EMAIL_FIELD11), 8) AS CCEMAIL FROM EMP_BASE E JOIN DEPT_PEOPLE D ON E.EMP_CODE = D.EMP_CODE AND D.rnk = 1 GROUP BY E.PLANT, E.DIV, E.DEPT_NAME, E.CLASS_NAME ) SELECT PLANT 厂区, DIV 处级, DEPT_NAME 部门名称, CLASS_NAME 课别名称, UNPEOPLE 未休人数, UNDAY 未休天数, TOEMAIL, CCEMAIL FROM(SELECT PLANT, DIV, DEPT_NAME, CLASS_NAME, UNPEOPLE, UNDAY, TOEMAIL, CCEMAIL FROM GROUPED_DATA UNION ALL SELECT '合计', '', '', '', COUNT(EMP_CODE), SUM(ANNUAL_LEAVE), '', '' FROM EMP_BASE WHERE ANNUAL_LEAVE > 0) WHERE UNDAY IS NOT NULL ORDER BY PLANT, DIV, DEPT_NAME, CLASS_NAME
07-22
module newf( input clk, input rst_n, input key1, input sw0, input sw1, output [6:0] HEX0, HEX1, HEX2, HEX3, HEX4, HEX5, output [9:0] LEDR ); wire key1_press; key_sync_debounce key1_sync ( .clk(clk), .rst_n(rst_n), .key_in(key1), .key_press(key1_press) ); wire start_game = (key1_press && !display_flag && !input_flag); // === 分別定義 6 個目標字元與對應 HEX === wire [7:0] rand_char0, rand_char1, rand_char2, rand_char3, rand_char4, rand_char5; wire [6:0] hex4_0, hex4_1, hex4_2, hex4_3, hex4_4, hex4_5; // === 玩家撿到的 6 個線段與對應 HEX === wire [6:0] held0, held1, held2, held3, held4, held5; wire [6:0] hexf_0, hexf_1, hexf_2, hexf_3, hexf_4, hexf_5; // === 成功判斷 === wire success; // === 狀態旗標與定時器 === reg display_flag = 0; reg input_flag = 0; reg [31:0] timer = 0; // === 狀態控制 === always @(posedge clk or negedge rst_n) begin if (!rst_n) begin display_flag <= 0; input_flag <= 0; timer <= 0; end else begin if (start_game) begin display_flag <= 1; input_flag <= 0; timer <= 0; end else if (display_flag) begin timer <= timer + 1; if (timer >= 150_000_000) begin display_flag <= 0; input_flag <= 1; end end end end // === 模組實例化 === game4 rand_gen ( .clk(clk), .rst_n(rst_n), .char0(rand_char0), .char1(rand_char1), .char2(rand_char2), .start_game(start_game), .char3(rand_char3), .char4(rand_char4), .char5(rand_char5), .HEX0(hex4_0), .HEX1(hex4_1), .HEX2(hex4_2), .HEX3(hex4_3), .HEX4(hex4_4), .HEX5(hex4_5) ); gamef segment_game ( .clk(clk), .rst(rst_n), .key1(key1_press), .held0(held0), .held1(held1), .held2(held2), .held3(held3), .held4(held4), .held5(held5), .HEX0(hexf_0), .HEX1(hexf_1), .HEX2(hexf_2), .HEX3(hexf_3), .HEX4(hexf_4), .HEX5(hexf_5) ); GameComparer compare_game ( .held0(held0), .held1(held1), .held2(held2), .held3(held3), .held4(held4), .held5(held5), .target0(rand_char0), .target1(rand_char1), .target2(rand_char2), .target3(rand_char3), .target4(rand_char4), .target5(rand_char5), .success(success),.enable(sw1) ); // === 顯示 HEX:根據 flag 選擇是顯示字元或顯示輸入 === assign HEX0 = display_flag ? hex4_0 : hexf_0; assign HEX1 = display_flag ? hex4_1 : hexf_1; assign HEX2 = display_flag ? hex4_2 : hexf_2; assign HEX3 = display_flag ? hex4_3 : hexf_3; assign HEX4 = display_flag ? hex4_4 : hexf_4; assign HEX5 = display_flag ? hex4_5 : hexf_5; // === 成功提示 === endmodule module key_sync_debounce( input clk, input rst_n, input key_in, output reg key_press ); reg [1:0] sync; // 兩級同步器 reg [19:0] debounce_cnt; // 計數器,根據時脈頻率調整大小 reg key_state, key_state_prev; localparam DEBOUNCE_LIMIT = 1_000_000; // 約 20ms @ 50MHz (可調整) always @(posedge clk or negedge rst_n) begin if (!rst_n) begin sync <= 2'b00; end else begin sync <= {sync[0], key_in}; end end always @(posedge clk or negedge rst_n) begin if (!rst_n) begin debounce_cnt <= 0; key_state <= 0; end else if (sync[1] == key_state) begin debounce_cnt <= 0; end else begin debounce_cnt <= debounce_cnt + 1; if (debounce_cnt >= DEBOUNCE_LIMIT) begin key_state <= sync[1]; debounce_cnt <= 0; end end end always @(posedge clk or negedge rst_n) begin if (!rst_n) begin key_state_prev <= 0; key_press <= 0; end else begin key_state_prev <= key_state; key_press <= (~key_state_prev) & key_state; // 偵測上升沿 end end endmodule module GameComparer( input [6:0] held0, held1, held2, held3, held4, held5, input enable, input [7:0] target0, target1, target2, target3, target4, target5, output reg success ); // function: 將字元轉成 7 段 pattern (bit7~bit1) function [6:0] char_to_segments; input [7:0] c; begin case (c) 8'h30: char_to_segments = 7'b1000000; // '0' 8'h31: char_to_segments = 7'b1111001; // '1' 8'h32: char_to_segments = 7'b0100100; // '2' 8'h33: char_to_segments = 7'b0110000; // '3' 8'h34: char_to_segments = 7'b0011001; // '4' 8'h35: char_to_segments = 7'b0010010; // '5' 8'h36: char_to_segments = 7'b0000010; // '6' 8'h37: char_to_segments = 7'b1111000; // '7' 8'h38: char_to_segments = 7'b0000000; // '8' 8'h39: char_to_segments = 7'b0010000; // '9' 8'h41, 8'h61: char_to_segments = 7'b0001000; // 'A' or 'a' 8'h42, 8'h62: char_to_segments = 7'b0000011; // 'B' or 'b' 8'h43, 8'h63: char_to_segments = 7'b1000110; // 'C' or 'c' 8'h44, 8'h64: char_to_segments = 7'b0100001; // 'D' or 'd' 8'h45, 8'h65: char_to_segments = 7'b0000110; // 'E' or 'e' 8'h46, 8'h66: char_to_segments = 7'b0001110; // 'F' or 'f' // 依需要可自行擴充更多字元 default: char_to_segments = 7'b1111111; // 不認識字元全滅(關閉全部) endcase end endfunction reg match0,match1,match2,match3,match4,match5; always@(enable)begin match0 = (held0 == char_to_segments(target0)); match1 = (held1 == char_to_segments(target1)); match2 = (held2 == char_to_segments(target2)); match3 = (held3 == char_to_segments(target3)); match4 = (held4 == char_to_segments(target4)); match5 = (held5 == char_to_segments(target5)); success = match0 & match1 & match2 & match3 & match4 & match5; end endmodule module game4( input clk, input start_game, input rst_n, // 用來控制是否更新隨機字串 output reg [6:0] HEX0, output reg [6:0] HEX1, output reg [6:0] HEX2, output reg [6:0] HEX3, output reg [6:0] HEX4, output reg [6:0] HEX5, output reg [7:0] char0, output reg [7:0] char1, output reg [7:0] char2, output reg [7:0] char3, output reg [7:0] char4, output reg [7:0] char5 ); // sw0 同步與去彈跳 // LFSR 32-bit 隨機數產生器 reg [31:0] lfsr; always @(posedge clk or negedge rst_n) begin if (!rst_n) lfsr <= 32'hABCDE123; else lfsr <= {lfsr[30:0], lfsr[31] ^ lfsr[21] ^ lfsr[1] ^ lfsr[0]}; end // 隨機字元數量 1~6 reg [2:0] rand_len_reg; always @(posedge clk or negedge rst_n) begin if (!rst_n) rand_len_reg <= 3'd1; else if (start_game) rand_len_reg <= (lfsr[1:0] % 4) + 1; // 限制長度在 1~4 end ///// reg [3:0] rand_chars [0:5]; integer i; always @(posedge clk or negedge rst_n) begin if (!rst_n) begin for (i = 0; i < 6; i = i + 1) rand_chars[i] <= 4'd0; end else if (start_game) begin for (i = 0; i < 6; i = i + 1) begin rand_chars[i] <= lfsr[(4*i)+3 -: 4]; end end end // 將 4-bit 數值轉成 ASCII 字元 (0~9,a~f) function [7:0] to_ascii; input [3:0] val; begin if (val < 4'd10) to_ascii = 8'd48 + val; // '0'~'9' else to_ascii = 8'd97 + (val - 10); // 'a'~'f' end endfunction // 輸出 ASCII 字元 always @(*) begin char0 = to_ascii(rand_chars[0]); char1 = to_ascii(rand_chars[1]); char2 = to_ascii(rand_chars[2]); char3 = to_ascii(rand_chars[3]); char4 = to_ascii(rand_chars[4]); char5 = to_ascii(rand_chars[5]); end // 7-seg 顯示轉換式 (0-9,a-f) function [6:0] seg7; input [3:0] val; begin case(val) 4'h0: seg7 = 7'b100_0000; //0 4'h1: seg7 = 7'b111_1001; //1 4'h2: seg7 = 7'b010_0100; //2 4'h3: seg7 = 7'b011_0000; //3 4'h4: seg7 = 7'b001_1001; //4 4'h5: seg7 = 7'b001_0010; //5 4'h6: seg7 = 7'b000_0010; //6 4'h7: seg7 = 7'b111_1000; //7 4'h8: seg7 = 7'b000_0000; //8 4'h9: seg7 = 7'b001_0000; //9 4'hA: seg7 = 7'b000_1000; //A 4'hB: seg7 = 7'b000_0011; //b (小寫b) 4'hC: seg7 = 7'b100_0110; //C 4'hD: seg7 = 7'b010_0001; //d (小寫d) 4'hE: seg7 = 7'b000_0110; //E 4'hF: seg7 = 7'b000_1110; //F default: seg7 = 7'b111_1111; //關閉 endcase end endfunction // 顯示對應長度字元,超出長度的關閉顯示 always @(*) begin HEX0 = 7'b1111111; // 永遠關閉 HEX1 = (rand_len_reg >= 1) ? seg7(rand_chars[0]) : 7'b1111111; HEX2 = (rand_len_reg >= 2) ? seg7(rand_chars[1]) : 7'b1111111; HEX3 = (rand_len_reg >= 3) ? seg7(rand_chars[2]) : 7'b1111111; HEX4 = (rand_len_reg >= 4) ? seg7(rand_chars[3]) : 7'b1111111; HEX5 = 7'b1111111; // 永遠關閉 end endmodule module gamef( input clk, input rst, input key1, // 直接連接 key1_press output reg [6:0] HEX0, output reg [6:0] HEX1, output reg [6:0] HEX2, output reg [6:0] HEX3, output reg [6:0] HEX4, output reg [6:0] HEX5, output reg [6:0] held0, output reg [6:0] held1, output reg [6:0] held2, output reg [6:0] held3, output reg [6:0] held4, output reg [6:0] held5 ); reg [6:0] running_pattern; reg [2:0] pos; reg [7:0] lfsr; reg [24:0] clk_div; // 按鍵同步鎖存與上升緣偵測 reg key1_sync_0, key1_sync_1; wire key1_rising; reg start; assign key1_rising = key1_sync_0 & (~key1_sync_1); wire [2:0] rand_pos = lfsr[2:0] % 7; wire [6:0] new_pattern = ~(7'b0000001 << rand_pos); // 只有一個 bit 為 0,其餘為 1 wire feedback = lfsr[7] ^ lfsr[5] ^ lfsr[4] ^ lfsr[3]; always @(posedge clk or negedge rst ) begin if (!rst) begin lfs 幫我修改遊戲如果比對方面,如果拼出來的線條字符是對的會怎麼顯示
06-07
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值