Baanboard.com

Go Back   Baanboard.com > Blogs > Hitesh Shah's blog

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
19%
200 - 500 GB
30%
500 - 800 GB
4%
800 - 1200 GB
7%
1200 - 1500 GB
7%
1500 - 2000 GB
11%
> 2000 GB
22%
Total votes: 54

Baanboard at LinkedIn


Reference Content

 
Tips for Multi-company / Calculated items in MS SQL query
By Hitesh Shah at 23 May 2009 - 11:18

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.

 

Multi-company query

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

From ttfgld106300

UNION

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

From ttfgld106301

 

This example will select data from tfgld106 of 300 and 301 company.

 

Calculated  Items

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,

case  t_dbcr

when 1 then t_amth --> Debit amount

when 2 then (-1) * t_amth--> Credit Amount

end as Amount , ‘300’ as company --> Constant for company

From ttfgld106300

 

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 .

0
No votes yet


All times are GMT +2. The time now is 18:10.


©2001-2018 - Baanboard.com - Baanforums.com