New user. Installed LO, tried to access Postgres, connection tested OK, but SQL is not compatible.
Is there a Postgres driver for Apple Silicon that can be installed to allow full access to Postgres ?
Thanks.
Using the internal driver of LO for PostgreSQL on Linux here without any problem. Works also on Windows. Couldnât test any Mac⌠What do you mean with âSQL is not compatibleâ.
Well thatâs odd. I decided to create a small query to show the issue and it no longer gets the error!
So I tried one of the big queries with lots of joins and unions and it worked fine.
I have no idea why I was getting errors yesterday. âUnion Allâ was giving SQL errors.
Thanks.
Here a query with UNION. You have to press the Button for direct SQL, because the GUI of Base doesnât accept UNION.
1 Like
There are 2 kinds of queries, parsed ones and direct ones, sometimes referred to as âpass-through queriesâ.
Base can not parse union UNION queries. You can run them in direct mode. Switch the query window from design view to SQL view and turn on menu:Edit>âRun SQL directlyâ.
Direct SQL does not work with form/subform links. Workaround: create a parsed query: SELECT * FROM "Direct_SQL_Query"
1 Like
Today I am getting SQL errors again. I copy the query from my IDE into Base (sql edit mode). If I select ârun SQL directlyâ, nothing happens. I cannot save the query because it gets the sql error.
FWIW, here is the query:
-- ------------------------------------------------------------------------------------------------------------------------------
-- Account Summary Sheet: Holdings by Account/Symbol with, Acc totals, Fidelity Grand Total and All Accs Grand total
-- ------------------------------------------------------------------------------------------------------------------------------
with
const as (
select current_date + 7 as max_date -- Only current and near future open lots.
-- , 2 as decs -- Decimal places.
-- , 100 as mult -- Percentage divisor -- Worksheet
-- select current_date + 7 as max_date -- Only current and near future open lots.
, 4 as decs -- Decimal places.
, 1 as mult -- Percentage divisor -- Excel
),
cash_syms as (
select symbol from stock_tbl where stock_type = ('CASH') -- CASH and LOAN.
),
tot_div_per_sym_ex_dates as ( -- Distinct (symbol, ex_div_date), tot_div_per_share (may be multiple divs on same day, so added up).
select distinct on (symbol, coalesce(ex_div_date, trade_date)) -- use trade_date if no ex_div_date.
symbol
, coalesce(ex_div_date, trade_date) as ex_div_date
, sum(coalesce(div_per_share, 0)) as tot_div_per_share
from trade_tx_tbl
where action in ('Receive', 'Reinvest')
and coalesce(ex_div_date, trade_date) <= (select max_date from const)
group by account_name, symbol, ex_div_date, trade_date
),
divs_per_acc_sym as ( -- Total share of divs for current qty left for each acc/symbol.
select tl.account_name
, tl.symbol
, round(sum(tl.qty_left * dps.tot_div_per_share), 2) as tot_div_per_acc_sym
from trade_lot_tbl tl
join tot_div_per_sym_ex_dates dps using (symbol)
where tl.trade_date <= (select max_date from const)
and tl.trade_date < dps.ex_div_date
group by account_name, symbol
),
holdings as ( -- List of account-symbol details for open lots
select lot.account_name as "Account"
, case
when stock_class in ('Bond', 'Cash') then stock_class
else 'Stock'
end as class
, lot.symbol as "Symbol"
, round(sum(qty_left), 3) as "Qty"
, round(sum(qty_left * lot.adjusted_price) / sum(qty_left), 2) as "Avg Price"
, round(get_latest_price(lot.symbol), 2) as "Curr Price"
, round(sum(qty_left * lot.adjusted_price), 2) as "Basis"
, round(sum(qty_left * get_latest_price(lot.symbol)), 2) as "Value"
, round(sum(qty_left * (get_latest_price(lot.symbol) - adjusted_price)), 2) as "Pot PL"
, round(sum(qty_left * (get_latest_price(lot.symbol) - adjusted_price)) /
sum(qty_left * adjusted_price) * (select mult from const),
(select decs from const)) as "Pot PL %"
, round(coalesce(tot_div_per_acc_sym, 0), 2) as "Div Inc"
, round(coalesce(tot_div_per_acc_sym, 0) +
sum(qty_left * (get_latest_price(lot.symbol) - adjusted_price)), 2) as "True Net PPL"
, round((coalesce(tot_div_per_acc_sym, 0) +
sum(qty_left * (get_latest_price(lot.symbol) - adjusted_price))) /
sum(qty_left * adjusted_price) * (select mult from const),
(select decs from const)) as "True Net PPL %"
from trade_lot_tbl lot
join stock_tbl s using (symbol)
left outer join divs_per_acc_sym dps using (account_name, symbol)
where lot.qty_left > 0 -- Only active lots.
and lot.trade_date <= (select max_date from const)
group by lot.account_name, class, lot.symbol, tot_div_per_acc_sym
),
acc_totals as (
-- List of total account value for open lots - used for % calc
select lot.account_name as account
, sum(lot.qty_left * get_latest_price(lot.symbol)) /
(select mult from const) as acc_total
from trade_lot_tbl lot
join stock_tbl s using (symbol)
where lot.qty_left > 0 -- Only active lots.
and lot.trade_date <= (select max_date from const)
group by lot.account_name
)
select "Account"
, "Symbol"
, "Qty"
, "Avg Price"
, "Curr Price"
, "_"
, "Basis"
, "Value"
, "% of Acc"
, "Pot PL"
, "Pot PL %"
, "Div Inc"
, "True Net PPL"
, "True Net PPL %"
from ( -- Cash Symbols within Account
select "Account" as sort_val
, "Account"
, class
, "Symbol"
, ' ' as "_"
, "Qty"
, "Avg Price"
, "Curr Price"
, "Basis"
, "Value"
, round("Value" / (select acc_total from acc_totals atot where atot.account = "Account"),
(select decs from const)) as "% of Acc"
, "Pot PL"
, "Pot PL %"
, "Div Inc"
, "True Net PPL"
, "True Net PPL %"
from holdings h
where class = 'Cash'
union all -- Cash Total
select "Account" as sort_val
, ' ' as "Account"
, 'CashTotal' as class
, ' ' as "Symbol"
, 'Cash Total:' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, round(sum("Basis"), 2) as "Basis"
, round(sum("Value"), 2) as "Value"
, round(sum("Value") / (select acc_total from acc_totals atot where atot.account = "Account"),
(select decs from const)) as "% of Acc"
, round(sum("Pot PL"), 2) as "Pot PL"
, round(sum("Pot PL") / sum("Basis"),
(select decs from const)) as "Pot PL %"
, round(sum("Div Inc"), 2) as "Div Inc"
, round(sum("True Net PPL"), 2) as "True Net PPL"
, round(sum("True Net PPL") / sum("Basis"),
(select decs from const)) as "True Net PPL%"
from holdings h
where class = 'Cash'
group by sort_val
union all -- Bond Symbols within Account
select "Account" as sort_val
, "Account"
, 'D-Bond' as class
, "Symbol"
, ' ' as "_"
, "Qty"
, "Avg Price"
, "Curr Price"
, "Basis"
, "Value"
, round("Value" / (select acc_total from acc_totals atot where atot.account = "Account"),
(select decs from const)) as "% of Acc"
, "Pot PL"
, "Pot PL %"
, "Div Inc"
, "True Net PPL"
, "True Net PPL %"
from holdings
where class = 'Bond'
union all -- Bond Total
select "Account" as sort_val
, ' ' as "Account"
, 'D-BondTotal' as class
, ' ' as "Symbol"
, 'Bond Total:' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, round(sum("Basis"), 2) as "Basis"
, round(sum("Value"), 2) as "Value"
, round(sum("Value") / (select acc_total from acc_totals atot where atot.account = "Account"),
(select decs from const)) as "% of Acc"
, round(sum("Pot PL"), 2) as "Pot PL"
, round(sum("Pot PL") / sum("Basis"), (select decs from const)) as "Pot PL %"
, round(sum("Div Inc"), 2) as "Div Inc"
, round(sum("True Net PPL"), 2) as "True Net PPL"
, round(sum("True Net PPL") /
sum("Basis"), (select decs from const)) as "True Net PPL%"
from holdings
where class = 'Bond'
group by sort_val
union all -- Other (non-Cash/Bond) Symbols within Account
select "Account" as sort_val
, "Account"
, class
, "Symbol"
, ' ' as "_"
, "Qty"
, "Avg Price"
, "Curr Price"
, "Basis"
, "Value"
, round("Value" / (select acc_total from acc_totals atot where atot.account = "Account"),
(select decs from const)) as "% of Acc"
, "Pot PL"
, "Pot PL %"
, "Div Inc"
, "True Net PPL"
, "True Net PPL %"
from holdings
where class = 'Stock'
union all -- Stock Total
select "Account" as sort_val
, ' ' as "Account"
, 'StockTotal' as class
, ' ' as "Symbol"
, 'Stock Total:' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, round(sum("Basis"), 2) as "Basis"
, round(sum("Value"), 2) as "Value"
, round(sum("Value") / (select acc_total from acc_totals atot where atot.account = "Account"),
(select decs from const)) as "% of Acc"
, round(sum("Pot PL"), 2) as "Pot PL"
, round(sum("Pot PL") / sum("Basis"), (select decs from const)) as "Pot PL %"
, round(sum("Div Inc"), 2) as "Div Inc"
, round(sum("True Net PPL"), 2) as "True Net PPL"
, round(sum("True Net PPL") /
sum("Basis"), (select decs from const)) as "True Net PPL%"
from holdings
where class = 'Stock'
group by sort_val
union all -- Account Totals
select "Account" as sort_val
, ' ' as "Account"
, 'zAccTotal' as class
, ' ' as "Symbol"
, 'Account Total:' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, round(sum("Basis"), 2) as "Basis"
, round(sum("Value"), 2) as "Value"
, null as "% of Acc"
, round(sum("Pot PL"), 2) as "Pot PL"
, round(sum("Pot PL") / sum("Basis"),
(select decs from const)) as "Pot PL %"
, round(sum("Div Inc"), 2) as "Div Inc"
, round(sum("True Net PPL"), 2) as "True Net PPL"
, round(sum("True Net PPL") /
sum("Basis"), 4) as "True Net PPL%"
from holdings
group by sort_val
union all -- Account Delimiter
select "Account" as sort_val
, ' ' as "Account"
, 'zAcczDelim' as class
, ' ' as "Symbol"
, ' ' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, null as "Basis"
, null as "Value"
, null as "% of Acc"
, null as "Pot PL"
, null as "Pot PL %"
, null as "Div Inc"
, null as "True Net PPL"
, null as "True Net PPL %"
from holdings
group by sort_val
union all -- Fidelity Grand Total
select '_' as sort_val
, ' ' as "Account"
, 'zFGrandTotal' as class
, ' ' as "Symbol"
, 'Fidelity Accs Total:' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, round(sum("Basis"), 2) as "Basis"
, round(sum("Value"), 2) as "Value"
, null as "% of Acc"
, round(sum("Pot PL"), 2) as "Pot PL"
, round(sum("Pot PL") / sum("Basis"),
(select decs from const)) as "Pot PL %"
, round(sum("Div Inc"), 2) as "Div Inc"
, round(sum("True Net PPL"), 2) as "True Net PPL"
, round(sum("True Net PPL") /
sum("Basis"), (select decs from const)) as "True Net PPL%"
from holdings
join account_tbl acc on "Account" = acc.account_name
where acc.institution = 'Fidelity'
union all -- All Accounts Grand Total
select '_' as sort_val
, (select to_char(max(sys_updated_ts), 'Dy YYYY-MM-DD @ HH24:MI') as "time" from price_tbl) as "Account"
, 'zGrandTotal' as class
, ' ' as "Symbol"
, 'All Accs Total:' as "_"
, null as "Qty"
, null as "Avg Price"
, null as "Curr Price"
, round(sum("Basis"), 2) as "Basis"
, round(sum("Value"), 2) as "Value"
, null as "% of Acc"
, round(sum("Pot PL"), 2) as "Pot PL"
, round(sum("Pot PL") / sum("Basis"),
(select decs from const)) as "Pot PL %"
, round(sum("Div Inc"), 2) as "Div Inc"
, round(sum("True Net PPL"), 2) as "True Net PPL"
, round(sum("True Net PPL") / sum("Basis"),
(select decs from const)) as "True Net PPL%"
from holdings
) as x
order by sort_val, class, "Symbol"
Sorry, hold that. I just discovered that âRun SQL Directlyâ is a toggle.
1 Like