Sentence Where it's not working!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Sentence Where it's not working!

Sentence Where it's not working!

Rate Topic

Display Mode

Topic Options

Author

Message

saph

Posted Tuesday, January 08, 2008 8:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 29, 2009 4:36 PM
Points: 3, Visits: 8

Well guys. Hi Cool

I hope anyone can help me, thnks anyway.
I have this script, but the results are not what I want!
The sentence Where is not working how I want (the probable thing it's that I'm using wrong the T-SQL!!!!), and it's not filtering by the column COBRANZA.RUT_CLIE !!!

The Script (pretend ???) give a list of clients (COBRANZA.RUT_CLIE), to who applies some status and codes (SCEXTM00.COD_MOV & COBRANZA.ESTADO)

And even with all this, it show me other clients!!!!! not what I need!
What's wrong? Here is the script:

SELECT
	COBRANZA.NUM_CBZ, COBRANZA.RUT_CLIE, CLIENTES.NOM_FAC, COBRANZA.RUT_DEUD, 
	CONVERT( NUMERIC(17,0), SUM(COBRANZA.CAPITAL - COBRANZA.MONTO_ABP)) 'SALDO',
	CONVERT( VARCHAR( 10), DOCTOS.FEC_VCTO, 103) 'FECHA ENTREGA', 
	DATEDIFF(mm, DOCTOS.FEC_VCTO,  COBRANZA.FEC_CREA) 'ANTIGUEDAD',
	SCEXTM00.COD_MOV, SJTMVM00.DESC_CORTA, COBRANZA.ESTADO
FROM    
	COBRANZA WITH (NOLOCK) INNER JOIN
	CLIENTES WITH (NOLOCK) ON (COBRANZA.RUT_CLIE = CLIENTES.RUT_CLIE) INNER JOIN
	DOCTOS WITH (NOLOCK) ON (COBRANZA.RUT_CLIE = DOCTOS.RUT_CLIE) INNER JOIN 
        SCEXTM00 WITH (NOLOCK) ON (COBRANZA.NUM_CBZ = SCEXTM00.NUM_CBZ AND COBRANZA.RUT_CLIE = SCEXTM00.RUT_CLIE) INNER JOIN
	SJTMVM00 WITH (NOLOCK) ON (SJTMVM00.COD_MOV = SCEXTM00.COD_MOV)
WHERE
	(COBRANZA.RUT_CLIE IN ('96551880', '84431300', '78800200', '59029900', '96543700', '77647700', '77175200', '95050000', 
		'99544300', '77137900')) AND
	YEAR( DOCTOS.FEC_VCTO) = 2007 OR
	YEAR( DOCTOS.FEC_VCTO) = 2006 OR
	YEAR( DOCTOS.FEC_VCTO) = 2005
GROUP BY
	COBRANZA.NUM_CBZ, COBRANZA.RUT_CLIE, CLIENTES.NOM_FAC, COBRANZA.RUT_DEUD, SCEXTM00.COD_MOV, SJTMVM00.DESC_CORTA, 
	COBRANZA.ESTADO,
	COBRANZA.FEC_CREA, DOCTOS.FEC_VCTO
HAVING  
	(SCEXTM00.COD_MOV IN ('945', '914', '900EXT008', '921', '900EXT045', '922', '900EXT032', '900EXT046', '923', '900EXT038')) AND 
        (COBRANZA.ESTADO IN ('VIG', 'VSC', 'JUD', 'PT', 'PTJ', 'PK', 'DK', 'DKJ', 'PKJ', 'DT', 'DTJ', 'SUS', 'SJ', 'PP', 'PPJ', 'SCJ', 'SC'))
ORDER BY
	COBRANZA.NUM_CBZ

Post #440086

Steve Jones - SSC Editor

Posted Tuesday, January 08, 2008 9:24 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

First, why do you use those values in the HAVING clause instead of the WHERE clause?

Also, it's not really clear what's not working. If you get extra rows, then you need to determine what criteria they're satisfying in the query. From the query, we can't tell you what data is being returned or not being returned. There's no way to tell.

Also, WHERE isn't an sentence. A sentence is a grammatical contruct, not a part of T-SQL. There's a WHERE clause.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #440129

GSquared

Posted Tuesday, January 08, 2008 9:35 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

The reason your Where clause isn't working as expected is the OR statements need to be in parenthesis. Add an open paren "(" before the first "YEAR" and ")" after "2005". Then it should work as desired.

The way it's written, it selects where "RUT_CLIE" is in the values selected and the year is 2007, or if the year is 2006 or the year is 2005. If the year was 2005 or 2006, it ignores the RUT_CLIE value, because of the way the OR statements are set up. Parentheses will fix that.

You could also change it to " AND YEAR(DOCTOS.FEC_VCTO) in (2007, 2006, 2005)" and get rid of the OR statements, and it would also start to work as desired. (SQL will work exactly the same for each, but the IN statement might be easier to read and maintain.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #440134

GSquared

Posted Tuesday, January 08, 2008 9:37 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Also, as per the other post here, I'd recommend moving the stuff you have in "HAVING" up to "WHERE". That will make the whole thing a little bit faster.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #440136

saph

Posted Tuesday, January 08, 2008 9:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 29, 2009 4:36 PM
Points: 3, Visits: 8

I did the query in the Enterprice Manager... Not know SQL, not the level that i'd like to know.
Don't know what for the apps put the values in HAVING clause instead of the WHERE clause.

thnks anyway Steve Jones, I'm going to check out the script.
:D

Post #440141

saph

Posted Tuesday, January 08, 2008 9:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 29, 2009 4:36 PM
Points: 3, Visits: 8

thnks GSquared, going to check what you wrote too.
:)

Post #440146

« Prev Topic | Next Topic »

Permissions