What is wrong with this code? - 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

General SQL Server Forums

New to SQL Server Programming

What is wrong with this code?

Author

Topic

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 09:56:11

Code that breaks:

SELECT TEST.BUDGET_ENTITY, 
TEST.TITLE_L_BE, 
TEST.SPGM_STATE_STD, 
TEST.SPGM_TITLE, 
TEST.CATEGORY, 
TEST.TITLE_L_CAT, 
TEST.OCA, 
TEST.TITLE_L_OCA, 
TEST.ACCOUNT_CODE_29, 
SUM(TEST.APPROVED_BUDGET)
FROM IDS.TEST TEST
GROUP BY TEST.BUDGET_ENTITY, 
TEST.TITLE_L_BE, 
TEST.SPGM_STATE_STD, 
TEST.SPGM_TITLE, 
TEST.CATEGORY, 
TEST.TITLE_L_CAT, 
TEST.OCA, 
TEST.TITLE_L_OCA, 
TEST.ACCOUNT_CODE_29
HAVING (TEST.BUDGET_PART='1') AND 
(TEST.CATEGORY='010000') AND 
(TEST.STATE_FISCAL_YEAR='2015') AND 
(TEST.APPROVED_BUDGET<>0) AND 
(TEST.ACCOUNT_CODE_29 NOT LIKE '___________________00%') AND 
(TEST.MGDT<={ts '2015-03-31 00:00:00'})

Code that works (but doesn’t aggregate the sum of APPROVED_BUDGET, which I need):

SELECT TEST.BUDGET_ENTITY, 
TEST.TITLE_L_BE, 
TEST.SPGM_STATE_STD, 
TEST.SPGM_TITLE, 
TEST.CATEGORY, 
TEST.TITLE_L_CAT, 
TEST.OCA, 
TEST.TITLE_L_OCA, 
TEST.ACCOUNT_CODE_29, 
TEST.APPROVED_BUDGET
FROM IDS.TEST TEST
WHERE (TEST.BUDGET_PART='1') AND 
(TEST.CATEGORY='010000') AND 
(TEST.STATE_FISCAL_YEAR='2015') AND 
(TEST.APPROVED_BUDGET<>0) AND 
(TEST.ACCOUNT_CODE_29 NOT LIKE '___________________00%') AND 
(TEST.MGDT<={ts '2015-03-31 00:00:00'})

After two hours I am tired of looking at it. Someone please feel free to tell me everything I am doing wrong here.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-14 : 10:11:39

What is the error message you are getting?

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 10:16:46

ODBC Error

I cannot access the database directly, but can write the code and send it to the interpreter that gives me a result. As such, I only get a general error.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-14 : 10:21:30

Logically, HAVING clause is evaluated after the GROUP BY clause - which means, HAVING will have access only to those columns that have been grouped. Try using the WHERE clause instead like shown below:

SELECT  TEST.BUDGET_ENTITY ,
        TEST.TITLE_L_BE ,
        TEST.SPGM_STATE_STD ,
        TEST.SPGM_TITLE ,
        TEST.CATEGORY ,
        TEST.TITLE_L_CAT ,
        TEST.OCA ,
        TEST.TITLE_L_OCA ,
        TEST.ACCOUNT_CODE_29 ,
        SUM(TEST.APPROVED_BUDGET)
FROM    IDS.TEST TEST
WHERE  ( TEST.BUDGET_PART = '1' )
        AND ( TEST.CATEGORY = '010000' )
        AND ( TEST.STATE_FISCAL_YEAR = '2015' )
        AND ( TEST.APPROVED_BUDGET <> 0 )
        AND ( TEST.ACCOUNT_CODE_29 NOT LIKE '___________________00%' )
        AND ( TEST.MGDT <= {ts '2015-03-31 00:00:00'} )
GROUP BY TEST.BUDGET_ENTITY ,
        TEST.TITLE_L_BE ,
        TEST.SPGM_STATE_STD ,
        TEST.SPGM_TITLE ,
        TEST.CATEGORY ,
        TEST.TITLE_L_CAT ,
        TEST.OCA ,
        TEST.TITLE_L_OCA ,
        TEST.ACCOUNT_CODE_29

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 10:25:12

That was it!

So, order was what was getting me, rather than content.

Thank you.

EDIT

Now it's not completely grouping them. It is only partially grouping them together. I still have 2-3 entries for each set. But, at least I have a starting point.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-14 : 10:41:10

You will have one row for each unique combination of columns in the GROUP by clause. Look at one example where you are getting two or three rows, and determine which column(s) are causing the non-uniqueness. You will either need to remove those columns from the GROUP BY clause (and the SELECT clause), or wrap those columns in an aggregate function such as SUM, MAX etc.

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 10:56:26

How can I grab part of a field?

Explanation: my ACCOUNT_CODE_29 has 29 digits in it, but I only need two of them located in the 20th and 21st positions. The other 27 digits are differentiated between entries, which is causing the separation.

