r270ba

asked on 4/15/2008

SQL Statment to Join Several Tables

I need to combine several tables together and I do not know the best way to go about it. I am selecting information about sales orders and the tables I am wanting to pull from are the following:

Sales Order Header (SOP10100)
Customer Main (RM00101)------------------|---------------------Sales Order Line Items (SOP10200)
|
Customer details (RMxxxx I still have to figure out this table name)

I then want to add these where statements to it...

/*
Line Marging Check - Add check for $0.00 line items and line item margins less than 20% (from SOP10200)
Tax Percent Check - Add check for sales tax from customer tax schedule (rm00101) * document total (SOP10100)
Exmpetion Check - Add check for sales tax on tax exempt companies (RMxxx, SOP00100)
Ship To Check - Add check for ship to address id <> to '1' or 'SHIP TO' (SOP10100)
Terms Check - Add check for terms = credit card (RM00101)
*/

I have posted some code for what I have so far. I am having trouble with the Joins for this statement.

What I would like to do is run each one of the "checks" and return a bit flag to state whether or not it failed or passed for each check. I only what to return rows where there is at least one failed check. I will then use the 1 or 0 to show a green 'check' or red 'x' in each of the check columns of the SSRS Report.

Order Number Margin Percent Exemption Terms
I001234 x 'check' 'check' x
I001235 x x x 'check'
I001236 'check' 'check' x 'check'

Let me know if you have any questions.

select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname from
(select sopnumbe, docid, docdate, custnmbr, custname, bachnumb, frtamnt, taxamnt from sop10100) A
left outer join
(select custnmbr, custname from rm00101) B
on a.custnmbr = b.custnmbr
left outer join
(select sopnumbe, itemnmbr, itemdesc, uofm, unitcost, unitprce from sop10200) C
on a.sopnumbe = c.sopnumbe
/*where /*(bachnumb in(@bachnumb)) and */(a.frtamnt <= '0' or a.taxamnt <= '0' or c.unitprce <= '0') /*or ((1-(c.unitcost/unitprce))<.2)*/*/
order by sopnumbe asc

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Last Comment

r270ba

8/22/2022 - Mon

Mark Wills

4/15/2008

You weren't too far off, too many brackets and sub-queries.
Needs checking for conditions and field names, but should be something like :

select sopnumbe, docid, docdate, custnmbr, custname, bachnumb, itemnmbr, frtamnt, taxamnt, custname , check1,check2,check3,check4
 
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname ,
case when (a.frtamnt <= '0')  then 'Check' else 'x' end as check1,
case when (a.taxamnt <= '0')  then 'Check' else 'x' end as check2,
case when (c.unitprce <= '0') then 'Check' else 'x' end as check3,
case when (1-(c.unitcost/unitprce))<.2 then 'Check' else 'x' end as check4
 
from sop10100 A
left outer join rm00101 b on a.custnmbr = b.custnmbr
left outer join sop10200 c on a.sopnumbe = c.sopnumbe
) 
 
where bachnumb in(@bachnumb)
and 'Check' in (check1,check2,check3,check4)
 
order by sopnumbe asc

Open in new window

ee_rlee

4/15/2008

>Line Marging Check - Add check for $0.00 line items and line item margins less than 20% (from SOP10200)
>Tax Percent Check - Add check for sales tax from customer tax schedule (rm00101) * document total (SOP10100)
>Exmpetion Check - Add check for sales tax on tax exempt companies (RMxxx, SOP00100)
>Ship To Check - Add check for ship to address id <> to '1' or 'SHIP TO' (SOP10100)
>Terms Check - Add check for terms = credit card (RM00101)

Pls state clearly which fields these are referring to. I used the conditions you commented to do the check.

select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname 
      ,case when a.bachnumb in (@bachnumb) then 1 else 0 end as check1
      ,case when a.frtamnt <= '0' then 1 else 0 end as check2
      ,case when a.taxamnt <= '0' then 1 else 0 end as check3
      ,case when c.unitprce <= '0' then 1 else 0 end as check4
      ,case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end as check5
from sop10100 A
     left outer join rm00101 b on a.custnmbr = b.custnmbr
     left outer join sop10200 c on a.sopnumbe = c.sopnumbe
where bachnumb in (@bachnumb)
      OR a.frtamnt <= '0'
      OR a.taxamnt <= '0'
      OR c.unitprce <= '0'
      OR (1-(c.unitcost/c.unitprce))<0.2
order by a.sopnumbe asc

Open in new window

r270ba

4/15/2008

ASKER

Sorry that was not clear...

Some of these checks are not included in the Where clause I previously posted...

Line Item Margin Check - where (1-(c.unitcost/c.unitprce))<0.2)
Tax Percent Check -
(from RM00101) TaxSchedule * (from SOP10100) DocTotal <> (from SOP10100) TaxAmnt
Excemption Check - (from RM00101) ExemptStatus = true and (from SOP10100) TaxAmnt > 0
Ship To Check - where (from SOP10100) ShipTo <> 'SHIP TO' or ShipTo <> '1'
Terms Check - where (from RM00101) CustomerTerms = 'Credit Card'

After running all of the statements above I would like to have 5 fields to choose from that are either 1 or 0 for the status of the check.

Then in my SSRS report I could just use the 5 fields to say whether a "check" or "x" should icon should be placed in the cell of the table.

Order Number Margin Percent Exemption Terms
I001234 x 'check' 'check' x
I001235 x x x 'check'
I001236 'check' 'check' x 'check'

Is this any clearer?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

ee_rlee

4/15/2008

>Ship To Check - where (from SOP10100) ShipTo <> 'SHIP TO' or ShipTo <> '1'
Did you mean AND? If you use OR, it will be always true because at least one of them will be true. In the query below, I used AND for Ship To Check.

