Tips for Multi-company / Calculated items in MS SQL query
If one is using SQL 2000/2005 and one wants to access the same from outside (say MS Excel or .net application or any other application) , one can use the SQL client in conjunction with following SQL tips.
Though baan driver let’s user access data from same table from multiple companies using compnr specification, in the MS SQL backend the tables are separate with company suffix . If one is to combine the data from multiple companies for same table, Union operator is SQL query is the key. Using this operator also one may need to know from which company the data has originated . In Baan 4GL one can get this with ._compnr field selected . In MS SQL one needs to hard code the company constant expression in each SQL query combined with UNION operator . An example will illustrate this clearly.
Select t_leac as Ledger ,t_dbcr ‘Debit/Credit’,t_year as Year ,t_fprd as Period, t_amth as Amount , ‘300’ as company – Constant for company
Select t_leac as Ledger ,t_dbcr ‘Debit/Credit’,t_year as Year ,t_fprd as Period, t_amth as Amount, ‘301’ as company – Constant for company
This example will select data from tfgld106 of 300 and 301 company.
Same way people can select expression in select clause unlike Baan 4gl sql query. This lets users / developers process certain data at database level itself . e.g.
When user wants to treat credit amount as –ve and debit as positive , he/she can use following SQL query
Select t_leac as Ledger ,t_year as Year ,t_fprd as Period,
when 1 then t_amth --> Debit amount
when 2 then (-1) * t_amth--> Credit Amount
end as Amount , ‘300’ as company --> Constant for company
These are pretty basic stuff for veterans but for starters this may be very much useful.
PS – The SQL queries are more hint and may probably need syntax check .