I tried the following, but am getting an error again.

SELECT TEST.BUDGET_ENTITY, 
TEST.TITLE_L_BE, 
TEST.SPGM_STATE_STD, 
TEST.SPGM_TITLE, 
TEST.CATEGORY, 
TEST.TITLE_L_CAT, 
TEST.OCA, 
TEST.TITLE_L_OCA, 
SUBSTRING(TEST.ACCOUNT_CODE_29, 20, 2), 
SUM(TEST.APPROVED_BUDGET)
 FROM IDS.TEST TEST
 WHERE (TEST.BUDGET_PART='1') AND 
(TEST.CATEGORY='010000') AND 
(TEST.STATE_FISCAL_YEAR='2015') AND 
(TEST.APPROVED_BUDGET<>0) AND 
(TEST.ACCOUNT_CODE_29 NOT LIKE '___________________00%') AND 
(TEST.MGDT<={ts '2015-03-31 00:00:00'})
 GROUP BY TEST.BUDGET_ENTITY, 
TEST.TITLE_L_BE, 
TEST.SPGM_STATE_STD, 
TEST.SPGM_TITLE, 
TEST.CATEGORY, 
TEST.TITLE_L_CAT, 
TEST.OCA, 
TEST.TITLE_L_OCA, 
TEST.ACCOUNT_CODE_29

If I remove the SUBSTRING part, it works fine, but differentiates.

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 11:12:29

How would the code go to pull a set of data using the WHERE filters, selecting the specific characters I need from ACCOUNT_CODE_29, THEN grouping it together and take the SUM of the APPROVED_BUDGET field?

I am not THAT familiar with SQL.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-14 : 11:58:11

If you change the SELECT list, you should change the GROUP BY clause to match that. So you would need to group by the substring as in:

SELECT  TEST.BUDGET_ENTITY ,
        TEST.TITLE_L_BE ,
        TEST.SPGM_STATE_STD ,
        TEST.SPGM_TITLE ,
        TEST.CATEGORY ,
        TEST.TITLE_L_CAT ,
        TEST.OCA ,
        TEST.TITLE_L_OCA ,
        SUBSTRING(TEST.ACCOUNT_CODE_29, 20, 2) ,
        SUM(TEST.APPROVED_BUDGET)
FROM    IDS.TEST TEST
WHERE   ( TEST.BUDGET_PART = '1' )
        AND ( TEST.CATEGORY = '010000' )
        AND ( TEST.STATE_FISCAL_YEAR = '2015' )
        AND ( TEST.APPROVED_BUDGET <> 0 )
        AND ( TEST.ACCOUNT_CODE_29 NOT LIKE '___________________00%' )
        AND ( TEST.MGDT <= {ts '2015-03-31 00:00:00'} )
GROUP BY TEST.BUDGET_ENTITY ,
        TEST.TITLE_L_BE ,
        TEST.SPGM_STATE_STD ,
        TEST.SPGM_TITLE ,
        TEST.CATEGORY ,
        TEST.TITLE_L_CAT ,
        TEST.OCA ,
        TEST.TITLE_L_OCA ,
        SUBSTRING(TEST.ACCOUNT_CODE_29, 20, 2)

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 12:06:05

I tried it (both before you suggested but again after you did so), but it doesn't seem to want to work. Same general ODBC error.

I may have to throw the results of the output through a pivot table to aggregate the results, but I had hoped for an SQL solution.

EDIT

I tried taking out everything but the basics and it doesn't seem to like the SUBSTRING command.

EDIT again

It also doesn't like MID or a combination of LEFT or RIGHT.

I know LTRIM and RTRIM work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-14 : 12:17:48

DO NOT INVOKE ODBC AT ALL.

AND ( TEST.MGDT <= {ts '2015-03-31 00:00:00'} )
AND ( TEST.MGDT <= '20150331 00:00:00' )

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 12:32:14

I don't have a choice. I have to pull this all through (and into) Excel, which requires the ODBC layer.

Trust me, if I had access to the server this wouldn't be an issue, but I do not. I have to 'get creative' using Excel.

On the bright news side, I have gotten REALLY good at writing Visual Basic code to create dynamic SQL queries.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-14 : 12:52:51

Without access to the server, we are really groping in the dark - it would help if you can get access to a server in a dev environment. Assuming it is SQL Server, SUBSTRING should work. Try

SUBSTRING(CAST(TEST.ACCOUNT_CODE_29 AS VARCHAR(MAX)), 20, 2)

instead of

SUBSTRING(TEST.ACCOUNT_CODE_29, 20, 2)

Fixer
Starting Member

8 Posts

Posted - 2015-04-14 : 15:21:09

Unfortunately, that did not work either.

I ended up pushing the results through a pivot table to get my desired results. I appreciate everyone's efforts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources