Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / First Attempt at SQL -...
Microsoft Dynamics GP (Archived)

First Attempt at SQL - can't find my error

(0) ShareShare
ReportReport
Posted on by KATHRYN HILL 315

Hi, All - We don't have IT support for GP and I'm trying to practice write a practice sql query based on something wrote with Excel Report Builder but it isn't working.  My error is "Incorrect syntax near 'DYCUN'.  Can anyone spot my error?  Thanks for any help!

select  GL00201.BUDGETID  ,GL00201.ACTNUMBR_2 AS 'CC'  ,GL00201.ACTNUMBR_3 AS 'ACCT'  ,GL00201.ACTNUMBR_4 AS 'PROD'  ,GL00201.PERIODID AS 'MONTH'  ,GL00201.BUDGETAMT  ,GL00201.ACTINDX  ,GL00201.YEAR1  ,GL00100.ACTINDX  ,GL00100.ACTDESCR  ,GL00102.ACCATNUM FROM  DYCUN.dbo.GL00201  INNER JOIN GL00100   on GL00201.ACTINDX = GL00100.ACTINDX  DYCUN.dbo.GL00100  INNER JOIN GL00102   on GL00100.ACCATNUM = GL00102.ACCATNUM

where  GL00201.YEAR1 >= 2012   and GL00201.BUDGETID IN ('2013BUD'  '2014BUD'  '2015BUD')

 

*This post is locked for comments

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: First Attempt at SQL - can't find my error

    Kathryn,

    Glad we could help.  I have a series of how to blog posts on SQL fundamentals, you might find interesting:

    redbeardblogging.blogspot.com/.../how-to.html

  • KATHRYN HILL Profile Picture
    KATHRYN HILL 315 on at
    RE: First Attempt at SQL - can't find my error

    Awesome!!  Thanks so much for the help! This has helped me learn quite a lot.

  • Joshua Page Profile Picture
    Joshua Page on at
    RE: First Attempt at SQL - can't find my error

    Second Harrys script! Really nice!

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: First Attempt at SQL - can't find my error

    Kathryn -  I made a few changes to the query. I added a variable for BudgetID,  a summary for the Annual Budget amount. I linked in the GL00105 Table, which contains the Full Account Number.  In order to make this version effective, you'll likely need to remove some columns and group by the ACTINDX overall, but I wanted to leave the major components in place.

    DECLARE @BUDGETID AS varchar(15)

    SET @BUDGETID = '2015BUD'

    select  

    GL00201.BUDGETID,

    GL00201.ACTNUMBR_2 AS 'CC',

    GL00201.ACTNUMBR_3 AS 'ACCT',

    GL00201.ACTNUMBR_4 AS 'PROD',

    GL00201.PERIODID AS 'MONTH',

    GL00201.BUDGETAMT,

    GL00201.ACTINDX,

    GL00201.YEAR1,

    GL00100.ACTINDX,

    GL00100.ACTDESCR,

    GL00102.ACCATNUM,

    GL00105.ACTNUMST,

    BAMT.AMOUNT as Annual_Budget

    FROM  GL00201

    LEFT JOIN (SELECT ACTINDX,SUM(BUDGETAMT) AMOUNT

    FROM GL00201 WHERE BUDGETID IN (@BUDGETID) GROUP BY ACTINDX) BAMT ON BAMT.ACTINDX = GL00201.ACTINDX

    LEFT JOIN GL00100 on GL00201.ACTINDX = GL00100.ACTINDX  

    LEFT JOIN GL00102 on GL00100.ACCATNUM = GL00102.ACCATNUM

    LEFT JOIN GL00105 ON GL00201.ACTINDX = GL00105.ACTINDX

    where  GL00201.YEAR1 >= 2012  and GL00201.BUDGETID IN (@BUDGETID)

  • KATHRYN HILL Profile Picture
    KATHRYN HILL 315 on at
    RE: First Attempt at SQL - can't find my error

    Thank you!  That was so helpful.  Do you know if there is a way to have the query return the aggregate/summary budget amount account instead of by each period for each account?

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: First Attempt at SQL - can't find my error

    Here is the correction of your script 

    SELECT  GL00201.BUDGETID,
		GL00201.ACTNUMBR_2 AS 'CC',
		GL00201.ACTNUMBR_3 AS 'ACCT',
		GL00201.ACTNUMBR_4 AS 'PROD',
		GL00201.PERIODID AS 'MONTH',
		GL00201.BUDGETAMT,
		GL00201.ACTINDX,
		GL00201.YEAR1,
		GL00100.ACTINDX,
		GL00100.ACTDESCR,
		GL00102.ACCATNUM 
		FROM  dbo.GL00201  
		INNER JOIN GL00100   
		ON  GL00201.ACTINDX = GL00100.ACTINDX  
		INNER JOIN GL00102   
		ON GL00100.ACCATNUM = GL00102.ACCATNUM
		WHERE   GL00201.YEAR1 >= 2012   
		AND 
		GL00201.BUDGETID IN ('2013BUD','2014BUD','2015BUD')


    Your feedback is highly appreciated,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans