Squeary in Queary(Sub queary) syntax missing

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

General SQL Server Forums

New to SQL Server Programming

Squeary in Queary(Sub queary) syntax missing

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-22 : 05:39:07

Dear All,

I tried following queary in that s queary output getting properly. Now i want AbsId related that s queary name. I am getting error following error. I think sytax wrong.
Can anybody plz correct the syntax or solve it.

Queary:

select AbsId from ostt where
s.Name from
(
select I4.DocEntry,I4.TaxRate,ot.NfTaxId,oa.Name,sum(I4.TaxSum) as taxamount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by I4.DocEntry,I4.TaxRate,ot.NfTaxId,oa.Name
)s

Error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 's'.

Harish Patil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 05:43:40

[code]SELECT DISTINCT ot.AbsID
FROM inv4 AS i4
LEFT JOIN ostt AS ot ON ot.AbsId = i4.StaType
LEFT JOIN osta AS oa ON oa.Code = i4.StaCode
WHERE i4.DocEntry = '91'
GROUP BY i4.DocEntry,
i4.TaxRate,
ot.NfTaxId,
oa.Name[/code]

E 12°55'05.63"
N 56°04'39.26"

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-22 : 06:17:02

Dear peso,

In my previous queary in s queary i used group by for Name also. I want related that Name want AbsId. And this AbsId is unique.

91 1.000000 -50 HCESS 306.060000
91 1.000000 -3 OCT_SER 106.560000
91 2.000000 -50 ECESS 612.120000
91 3.000000 -3 OCTROI 10655.430000
91 4.000000 -7 VAT_4 14091.300000
91 10.000000 -50 Excise 30605.940000

Now i want following output,

7 HCESS 306.060000
8 OCT_SER 106.560000
9 ECESS 612.120000
10 OCTROI 10655.430000
11 VAT_4 14091.300000
12 Excise 30605.940000

Means i want AbsId only related that name.

Harish Patil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:20:42

Try following these simple suggestions and we may understand your requirements.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 06:28:36

You're not being very clear about what you want or the structure of your tables but looks like you just want AbsId, Name, taxamount so maybe

select ostt.AbsId, oa.Name, sum(I4.TaxSum) as taxamount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by ostt.AbsId, oa.Name

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-22 : 06:35:26

Dear freind,

U r getting correct my problem. It is work. But in this i want grouping by Name but in that AbsId is different. Plz see the following output of ur queary.

8 ECESS 612.120000
7 Excise 30605.940000
9 HCESS 306.060000
11 OCT_SER 105.330000
14 OCT_SER 1.230000
10 OCTROI 10532.600000
13 OCTROI 122.830000
12 VAT_4 13928.860000
15 VAT_4 162.440000

And ur queary is as follows. Plz tell me how to do grouping by name.

select ot.AbsId, oa.Name, sum(I4.TaxSum) as taxamount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by ot.AbsId, oa.Name

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:48:11

select MAX(ot.AbsId), oa.Name, sum(I4.TaxSum) as taxamount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by oa.Name

E 12°55'05.63"
N 56°04'39.26"

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-22 : 07:57:08

Dear Peso,

Tnks for the reply. Can u plz send me ur mail id so i can send u my deatils queary to u. Coz i am not able to send the attachement through sqlteam forum.

Harish Patil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources