Column or expression in SELECT list not valid

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 2012 Forums

Transact-SQL (2012)

Column or expression in SELECT list not valid

Author

Topic

donalejandro61
Starting Member

4 Posts

Posted - 2013-03-08 : 15:40:59

This is a SQL question and I am running out of ideas I need help from a pro, below is my SQL statement what I am trying to do is to get amount totals by month, the field IDSHPD is a date field that will show as follows for example "20130101" What I am getting is by the day along with the month I just want by the month only. If I take out IDSHPD I get and error message "Column or Expression in SELECT list not valid" Can anybody give me a work around solution. I am a newbie. Thank you in advance.

SELECT CASE SUBSTR(IDSHPD, 5, 2)
WHEN 01 THEN 'Jan' WHEN 02 THEN 'Feb' WHEN 03 THEN 'Mar' WHEN 04 THEN 'Apr' WHEN 05 THEN 'May' WHEN 06 THEN 'May' WHEN 06 THEN 'Jun' WHEN 07
THEN 'Jul' WHEN 08 THEN 'Aug' WHEN 09 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE '' END AS "Month",
SUBSTR(IDSHPD, 5, 2) AS Mo, SUBSTR(IDSHPD, 7, 2) AS "Day", SUBSTR(IDSHPD, 1, 4) AS "Year", SUM(IDAMTP) AS Amount, IDSHPD
FROM MEMDTANNA.INSDTL
WHERE (SUBSTR(IDSHPD, 1, 4) = "YEAR"(CURRENT_DATE))
GROUP BY SUBSTR(IDSHPD, 1, 4), SUBSTR(IDSHPD, 5, 2), IDSHPD, SUBSTR(IDSHPD, 7, 2)
ORDER BY IDSHPD

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 16:00:14

Are you using Microsoft SQL Server? Some of the syntax doesn't seem like Microsoft T-SQL.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 16:04:28

The following should be similar to your query, except, I made a few changes to make it more robust.

SELECT LEFT(DATENAME(month, IDSHPD),3)  AS "Month",
       MONTH(IDSHPD)                     AS Mo,
       DAY(IDSHPD)                       AS "Day",
       YEAR(IDSHPD)                      AS "Year",
       SUM(IDAMTP)                       AS Amount,
       IDSHPD
FROM   MEMDTANNA.INSDTL
WHERE  IDSHPD >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
       AND IDSHPD < DATEADD(yy, 1 + DATEDIFF(yy, 0, GETDATE()), 0)
GROUP BY
       LEFT(DATENAME(MONTH, IDSHPD), 3),
       MONTH(IDSHPD),
       DAY(IDSHPD),
       YEAR(IDSHPD)
ORDER BY
       YEAR(IDSHPD),MONTH(IDSHPD),DAY(IDSHPD)

If you want to group by month, remove the DAY as in

SELECT LEFT(DATENAME(month, IDSHPD),3)  AS "Month",
       MONTH(IDSHPD)                     AS Mo,
       YEAR(IDSHPD)                      AS "Year",
       SUM(IDAMTP)                       AS Amount,
       IDSHPD
FROM   MEMDTANNA.INSDTL
WHERE  IDSHPD >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
       AND IDSHPD < DATEADD(yy, 1 + DATEDIFF(yy, 0, GETDATE()), 0)
GROUP BY
       LEFT(DATENAME(MONTH, IDSHPD), 3),
       MONTH(IDSHPD),
       YEAR(IDSHPD)
ORDER BY
       YEAR(IDSHPD),MONTH(IDSHPD)

donalejandro61
Starting Member

4 Posts

Posted - 2013-03-08 : 17:13:34

Thanks James, the SQL is based on DB2 tables using Visual Studios I will give this a shot and make any changes if needed. I appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-09 : 01:02:38

this is ms sql server forum. So solutions posted here are guaranteed to work only in sql server. If you're using db2, try your luck at db2 forum in www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources