SQL Error - 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

SQL Error

Author

Topic

zj_sql_user
Starting Member

1 Post

Posted - 2015-04-30 : 08:40:56

Could you please help m finding bugs with the following query:

SELECT COUNT(*) FROM LADRESSE_EMAIL_INSTANCE_EMAIL WHERE GROUPE_VALEURID!=NULL;

SELECT
AE.EMAIL,
VCOMPTE.VALEUR NUMEROCOMPTE,
VCONTACT.VALEUR NUMEROCONTACT,
COUNT(PJ.ID) NBPJ,
SUM(ISNULL(PJ.TAILLE,0)) TAILLEPJ,
LAEIE.ERREUR,
T.NOM THEME,
M.NOM MODELE,
CONVERT(DATETIME,IE.DATE_EMISSION,103) as DATE_EMISSION
FROM
INSTANCE_EMAIL IE
INNER JOIN
UTILISATEUR U
ON
IE.UTILISATEURID=U.ID
INNER JOIN
LADRESSE_EMAIL_INSTANCE_EMAIL LAEIE
ON
IE.ID=LAEIE.INSTANCE_EMAILID
INNER JOIN
ADRESSE_EMAIL AE
ON
AE.ID=LAEIE.ADRESSE_EMAILID
LEFT OUTER JOIN
VALEUR VCOMPTE
ON
VCOMPTE.GROUPE_VALEURID=LAEIE.GROUPE_VALEURID AND VCOMPTE.ELEMENTFORMULAIREID=3
LEFT OUTER JOIN
VALEUR VCONTACT
ON
VCONTACT.GROUPE_VALEURID=LAEIE.GROUPE_VALEURID AND VCONTACT.ELEMENTFORMULAIREID=4
LEFT OUTER JOIN
PIECEJOINTE PJ
ON
PJ.INSTANCE_EMAILID=IE.ID
INNER JOIN
MODELE M
ON
M.ID=IE.MODELEID
INNER JOIN
THEMATIQUE T
ON
T.ID=M.THEMATIQUEID
WHERE
IE.DATE_EMISSION IS NOT NULL AND
'01/01/2009'<=IE.DATE_EMISSION
HAVING
COUNT(PJ.ID)>0
GROUP BY
LAEIE.ADRESSE_EMAILID,
LAEIE.INSTANCE_EMAILID,
AE.EMAIL,
VCOMPTE.VALEUR,
VCONTACT.VALEUR,
LAEIE.ERREUR,
T.NOM
ORDER BY
IE.DATE_EMISSION DESC;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-30 : 08:50:10

First off, please read these posting guidelines:

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Then, repost your question, following the guidelines.

Please include any error messages you are getting!

At first glance though, there is a syntax error that's easy to spot:

HAVING needs to come after GROUP BY, so this part is wrong:


HAVING
COUNT(PJ.ID)>0
GROUP BY
LAEIE.ADRESSE_EMAILID,
...

There are probably other things, but without having the items in the posting guidelines, it will be hard to find them.

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 10:44:19

[code]
AND '01/01/2009'<=IE.DATE_EMISSION
[/code]
The date format you are using is ambiguous and will not necessarily parse as you expect (and sometimes will parse with error).

The only unambiguous format for dates is "yyyymmdd" or "yyyy-mm-ddThh:mm:ss.sss". Anything else is dependent on the Locale setting for the server (which may change) and the Language of the currently logged on users, and a whole raft of other things. So even if it works now it may stop working in the future.

I doubt that is the issue currently troubling you, but it will do in future! so worth changing habit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources