Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sentence Where it's not working!


Sentence Where it's not working!

Author
Message
saph
saph
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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


Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18742
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
My Blog: www.voiceofthedba.com
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
saph
saph
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
BigGrin
saph
saph
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
thnks GSquared, going to check what you wrote too.
Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search