Table alias column alias HELP PLEASE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table alias column alias HELP PLEASE

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-19 : 11:24:41

Hello friends,
I am trying to convert access query to sql view and sql server does not support table alias (I think). Please look at my access query and sql view below:

Access:
SELECT [mo Data].[mo ID], 1*nz(1*[la],0) AS labo, 1*nz(1*[tra],0) AS trave,
1*nz(1*[odc],0) AS od, 1*nz(1*

,0) AS [h]
FROM [SELECT [job hr].[mo ID], Sum(nz([job hr].[la money],0)) AS la,
Sum(nz([job hr].[tra money],0)) AS tra, Sum(nz([job hr].[ODC money],0)) AS odc,
Sum(nz([job hr].[job hr],0)) AS hr FROM [job hr] LEFT JOIN job
ON [job hr].[job ID] = job.[job ID] WHERE (((job.[job num])="99"))
GROUP BY [job hr].[mo ID]]. AS a RIGHT JOIN [mo Data]
ON a.[mo ID] = [mo Data].[mo ID];

sql server:
SELECT mo_data.mo_id,
IsNull(la,0) AS labo, IsNull(tra,0) AS trave, IsNull(odc,0) AS od, IsNull(hs,0) AS h
FROM SELECT job_hs.mo_id, Sum(IsNull(job_hs.la_money,0)) AS la,
Sum(IsNull(job_hs.tra_money,0)) AS tra, Sum(IsNull(job_hs.odc_money,0)) AS odc,
Sum(IsNull(job_hs.job_hs,0)) AS hs FROM job_hs
LEFT JOIN job ON job_hs.job_id = job.job_id
WHERE (((job.[job num])='99')) GROUP BY job_hs.mo_id. AS a
RIGHT JOIN mo_data ON a.mo_id = mo_data.mo_id;

The error is sql doesn't recognize the 'a'

Can you help me to modify it?

Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-04-19 : 12:11:34

Just had a quick glance...
The table alias becomes a derived table. These need to be surrounded by round brackets. Also it's not going to like the "." after "GROUP BY job_hs.mo_id".
I.e., try replacing that section with:


(SELECT job_hs.mo_id, Sum(IsNull(job_hs.la_money,0)) AS la, 
Sum(IsNull(job_hs.tra_money,0)) AS tra, Sum(IsNull(job_hs.odc_money,0)) AS odc, 
Sum(IsNull(job_hs.job_hs,0)) AS hs FROM job_hs 
LEFT JOIN job ON job_hs.job_id = job.job_id 
WHERE job.[job num]='99' GROUP BY job_hs.mo_id) AS a

Mark

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-19 : 12:14:14

Jenny,

Post the DDL of the 2 tables please

Brett

8-)

EDIT:

What's with 1* stuff? Here try this



SELECT    m.[mo ID]
	, a.la  AS labo
	, a.tra AS trave
	, a.odc AS od
	, a.hr  AS [h]
  FROM (   SELECT h.[mo ID]
		, COALESCE(SUM(h.[la money]),0)  AS la
		, COALESCE(SUM(h.[tra money]),0) AS tra
		, COALESCE(SUM(h.[ODC money]),0) AS odc
		, COALESCE(SUM(h.[job hr]),0)    AS hr 
	   FROM  [job hr] h
      LEFT JOIN   job	  j 
	     ON h.[job ID] = j.[job ID] 
	  WHERE j.[job num]='99' 
       GROUP BY h.[mo ID]] 
        ) AS a 
RIGHT JOIN [mo Data] m 
        ON a.[mo ID] = m.[mo ID]

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-19 : 12:53:30

hello X00, it worked.
Big thanks.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-19 : 13:27:20

quote:
Originally posted by jennypretty

hello X00

That's funny..is that a kiss and 2 hugs?
Don't tell my wife.

Let me ask.

Does this return exactly the same thing?


    SELECT  h.[mo ID]
	  , COALESCE(SUM(h.[la money]),0)  AS la
	  , COALESCE(SUM(h.[tra money]),0) AS tra
	  , COALESCE(SUM(h.[ODC money]),0) AS odc
	  , COALESCE(SUM(h.[job hr]),0)    AS hr 
      FROM  [job hr] h
 LEFT JOIN  job	  j 
	ON  h.[job ID] = j.[job ID] 
RIGHT JOIN  [mo Data] m 
	ON  a.[mo ID] = m.[mo ID]
     WHERE  j.[job num]='99' 
  GROUP BY  h.[mo ID]]

And it's Brett btw

Good Luck

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources