Turn a query into a subquery - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Turn a query into a subquery

Author

Topic

SBLatta
Starting Member

33 Posts

Posted - 2010-02-09 : 10:14:59

I have the following query, and it's working fine, returning each unique Voucher number and the days it took to pay it:


SELECT DISTINCT APLINH.Vouchernumber, COALESCE((SELECT APTRAN.Documentdate from PFWEASLEY.APTRAN APTRAN WHERE APTRAN.Transactiontype = 'P' 
and APTRAN.Applyto = APLINH.Vouchernumber), (SELECT APTRANH.Documentdate from PFWEASLEY.APTRANH APTRANH WHERE APTRANH.Transactiontype = 'P' 
and APTRANH.Applyto = APLINH.Vouchernumber)) - APLINH.Documentdate AS Days2Pay
FROM PFWEASLEY.APLINH APLINH
WHERE (APLINH.Vendorkey Like 'E%') AND (APLINH.Acct<>'115100000000') AND (APLINH.Documentdate Between {d '2010-01-01'} And {d '2010-01-31'})

I'm trying to turn it into a subquery so I can get a count of the number of vouchers for each 'days to pay'.

I tried:


Select Days2Pay, Count(*)
FROM
(SELECT DISTINCT APLINH.Vouchernumber, COALESCE((SELECT APTRAN.Documentdate from PFWEASLEY.APTRAN APTRAN WHERE APTRAN.Transactiontype = 'P' 
and APTRAN.Applyto = APLINH.Vouchernumber), (SELECT APTRANH.Documentdate from PFWEASLEY.APTRANH APTRANH WHERE APTRANH.Transactiontype = 'P' 
and APTRANH.Applyto = APLINH.Vouchernumber)) - APLINH.Documentdate AS Days2Pay
FROM PFWEASLEY.APLINH APLINH
WHERE (APLINH.Vendorkey Like 'E%') AND (APLINH.Acct<>'115100000000') AND (APLINH.Documentdate Between {d '2010-01-01'} And {d '2010-01-31'}))
GROUP BY Days2Pay

But I'm getting a syntax error with no hint of where my code is wrong. I'm working in Excel's query builder. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 10:18:49

you should give it an alias


Select Days2Pay, Count(*)
FROM
(SELECT DISTINCT APLINH.Vouchernumber, COALESCE((SELECT APTRAN.Documentdate from PFWEASLEY.APTRAN APTRAN WHERE APTRAN.Transactiontype = 'P' 
and APTRAN.Applyto = APLINH.Vouchernumber), (SELECT APTRANH.Documentdate from PFWEASLEY.APTRANH APTRANH WHERE APTRANH.Transactiontype = 'P' 
and APTRANH.Applyto = APLINH.Vouchernumber)) - APLINH.Documentdate AS Days2Pay
FROM PFWEASLEY.APLINH APLINH
WHERE (APLINH.Vendorkey Like 'E%') AND (APLINH.Acct<>'115100000000') AND (APLINH.Documentdate Between {d '2010-01-01'} And {d '2010-01-31'}))t
GROUP BY Days2Pay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 10:25:22

And your "subquery" is known as "derived table".

No, you're never too old to Yak'n'Roll if you're too young to die.

SBLatta
Starting Member

33 Posts

Posted - 2010-02-09 : 10:48:21

quote:

Originally posted by visakh16

you should give it an alias


Select Days2Pay, Count(*)
FROM
(SELECT DISTINCT APLINH.Vouchernumber, COALESCE((SELECT APTRAN.Documentdate from PFWEASLEY.APTRAN APTRAN WHERE APTRAN.Transactiontype = 'P' 
and APTRAN.Applyto = APLINH.Vouchernumber), (SELECT APTRANH.Documentdate from PFWEASLEY.APTRANH APTRANH WHERE APTRANH.Transactiontype = 'P' 
and APTRANH.Applyto = APLINH.Vouchernumber)) - APLINH.Documentdate AS Days2Pay
FROM PFWEASLEY.APLINH APLINH
WHERE (APLINH.Vendorkey Like 'E%') AND (APLINH.Acct<>'115100000000') AND (APLINH.Documentdate Between {d '2010-01-01'} And {d '2010-01-31'}))t
GROUP BY Days2Pay

That worked! Thank you. I knew there had to be a simple answer to this one.

SBLatta
Starting Member

33 Posts

Posted - 2010-02-09 : 10:49:22

quote:
Originally posted by webfred

And your "subquery" is known as "derived table".

I'll keep that in mind. I'm teaching myself this stuff as I go.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 11:01:07

It is ok I don't wanna be a wisenheimer

I mentioned it only because sometimes it is easier to understand a problem if the things are named correct.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 11:27:29

quote:

Originally posted by SBLatta

quote:

Originally posted by visakh16

you should give it an alias


Select Days2Pay, Count(*)
FROM
(SELECT DISTINCT APLINH.Vouchernumber, COALESCE((SELECT APTRAN.Documentdate from PFWEASLEY.APTRAN APTRAN WHERE APTRAN.Transactiontype = 'P' 
and APTRAN.Applyto = APLINH.Vouchernumber), (SELECT APTRANH.Documentdate from PFWEASLEY.APTRANH APTRANH WHERE APTRANH.Transactiontype = 'P' 
and APTRANH.Applyto = APLINH.Vouchernumber)) - APLINH.Documentdate AS Days2Pay
FROM PFWEASLEY.APLINH APLINH
WHERE (APLINH.Vendorkey Like 'E%') AND (APLINH.Acct<>'115100000000') AND (APLINH.Documentdate Between {d '2010-01-01'} And {d '2010-01-31'}))t
GROUP BY Days2Pay

That worked! Thank you. I knew there had to be a simple answer to this one.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources