Link to home
Create AccountLog in
Avatar of r270ba
r270ba

asked on

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

Avatar of Mark Wills
Mark Wills
Flag of Australia image

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

>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

Avatar of r270ba
r270ba

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?
>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

Couldn't have put it better myself :)

Avatar of r270ba

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.
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

Avatar of r270ba

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
try

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

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.
>The query returns them as columns but in Visual Studio I cannot select them as a field.
Why?
Avatar of r270ba

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...
They are returned as fields. The field names are Margin, Percent, Excemption, Ship and Terms.
Avatar of r270ba

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)?

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

Avatar of r270ba

ASKER

This is the error I get

Msg 195, Level 15, State 10, Line 5
'MAX' is not a recognized built-in function name.
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

Avatar of r270ba

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.
Avatar of r270ba

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of r270ba

ASKER

Sorry...I did not realize this was still open.  Thanks ee_rlee for your help and I apologize for not closing this.
r270ba,

Thanks for following up on this :-)
Avatar of r270ba

ASKER

No problem...sorry you had to get involved!