SQL syntax help (E9)

Epicor ERP 9
1

Epicor version 9.05.701

I’m running a report in excel, the below SQL syntax shows me ALL companies with warehouse codes ‘LOAN’, ‘T1’, ‘T3’, ‘T5’, ‘T6’, ‘T900’
But lets supposing there are 4 companies, A,B,C and D…how can i re-write the WHERE clause to exclude the loan warehouse from just company C?

Many thanks in advance.

SELECT PartBin.Company, PartBin.PartNum, PartBin.BinNum, PartBin.WarehouseCode, PartBin.OnhandQty, PartCost.StdBurdenCost, PartCost.StdLaborCost, PartCost.StdMaterialCost, PartCost.StdMtlBurCost, PartCost.StdSubContCost

FROM x.dbo.PartBin PartBin, x.dbo.PartCost PartCost

WHERE PartCost.Company = PartBin.Company AND PartCost.PartNum = PartBin.PartNum
AND partbin.WarehouseCode IN (‘LOAN’, ‘T1’, ‘T3’, ‘T5’, ‘T6’, ‘T900’)

2

SELECT PartBin.Company, PartBin.PartNum, PartBin.BinNum, PartBin.WarehouseCode, PartBin.OnhandQty, PartCost.StdBurdenCost, PartCost.StdLaborCost, PartCost.StdMaterialCost, PartCost.StdMtlBurCost, PartCost.StdSubContCost

FROM x.dbo.PartBin PartBin, x.dbo.PartCost PartCost

WHERE PartCost.Company = PartBin.Company AND PartCost.PartNum = PartBin.PartNum
AND partbin.WarehouseCode IN (‘LOAN’, ‘T1’, ‘T3’, ‘T5’, ‘T6’, ‘T900’) AND PartBin.Company NOT IN (‘C’)

3

WHERE PartCost.Company = PartBin.Company AND PartCost.PartNum = PartBin.PartNum
AND (partbin.WarehouseCode IN (‘T1’, ‘T3’, ‘T5’, ‘T6’, ‘T900’) OR(partbin.warehousecode = ‘LOAN’ and partbin.company <> ‘C’) )
Regards
Mark

4

Hi,
Thanks for your quick response.

Your syntax excluded the entire company C…i need it to just exclude the loan warehouse from company C and show all the other warehouses

6

Hi
That didn’t quite work after testing, (i actually thought it had)

7

so, just so theres no misunderstanding

company A, B, and D should show all the warehouses listed
company C should show all warehouses excluding loan

8

What you describe there is what I imagine by code will produce. What was added/missing from the results of my code?

9

Hi,
The loan warehouse still appeared for company C

10

I’ll give it a try…

WHERE PartCost.Company = PartBin.Company AND PartCost.PartNum = PartBin.PartNum
AND partbin.WarehouseCode IN (‘LOAN’, ‘T1’, ‘T3’, ‘T5’, ‘T6’, ‘T900’)
AND NOT (partbin.WarehouseCode = ‘LOAN’ AND PartBin.Company = ‘C’)

2 Likes
11

SELECT PartBin.Company, PartBin.PartNum, PartBin.BinNum, PartBin.WarehouseCode, PartBin.OnhandQty, PartCost.StdBurdenCost, PartCost.StdLaborCost, PartCost.StdMaterialCost, PartCost.StdMtlBurCost, PartCost.StdSubContCost

FROM x.dbo.PartBin PartBin, x.dbo.PartCost PartCost

WHERE PartCost.Company = PartBin.Company AND PartCost.PartNum = PartBin.PartNum
AND partbin.WarehouseCode IN (‘LOAN’, ‘T1’, ‘T3’, ‘T5’, ‘T6’, ‘T900’) and not (partbin.WarehouseCode = ‘LOAN’ AND partbin.Company = ‘C’)

1 Like
12

think that nailed it pal
thank you

13

Checked my code and it’s all good:

Demo table mirroring what you have:
[/uploads/default/original/2X/e/ea06a774a9a0b8ced8e540be9b073929f33ce077.png]

Then with my query conditions:
[/uploads/default/original/2X/3/3a6c7826ad6cd4329be7a3f12bde51fb4898b04e.png]

Did you type it out or copy/paste? The brackets are important to make it work.

Regards
Mark

14

Mark - I was curious and checked yours too. I’ll second that he probably missed or misplaced a set of brackets.