Trying to understand a SQL Server 2005 Query

Question

I am converting a few queries written for SQL Server 2005 into language and syntax that will work in SQL Server 2012. Some of the language in this query is deprecated and/or I'm not familiar with it. In the query below I know that COMPUTE has been replaced by ROLLUP.

The first thing I've come across which I am unfamiliar is why does the field c.employee_name have ,1 after it? Same question for d.client_name. I noticed I can change the 1 to other numbers, but I can't figure out the purpose of having it. I also noticed if I remove the ,1 the query does not work.

The second question is with the from clause three tables are references and aliased. This is probably basic to many people, but I have never seen this before and never referenced more than one table in a single from clause. I always have used joins. Is this normal syntax?

select 
   a.latest_period , 
   b.offc,
   convert(char(4), c.employee_name, 1),
   d.client_code, 
   convert(Char(25), d.client_name, 1),
   sum(tobill_amt) 
from 
   CDT_disb a, hbm_matter b, hbm_persnl c, HBM_client d
where  
   a.matter_uno = b.matter_uno 
   and c.empl_uno = b.bill_empl_uno 
   and b.client_uno = d.client_uno
   and a.latest_period between '200701' AND '200901'
   and a.wip_status = 'W' and a.hardsoft = 'S' and a.tobill_amt <> 0
group by 
   a.latest_period, b.offc, c.employee_name, d.client_code, d.client_name
compute sum(sum(tobill_amt))

Here is a sample of the result set

latest_period   offc    (No Column Name)    client_code (No Column Name)    (No Column Name)
-------------   ----    ----    ----------- -------------------------   -------------------------
200704  ATL Brav    TAY003  Smith, James    14310.25
200704  MV  Arms    CLA002  Smith, Jane 2000

Check out the [CONVERT](http://msdn.microsoft.com/en-us/library/ms187928(v=sql.110).aspx) function. "1" is a parameter for that function. — Dave Mason, Jul 15 '14 at 20:12
Take a look at [this link](http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) about the multiple tables part. — imtheman, Jul 15 '14 at 20:17
And while you're at it - get rid of the deprecated, old-style JOINs ! See [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) should be replaced with the ANSI-**92** SQL Standard `JOIN` syntax (introduced more than **20 years** ago) — marc_s, Jul 15 '14 at 20:22

score 1 · Accepted Answer · answered Jul 15 '14 at 20:22

Per MSDN documentation syntax for CONVERT function is

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

So in your case convert(char(4),c.employee_name,1) the 1 is being used a style format.

Referencing 3 tables in FROM clause as below; is an implicit join syntax.

from CDT_disb a,hbm_matter b, hbm_persnl c, HBM_client d

You can and better change this to explicit join syntax like

from CDT_disb a
join hbm_matter b on a.col = b.col
join hbm_persnl c on a.col = c.col
join HBM_client d on a.col = d.col

Trying to understand a SQL Server 2005 Query

1 Answers1