Cannot call methods on money - 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)

Cannot call methods on money

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-19 : 10:29:48

Having an issue when trying to run the following, i get cannot call mehods on money, the part of the query that really builds that message is in green


SELECT TOP10.SICDESCRIPTION,
       TOP10.TOTALACCOUNTS,
       TOP10.ACCOUNTMV,
       TOP10.ACCOUNTFEELY,
       TOP10.SICID,
       TOP10.RNUMBER
FROM   (SELECT   TOP 10 SICDESCRIPTION,
                        COUNT(* ) AS [TOTALACCOUNTS],
                        SUM(ACCOUNTMV) AS [ACCOUNTMV],
                        SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                        SICID,
                        COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    (BRANCHSTATE = 'Arizona')
                 AND SICDESCRIPTION IS NOT NULL
                 AND (MONTHEND = '06/01/2007')
                 AND SICID <> '00'
        GROUP BY SICID,SICDESCRIPTION,MONTHEND
        ORDER BY ACCOUNTMV DESC) AS TOP10
UNION 
SELECT OTHER.SICDESCRIPTION,
       OTHER.TOTALACCOUNTS,
       OTHER.ACCOUNTMV.OTHER.ACCOUNTFEELY,
       OTHER.SICID,
       OTHER.RNUMBER
FROM   (SELECT   TOP 100 PERCENT SICDESCRIPTION,
                                 COUNT(* ) AS [TOTALACCOUNTS],
                                 SUM(ACCOUNTMV) AS [ACCOUNTMV],
                                 SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
                                 SICID,
                                 COUNT(DISTINCT RELATIONSHIPID) AS 'rnumber'
        FROM     SNAPSRAW
        WHERE    SICDESCRIPTION NOT IN (SELECT   TOP 10 SICDESCRIPTION
                                        FROM     SNAPSRAW
                                        WHERE    (BRANCHSTATE = 'Arizona')
                                                 AND SICDESCRIPTION IS NOT NULL
                                                 AND (MONTHEND = '06/01/2007')
                                                 AND SICID <> '00'
                                        GROUP BY SICID,SICDESCRIPTION,MONTHEND)
        GROUP BY SICDESCRIPTION,SICID
        ORDER BY ACCOUNTMV DESC) AS OTHER

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-07-19 : 12:26:00

(1)What method(s) are you referring to
(2)What error(s) are you getting?

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-19 : 12:41:40

I guess thats my problem, when I run the query in green its giving me the message

Msg 258, Level 15, State 1, Line 13
Cannot call methods on money.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 13:16:34

Do your SQL statements work independently without the UNION? It should have given you an error about using ORDER BY in a subquery.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-19 : 13:20:31

top one does work fine, one in green does not, spits out that message

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 13:52:43

I have never seen an error like that. Start with the innermost subquery "SELECT TOP 10 SICDESCRIPTION.." and work it backwards to identify xactly which part of the query is breaking the code.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-19 : 15:18:55

It appears when I remove
OTHER.ACCOUNTMV.OTHER.ACCOUNTFEELY
it runs, doesnt seem to like those two fields that have a money type, something weird with a money type field?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 15:22:03

Its not the data type. What are you trying to do with those 2 columns? concatenate? multiply?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-19 : 15:33:29

quote:
Originally posted by duhaas

It appears when I remove
OTHER.ACCOUNTMV.OTHER.ACCOUNTFEELY
it runs, doesnt seem to like those two fields that have a money type, something weird with a money type field?

OTHER.ACCOUNTMV.OTHER.ACCOUNTFEELY is invalid syntax.

Is that supposed to represent 2 different columns? Are you trying to add them together?

CODO ERGO SUM

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-19 : 16:12:09

sorry it was a typo, just trying to lay things out in my head right now, let me get back to everyone, appreciate the time spent looking so far. i am essentially trying to tally up the top10 and than sum up the rest as other to show up in a chart.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-07-20 : 10:42:01

quote:
Originally posted by duhaas

sorry it was a typo, just trying to lay things out in my head right now, let me get back to everyone, appreciate the time spent looking so far. i am essentially trying to tally up the top10 and than sum up the rest as other to show up in a chart.

Im an idiot, thanks Mike for pointing out that I did have a syntax error in my query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources