October 1, 2015 at 3:49 pm
Hello comunity
I have a small problem with a join clause, because i need to return all values from my table BL:
my code is:
SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL
)x
RIGHT JOIN ol ON x.noconta =ol.contado
WHERE
(OL.ollocal like 'CX%' or OL.ollocal = 'Caixa' OR OL.ollocal LIKE '%POS%')
AND OL.ollocal NOT LIKE ('CX%Desp%')
AND OL.origem IN ('FT','RD','RE')
and OL.data = '20151001'
and OL.ousrdata+ space(1)+OL.ousrhora > '20150928 23:23:06'
and UPPER(x.u_area) = 'FACTORY'
and x.NOCONTA IN (16,35)
In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.
How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).
Thanks for your Help.
Best Regards
Lsantos
October 1, 2015 at 4:00 pm
I think this might do it, if I understand correctly what you need:
SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL
)x
LEFT OUTER JOIN ol ON x.noconta =ol.contado
AND (OL.ollocal like 'CX%' or OL.ollocal = 'Caixa' OR OL.ollocal LIKE '%POS%')
AND OL.ollocal NOT LIKE ('CX%Desp%')
AND OL.origem IN ('FT','RD','RE')
and OL.data = '20151001'
and (OL.ousrdata > '20150928' OR (OL.ousrdata = '20150928' AND OL.ousrhora > '23:23:06'))
WHERE UPPER(x.u_area) = 'FACTORY'
and x.NOCONTA IN (16,35)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2015 at 4:10 pm
Hello Scott
Thanks, that is exactly what i need.
I need to read with more attention all the kind of JOIN, because i forgot some of them.:-)
Best regards,
LSantos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply