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.
| Author |
Topic |
|
FData
Starting Member
24 Posts |
 Posted - 2012-09-04 : 06:02:37
|
| Hey guys i believe i wrote my query correct, however i am receiving the following error msg Incorrect syntax near 'sub'.can anyone help me My query is select ParentID, SUM(Gross_Sales) as Sales_Annualised Into #RM--drop table #RM--select * from #RM from [FDMS].[dbo].[Dim_Outlet] o inner join [FDMS].[dbo].[Fact_Financial_History_Annualised] fh on o.FDMSAccountNo = fh.FDMSAccountNo group by ParentID having SUM (fh.Gross_Sales) > 1999999 order by SUM(Gross_Sales) desc select rm.ParentID, Dba_Name, o.Post_Code, o.HO, o.LBG_Account, o.LBG_Status, o.Account_Status, Sales_Annualised, CASE WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN LEFT(post_code, 2) ELSE LEFT(post_code, 1) END AS 'sPostcode' From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID where LBG_Status ='N/A' and Account_Status ='16' AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations) sub INNER JOIN [geo_pca_sellers] ON [pca] = spostcode select * from #RM |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-04 : 06:28:02
|
quote:
Originally posted by FData
Hey guys i believe i wrote my query correct, however i am receiving the following error msg Incorrect syntax near 'sub'.can anyone help me My query is select ParentID, SUM(Gross_Sales) as Sales_Annualised Into #RM--drop table #RM--select * from #RM from [FDMS].[dbo].[Dim_Outlet] o inner join [FDMS].[dbo].[Fact_Financial_History_Annualised] fh on o.FDMSAccountNo = fh.FDMSAccountNo group by ParentID having SUM (fh.Gross_Sales) > 1999999 order by SUM(Gross_Sales) desc select rm.ParentID, Dba_Name, o.Post_Code, o.HO, o.LBG_Account, o.LBG_Status, o.Account_Status, Sales_Annualised, CASE WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN LEFT(post_code, 2) ELSE LEFT(post_code, 1) END AS 'sPostcode' From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID where LBG_Status ='N/A' and Account_Status ='16' AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations) sub INNER JOIN [geo_pca_sellers] ON [pca] = spostcode select * from #RM
Too old to Rock'n'Roll too young to die. |
 |
|
|
FData
Starting Member
24 Posts |
Posted - 2012-09-04 : 06:45:07
|
| Hi When i remove the sub, i get this error msg Msg 156, Level 15, State 1, Line 39Incorrect syntax near the keyword 'INNER'. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-04 : 06:49:56
|
Yes that's the next problem.I don't know where you wanted to do that inner join... and on what...
Too old to Rock'n'Roll too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-09-04 : 06:52:56
|
| select rm.ParentID,Dba_Name,o.Post_Code,o.HO,o.LBG_Account,o.LBG_Status,o.Account_Status,Sales_Annualised,CASE WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN LEFT(post_code, 2) ELSE LEFT(post_code, 1) END AS 'sPostcode'From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID INNER JOIN [geo_pca_sellers] ON [pca] = spostcode where LBG_Status ='N/A'and Account_Status ='16'AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)MadhivananFailing to plan is Planning to fail |
|
|
|
FData
Starting Member
24 Posts |
Posted - 2012-09-04 : 07:11:20
|
| Hi Madivanan Thank you for your post, i have changed the query to select ParentID, SUM(Gross_Sales) as Sales_Annualised Into #RM--drop table #RM--select * from #RM from [FDMS].[dbo].[Dim_Outlet] o inner join [FDMS].[dbo].[Fact_Financial_History_Annualised] fh on o.FDMSAccountNo = fh.FDMSAccountNo group by ParentID having SUM (fh.Gross_Sales) > 1999999 order by SUM(Gross_Sales) desc select rm.ParentID,Dba_Name,o.Post_Code,o.HO,o.LBG_Account,o.LBG_Status,o.Account_Status,Sales_Annualised,CASE WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN LEFT(post_code, 2) ELSE LEFT(post_code, 1) END AS 'NewPostcode'From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID INNER JOIN [geo_pca_sellers] ON [pca] = 'NewPostcode'where LBG_Status ='N/A'and Account_Status ='16'AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations) select * from #RM However its producing results like this http://s16.postimage.org/unqivc1ut/query.jpgany ideas ? |
|
|
|
|
|
|
|
|
Incorrect syntax near 'sub'.