select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname 
      ,case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end as Margin
      ,case when b.TaxSchedule * a.DocTotal<>a.TaxAmnt then 1 else 0 end as [Percent]
      ,case when b.ExemptStatus = 1 and a.TaxAmnt>0 then 1 else 0 end as Excemption
      ,case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end as Ship
      ,case when b.CustomerTerms='Credit Card' then 1 else 0 end as Terms
from sop10100 a
     left outer join rm00101 b on a.custnmbr = b.custnmbr
     left outer join sop10200 c on a.sopnumbe = c.sopnumbe
) T
WHERE Margin+[Percent]+Excemption+Ship+Terms>0
order by sopnumbe asc

Open in new window

Mark Wills

4/15/2008

Couldn't have put it better myself :)

r270ba

4/15/2008

ASKER

Sorry...

The OR should be used because there are more than SHIP TO and 1 addresses. I want to make sure it is either SHIP TO or 1 but not BILL TO or some other address.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ee_rlee

4/15/2008

This would return 1 if ShipTo is not 1 or 'SHIP TO'.
,case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end as Ship

This would return 1 if ShipTo is either 1 or 'SHIP TO'.
,case when a.ShipTo IN ('SHIP TO','1') then 1 else 0 end as Ship

r270ba

4/15/2008

ASKER

This is getting close! I am receiving a Divide By Zero error on
,case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end as Margin

Sometimes we have items that will go to a sales order that we do not have a unitprce on. How can I "ignore" the divide by zero error and continue the execution of the query?

If we do divide by zero I would like to set the end as Margin to 1 just like we would if the margin was <.2

ee_rlee

4/15/2008

try

,case when c.unitprice=0 then 1 when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end as Margin

Your help has saved me hundreds of hours of internet surfing.

fblack61

r270ba

4/15/2008

ASKER

That works...and the query appears to have executed properly. The only problem now is that I cannot use the "end as fields" as fields in my dataset. The query returns them as columns but in Visual Studio I cannot select them as a field.

ee_rlee

4/15/2008

>The query returns them as columns but in Visual Studio I cannot select them as a field.
Why?

r270ba

4/15/2008

ASKER

When I go into the dataset fields in VS.net it has fields for
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt

But not for any of the end as
Margin+[Percent]+Excemption+Ship+Terms

I see the results returned in the actual query, but they do not become fields in the dataset...

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ee_rlee

4/15/2008

They are returned as fields. The field names are Margin, Percent, Excemption, Ship and Terms.

r270ba

4/15/2008

ASKER

I just checked again and they are there as fields. I am not sure why that happened but the first time I checked they were not in the dataset as fields.

My last question is this...This query returns all the line item rows for the sales order (SOP00200). Is there a way to only return one sales order row (SOP00100) even if though we are running some of the cases on the line items (SOP00200)?

ee_rlee

4/15/2008

select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, MIN(c.itemnmbr) as itemnmbr, a.frtamnt, a.taxamnt, b.custname 
      ,MAX(case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end) as Margin
      ,MAX(case when b.TaxSchedule * a.DocTotal<>a.TaxAmnt then 1 else 0 end) as [Percent]
      ,MAX(case when b.ExemptStatus = 1 and a.TaxAmnt>0 then 1 else 0 end) as Excemption
      ,MAX(case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end) as Ship
      ,MAX(case when b.CustomerTerms='Credit Card' then 1 else 0 end) as Terms
from sop10100 a
     left outer join rm00101 b on a.custnmbr = b.custnmbr
     left outer join sop10200 c on a.sopnumbe = c.sopnumbe
GROUP BY a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, a.frtamnt, a.taxamnt, b.custname 
) T
WHERE Margin+[Percent]+Excemption+Ship+Terms>0
order by sopnumbe asc

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

r270ba

4/15/2008

ASKER

This is the error I get

Msg 195, Level 15, State 10, Line 5
'MAX' is not a recognized built-in function name.

ee_rlee

4/15/2008

That's weird... How about this one?

select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, a.frtamnt, a.taxamnt, b.custname 
      ,c.Margin
      ,case when b.TaxSchedule * a.DocTotal<>a.TaxAmnt then 1 else 0 end as [Percent]
      ,case when b.ExemptStatus = 1 and a.TaxAmnt>0 then 1 else 0 end as Excemption
      ,case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end as Ship
      ,case when b.CustomerTerms='Credit Card' then 1 else 0 end as Terms
from sop10100 a
     left outer join rm00101 b on a.custnmbr = b.custnmbr
     left outer join (SELECT sopnumbe, MAX(case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end) AS Margin FROM sop10200 GROUP BY sopnumbe) c on a.sopnumbe = c.sopnumbe
) T
WHERE Margin+[Percent]+Excemption+Ship+Terms>0
order by sopnumbe asc

Open in new window

r270ba

4/16/2008

ASKER

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.unitcost" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.unitprce" could not be bound.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

r270ba

4/16/2008

ASKER

This was the query I ran (modified from what you had given me)

select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, a.frtamnt, a.taxamnt, c.Margin
      ,case when b.TAXSCHID = 1 and a.TaxAmnt>0 then 1 else 0 end as Excemption
      ,case when a.PRSTADCD NOT IN ('SHIP TO','1') then 1 else 0 end as Ship
      ,case when b.PYMTRMID='Credit Card' then 1 else 0 end as Terms
from sop10100 a
     left outer join rm00101 b on a.custnmbr = b.custnmbr
     left outer join (SELECT sopnumbe, MAX(case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end) AS Margin FROM sop10200 GROUP BY sopnumbe) c on a.sopnumbe = c.sopnumbe
) T
WHERE Margin+Excemption+Ship+Terms>0
order by sopnumbe asc