Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sentence Where it's not working! Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2008 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

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
Posted Tuesday, January 8, 2008 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Posted Tuesday, January 8, 2008 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Tuesday, January 8, 2008 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Tuesday, January 8, 2008 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 8, 2008 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse