search

xdenser / node-firebird-libfbclient

Firebird SQL binding
MIT License
82 stars 34 forks source link

Stored procedure in Select Operator (joins and subquery) ? #73

Closed Diaskhan closed 6 years ago

Diaskhan commented 7 years ago

Its possible to execute this kind of queries with stored procedure join ??

SELECT L.DOC_ID doc_id, LPR.NAME name Z(OD.S_D_OUT - OD.S_C_OUT) ,, (SELECT SUM(PRO.S_D_OUT - PRO.S_C_OUT) FROM A2_SO_AN('LA', 'A_LN#' || L.DOC_ID, L.D_DATE, CURRENT_DATE, '', 1) PRO WHERE PRO.OBJ_3_ID IN ('A_AMT#L_PR_D', 'A_AMT#L_IN_D')) , Z(TR.S_C_OUT - TR.S_D_OUT) FROM DR_LOAN L INNER JOIN DOC DOC ON DOC.ID = L.DOC_ID LEFT JOIN R_CLIENT CLI ON CLI.ID = L.CLIENT_ID LEFT JOIN R_LPR LPR ON L.LPR_ID = LPR.ID LEFT JOIN X_CONST_VALUE X ON (COALESCE(CLI.EMP, 0) = 1 AND X.C_NAME = 'C_STAFF_ACC_NO') OR (COALESCE(CLI.EMP, 0) <> 1 AND X.C_NAME = 'C_CLIENT_ACC_NO') LEFT JOIN A1_SO(X.C_VALUE, 'A_CLI#' || L.CLIENT_ID, L.D_DATE, CURRENT_DATE, '', 1) TR ON 1 = 1 LEFT JOIN A12_SO('L', 'A_CLI#' || L.CLIENT_ID, 'A_LN#' || L.DOC_ID, L.D_DATE, CURRENT_DATE, '', 1) OD ON 1 = 1

xdenser commented 7 years ago

The module does not analyze/change SQL statements. If this statement works in other Firebird clients it should work with this module.

Diaskhan commented 7 years ago

Ihave tried this example, https://github.com/xdenser/node-fb-sample

A.for query Select * from R_CLIENT (R_CLIENT is table) its returned me a all records.

B. For query SELECT * FROM UP_LOAN_CARD_AMT('10A3QRK') did not returm anything !! But this query is correct and has no error, if remove quote from query its signalized that 10A3QRK' wrong field!!

xdenser commented 7 years ago

Please, provide failing test case.

GuilhermeHumberto commented 6 years ago

@Diaskhan I have a same problem in my procedure. When I execute a table, working perfectly but when I execute a stored procedure did not returning anything. 😞

Diaskhan commented 6 years ago

GuilhermeHumberto Firebird is very bad database, Dont Use it !

Better use postgresql or Mysql, But not Firebird !

GuilhermeHumberto commented 6 years ago

@Diaskhan I see, but the Firebird is required in my application 😠. So I need to solve this.

Diaskhan commented 6 years ago

GuilhermeHumberto its OSS, open source support, provide examples of not working Code, maybe somebody will fix it in future 2-4 years !

xdenser commented 6 years ago

Fixed in v0.1.2