SQL Statement Help 1

[RonRepp]

Hi all:

I have a SQL statement that is pulling more data than I want. Here is the statement:

SELECT COMPANY AS Co, DIVISION AS Div, FIN_TRANS_DT, TRANSMITTAL_NR AS TransNum, AMOUNTS_1 / 100 AS Debit, AMOUNTS_2 / 100 AS Credit From dbo.SV_MACORD_FT_SHIP WHERE (COMPANY = '62') AND (DIVISION = '00') OR (COMPANY = '62') AND (DIVISION = '01') OR (COMPANY = '62') AND (DIVISION = '02') OR (COMPANY = '62') AND (DIVISION = '03') OR (COMPANY = '62') AND (DIVISION = '04') AND (TRANSMITTAL_NR = '01980000') ORDER BY TRANSMITTAL_NR, COMPANY, DIVISION, FIN_TRANS_DT

The problem is that, in my opinion, it should only be pulling one transmittal number (TRANSMITTAL_NR in the string). Instead, it is pulling all transmittals for the companies.

The string is concatenated through a VB program where the user has choices. i.e., they choose a certain company, which whittles it down to a certain amount of transmittal numbers. This works as advertised. Even the final result is a SQL statement that I thought would bring the correct results, but it doesn't.

Any help will be greatly appreciated.

Thanks,

Ron

 

[JamesLean]

I think your ANDs and ORs (plus lack of parentheses) are causing this problem. Try this:

WHERE company = '62'
  AND division IN ('00', '01', '02', '03', '04')
  AND transmittal_nr = '01980000'

--James

 

[Bajwa11]

You say it is pulling back more than one transmittal number (TRANSMITTAL_NR )
are there multiple records with transmittal number (TRANSMITTAL_NR ) number as '01980000' ??


SELECT Distinct
COMPANY AS Co,
DIVISION AS Div,
FIN_TRANS_DT,
TRANSMITTAL_NR AS TransNum,
AMOUNTS_1 / 100 AS Debit,
AMOUNTS_2 / 100 AS Credit
From dbo.SV_MACORD_FT_SHIP WHERE
(COMPANY = '62')
AND (DIVISION IN '00','01','02','03','04')
AND (TRANSMITTAL_NR = '01980000')
ORDER BY TRANSMITTAL_NR, COMPANY, DIVISION, FIN_TRANS_DT



[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[RonRepp]

Thanks James,

It worked great. Now all I have to do is figure out how to pull this string together.

Ron