January 8, 2008 at 8:22 am
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
January 8, 2008 at 9:24 am
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.
January 8, 2008 at 9:35 am
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
January 8, 2008 at 9:37 am
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
January 8, 2008 at 9:46 am
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.
😀
January 8, 2008 at 9:54 am
thnks GSquared, going to check what you wrote too.
🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply