Complicated OR

I have two tables where I'm trying to get a specific conversion factor
for a list of items. My items can have several conversion factors but
I'm only interested in LB or PCS. If the item has the conversion
factor for CS to LB I want my view to pick up that one, but if it
doesn't, it should look for PCS. If it finds neither, it should not
select anything for that field. How can I do that? Is it possible?It's actually more complicated than I stated in my initial post. The tables
that I'm concerned with are tdrpl100 and tiitm004. An example would be
S8W12700 has 4 conversion factors from CS to LB. PCS, ft3 and PLT.
K8081 has 2 from CS to PCS and PLT.
K8111K has 1 from CST to PLT.
In the first case, I want S8W12700 to return the conversion from CS to LB
but not PCS. In the second case, I want CS to PCS and in the third, I want
it to return nothing.
What do you think?
SELECT dbo.tdrpl100.Order_Number, dbo.tdrpl100.Order_Date,
dbo.tdrpl100.[Position], dbo.tdrpl100.Item, dbo.tiitm001.Item_Description,
dbo.tiitm001.Weight,
dbo.tdrpl100.Ordered_Quantity,
dbo.tdrpl100.Delivered_Quantity, dbo.tdrpl100.Units, dbo.tiitm004.Base_Unit,
dbo.tiitm004.Unit,
dbo.tiitm004.Conversion_Factor, dbo.tcmcs003.WH_Name,
dbo.tcmcs003.WH_Address, dbo.tcmcs003.WH_City
FROM dbo.tiitm004 INNER JOIN
dbo.tdrpl100 ON dbo.tiitm004.Base_Unit =
dbo.tdrpl100.Units AND dbo.tiitm004.Item = dbo.tdrpl100.Item LEFT OUTER JOIN
dbo.tcmcs003 RIGHT OUTER JOIN
dbo.ktdrpl105 ON dbo.tcmcs003.Warehouse =
dbo.ktdrpl105.DestWH ON dbo.tdrpl100.Order_Number = dbo.ktdrpl105.OrderNumbe
r
LEFT OUTER JOIN
dbo.tiitm001 ON dbo.tdrpl100.Item = dbo.tiitm001.Item
"ML" wrote:

> Could be, but then again it could not be. Right now you're the only one to
> say. :)
> Please post DDL and sample data.
>
> ML|||Kim,
Well it seems there is a complex logic in the querry that you wrote.
But then if you are only concerned with two tables: tdrpl100 and tiitm004
then to get the required conversion you can simply limit the result set with
the things that you actually want. say by limiting the result set first by
limiting the base unit to CS and then look for the conversion unit of first
LB and then PCS in tiitm004. If however you dont find a match just over ride
it will say null (if that is what is required- "to return nothing.")
Hope this helps
Abhishek
"Kim" wrote:
> It's actually more complicated than I stated in my initial post. The tabl
es
> that I'm concerned with are tdrpl100 and tiitm004. An example would be
> S8W12700 has 4 conversion factors from CS to LB. PCS, ft3 and PLT.
> K8081 has 2 from CS to PCS and PLT.
> K8111K has 1 from CST to PLT.
> In the first case, I want S8W12700 to return the conversion from CS to LB
> but not PCS. In the second case, I want CS to PCS and in the third, I wan
t
> it to return nothing.
> What do you think?
>
> SELECT dbo.tdrpl100.Order_Number, dbo.tdrpl100.Order_Date,
> dbo.tdrpl100.[Position], dbo.tdrpl100.Item, dbo.tiitm001.Item_Description,
> dbo.tiitm001.Weight,
> dbo.tdrpl100.Ordered_Quantity,
> dbo.tdrpl100.Delivered_Quantity, dbo.tdrpl100.Units, dbo.tiitm004.Base_Uni
t,
> dbo.tiitm004.Unit,
> dbo.tiitm004.Conversion_Factor, dbo.tcmcs003.WH_Name
,
> dbo.tcmcs003.WH_Address, dbo.tcmcs003.WH_City
> FROM dbo.tiitm004 INNER JOIN
> dbo.tdrpl100 ON dbo.tiitm004.Base_Unit =
> dbo.tdrpl100.Units AND dbo.tiitm004.Item = dbo.tdrpl100.Item LEFT OUTER JO
IN
> dbo.tcmcs003 RIGHT OUTER JOIN
> dbo.ktdrpl105 ON dbo.tcmcs003.Warehouse =
> dbo.ktdrpl105.DestWH ON dbo.tdrpl100.Order_Number = dbo.ktdrpl105.OrderNum
ber
> LEFT OUTER JOIN
> dbo.tiitm001 ON dbo.tdrpl100.Item = dbo.tiitm001.Ite
m
>
>
> "ML" wrote:
>|||Abhishek,
Yes, I understand what I need to do. It's how to do it that I'm a little
stuck on. Can you give me a hint? Of course, at first I thought I could
remove everything but LB and PCS. The base unit is already limited to CS.
That part is fine. But How do I look first for LB and then for PCS?
Anything I can think of returns both.
"Abhishek Pandey" wrote:
> Kim,
> Well it seems there is a complex logic in the querry that you wrote.
> But then if you are only concerned with two tables: tdrpl100 and tiitm004
> then to get the required conversion you can simply limit the result set wi
th
> the things that you actually want. say by limiting the result set first by
> limiting the base unit to CS and then look for the conversion unit of firs
t
> LB and then PCS in tiitm004. If however you dont find a match just over ri
de
> it will say null (if that is what is required- "to return nothing.")
> Hope this helps
> Abhishek
> "Kim" wrote:
>|||"Kim" <Kim@.discussions.microsoft.com> wrote in message
news:0AB29FAC-5D13-4472-8CC9-7B573E12950F@.microsoft.com...
> It's actually more complicated than I stated in my initial post.
The tables
> that I'm concerned with are tdrpl100 and tiitm004. An example
would be
> S8W12700 has 4 conversion factors from CS to LB. PCS, ft3 and PLT.
> K8081 has 2 from CS to PCS and PLT.
> K8111K has 1 from CST to PLT.
> In the first case, I want S8W12700 to return the conversion from
CS to LB
> but not PCS. In the second case, I want CS to PCS and in the
third, I want
> it to return nothing.
> What do you think?
>
<snip>

>
Kim,
I have a few questions.
1) How is the "conversion" that is being referred to here decided
upon? (Note: To me, the second post and the original post are
specifying what appear to be different desires for what end-results
are looked for.)
2) What are S8W12700, K8081, and K8111K? They do not appear to be
table or column names.
3) How does S8W12700 return something?
Note: In BOL, look up "aliases, table". Table aliases are a great
way to improve the readability of SQL statements.
Sincerely,
Chris O.
PS The link http://www.aspfaq.com/etiquette.asp?id=5006, is
excellent
when it comes to detailing how to provide the information that will
best enable others to answer your questions.
----
Original SQL Statement:
SELECT dbo.tdrpl100.Order_Number, dbo.tdrpl100.Order_Date,
dbo.tdrpl100.[Position], dbo.tdrpl100.Item,
dbo.tiitm001.Item_Description,
dbo.tiitm001.Weight,
dbo.tdrpl100.Ordered_Quantity,
dbo.tdrpl100.Delivered_Quantity, dbo.tdrpl100.Units,
dbo.tiitm004.Base_Unit,
dbo.tiitm004.Unit,
dbo.tiitm004.Conversion_Factor,
dbo.tcmcs003.WH_Name,
dbo.tcmcs003.WH_Address, dbo.tcmcs003.WH_City
FROM dbo.tiitm004 INNER JOIN
dbo.tdrpl100 ON dbo.tiitm004.Base_Unit =
dbo.tdrpl100.Units AND dbo.tiitm004.Item = dbo.tdrpl100.Item LEFT
OUTER JOIN
dbo.tcmcs003 RIGHT OUTER JOIN
dbo.ktdrpl105 ON dbo.tcmcs003.Warehouse =
dbo.ktdrpl105.DestWH ON dbo.tdrpl100.Order_Number =
dbo.ktdrpl105.OrderNumber
LEFT OUTER JOIN
dbo.tiitm001 ON dbo.tdrpl100.Item =
dbo.tiitm001.Item
----
Reformatted SQL Statement:
SELECT tdr.Order_Number
,tdr.Order_Date
,tdr.[Position]
,tdr.Item
,tii2.Item_Description
,tii2.Weight
,tdr.Ordered_Quantity
,tdr.Delivered_Quantity
,tdr.Units
,tii.Base_Unit
,tii.Unit
,tii.Conversion_Factor
,tcm.WH_Name
,tcm.WH_Address
,tcm.WH_City
FROM dbo.tiitm004 AS tii
INNER JOIN
dbo.tdrpl100 AS tdr
ON tii.Base_Unit = tdr.Units
AND tii.Item = tdr.Item
LEFT OUTER JOIN
dbo.tcmcs003 AS tcm
RIGHT OUTER JOIN
dbo.ktdrpl105 AS ktd
ON tcm.Warehouse = ktd.DestWH
ON tdr.Order_Number = ktd.OrderNumber
LEFT OUTER JOIN
dbo.tiitm001 AS tii2
ON tdr.Item = tii2.Item
----|||1) How is the "conversion" that is being referred to here decided
> upon? (Note: To me, the second post and the original post are
> specifying what appear to be different desires for what end-results
> are looked for.)
Table tiitm004 handles the conversions. What I'm trying to do is print out
a bill of lading that will give me each item on that bill, a single time. I
n
calculating the number of skids that will be included in that bill, I need
the conversion factor from Cases to either pounds or pieces depending on the
type of item. However, conversion factors exist for BOTH pounds and pieces
on some of the items. I want the view to search for the pounds conversion
first. If it finds it, I want it to ignore any other conversions including
pieces. If it doesn't find pounds, I want it to look for the conversion to
pieces next. If it doesn't find either, it can leave a blank on the report.

> 2) What are S8W12700, K8081, and K8111K? They do not appear to be
> table or column names.
No those were specific items. The column name is Item.
> 3) How does S8W12700 return something?
I guess I wasn't using the correct terms. For the row S8W12700 I would only
want to see the conversion for LB. The Unit field would be LB and the
conversion factor would should me what the conversion from Cases to pounds
should be. It would not return the conversion for PCS (pieces) as well
because I am making a bill of lading that would only have this item on it a
single time.
> Note: In BOL, look up "aliases, table". Table aliases are a great
> way to improve the readability of SQL statements.
"Chris2" wrote:

> "Kim" <Kim@.discussions.microsoft.com> wrote in message
> news:0AB29FAC-5D13-4472-8CC9-7B573E12950F@.microsoft.com...
> The tables
> would be
> CS to LB
> third, I want
> <snip>
>
> Kim,
> I have a few questions.
> 1) How is the "conversion" that is being referred to here decided
> upon? (Note: To me, the second post and the original post are
> specifying what appear to be different desires for what end-results
> are looked for.)
> 2) What are S8W12700, K8081, and K8111K? They do not appear to be
> table or column names.
> 3) How does S8W12700 return something?
> Note: In BOL, look up "aliases, table". Table aliases are a great
> way to improve the readability of SQL statements.
>
> Sincerely,
> Chris O.
>
> PS The link http://www.aspfaq.com/etiquette.asp?id=5006, is
> excellent
> when it comes to detailing how to provide the information that will
> best enable others to answer your questions.
> ----
> Original SQL Statement:
> SELECT dbo.tdrpl100.Order_Number, dbo.tdrpl100.Order_Date,
> dbo.tdrpl100.[Position], dbo.tdrpl100.Item,
> dbo.tiitm001.Item_Description,
> dbo.tiitm001.Weight,
> dbo.tdrpl100.Ordered_Quantity,
> dbo.tdrpl100.Delivered_Quantity, dbo.tdrpl100.Units,
> dbo.tiitm004.Base_Unit,
> dbo.tiitm004.Unit,
> dbo.tiitm004.Conversion_Factor,
> dbo.tcmcs003.WH_Name,
> dbo.tcmcs003.WH_Address, dbo.tcmcs003.WH_City
> FROM dbo.tiitm004 INNER JOIN
> dbo.tdrpl100 ON dbo.tiitm004.Base_Unit =
> dbo.tdrpl100.Units AND dbo.tiitm004.Item = dbo.tdrpl100.Item LEFT
> OUTER JOIN
> dbo.tcmcs003 RIGHT OUTER JOIN
> dbo.ktdrpl105 ON dbo.tcmcs003.Warehouse =
> dbo.ktdrpl105.DestWH ON dbo.tdrpl100.Order_Number =
> dbo.ktdrpl105.OrderNumber
> LEFT OUTER JOIN
> dbo.tiitm001 ON dbo.tdrpl100.Item =
> dbo.tiitm001.Item
>
> ----
> Reformatted SQL Statement:
> SELECT tdr.Order_Number
> ,tdr.Order_Date
> ,tdr.[Position]
> ,tdr.Item
> ,tii2.Item_Description
> ,tii2.Weight
> ,tdr.Ordered_Quantity
> ,tdr.Delivered_Quantity
> ,tdr.Units
> ,tii.Base_Unit
> ,tii.Unit
> ,tii.Conversion_Factor
> ,tcm.WH_Name
> ,tcm.WH_Address
> ,tcm.WH_City
> FROM dbo.tiitm004 AS tii
> INNER JOIN
> dbo.tdrpl100 AS tdr
> ON tii.Base_Unit = tdr.Units
> AND tii.Item = tdr.Item
> LEFT OUTER JOIN
> dbo.tcmcs003 AS tcm
> RIGHT OUTER JOIN
> dbo.ktdrpl105 AS ktd
> ON tcm.Warehouse = ktd.DestWH
> ON tdr.Order_Number = ktd.OrderNumber
> LEFT OUTER JOIN
> dbo.tiitm001 AS tii2
> ON tdr.Item = tii2.Item
> ----
>
>|||"Kim" <Kim@.discussions.microsoft.com> wrote in message
news:7027FDA0-65EE-4F76-9ABE-C7E1C28FB9DF@.microsoft.com...
> 1) How is the "conversion" that is being referred to here decided
end-results
> Table tiitm004 handles the conversions. What I'm trying to do is
print out
> a bill of lading that will give me each item on that bill, a
single time. In
> calculating the number of skids that will be included in that
bill, I need
> the conversion factor from Cases to either pounds or pieces
depending on the
> type of item. However, conversion factors exist for BOTH pounds
and pieces
> on some of the items. I want the view to search for the pounds
conversion
> first. If it finds it, I want it to ignore any other conversions
including
> pieces. If it doesn't find pounds, I want it to look for the
conversion to
> pieces next. If it doesn't find either, it can leave a blank on
the report.
Without your specifications (see:
http://www.aspfaq.com/etiquette.asp?id=5006), I am having some
difficulty understanding.
You mentioned: "What I'm trying to do is print out a bill of lading
that will give me each item on that bill, a single time."
If all you need is a list of items on the bill of lading, one time
only, your query should be pretty simple and involve no conversions
of any kind. Simply list all the items, and use DISTINCT.
Immediately after, you mention: "In calculating the number of skids
[...]"
May I ask what listing items only once has to do with calculating
the number of skids?
Obviously, I am missing something. Please forgive me when I say I
don't know what it is.

>
be
> No those were specific items. The column name is Item.
> I guess I wasn't using the correct terms. For the row S8W12700 I
would only
> want to see the conversion for LB. The Unit field would be LB and
the
> conversion factor would should me what the conversion from Cases
to pounds
> should be. It would not return the conversion for PCS (pieces) as
well
> because I am making a bill of lading that would only have this
item on it a
> single time.
>
What is it *about* S8W12700 that signals it gets an LB conversion?
What column of what table tells us this?
And what are we after since we're only listing each item once? The
total weight of all of those items on the bill of lading? Or are we
listing the conversion factor itself?
What is your bill of lading going to look like when it is
successfully printed?
ItemName Conversion
Canned Soup, PCS
Bagged Rice, LB
ItemName Weight
Canned Soup, 240 pieces
Bagged Rice, 6782 pounds
ItemName Skids
Canned Soup, 1
Bagged Rice, 2
What?
Sincerely,
Chris O.
PS The link http://www.aspfaq.com/etiquette.asp?id=5006,
is excellent when it comes to detailing how to provide
the information that will best enable others to answer
your questions.|||Ok. I'm going to answer your question now and then work on the DDL and
Sample data and I will post that as quickly as I can. Yes, I'm trying to
print each item one time on the BOL. Of course though there are other field
s
that I'm trying to extract and print on the same bill of lading, including
the unit of measure, the delivered quantity, description of the item, weight
etc. All of that needs to also be printed on the BOL. That would be very
simple except for the conversion factor. I need the conversion factor so
that I can calculate what the number of skids will be as well as the gross
weight because all these items come in cases and I need the lbs or pieces to
calculate the skids and gross weight. I do this calculation in Crystal
Reports, not in SQL which is why you aren't seeing these caluculations in
this sample view. As I explained, each item can have several conversion
factors. I can filter out all but PCS and LB. That's fine too. But even i
f
I use DISTINCT, the output on an item that has both PCS and LB as a
conversion type, I will get BOTH rows on the BOL.
I'm reading the document you refered me to now and I'll post the samples and
ddl as soon as I can get it.
"Chris2" wrote:

> "Kim" <Kim@.discussions.microsoft.com> wrote in message
> news:7027FDA0-65EE-4F76-9ABE-C7E1C28FB9DF@.microsoft.com...
> end-results
> print out
> single time. In
> bill, I need
> depending on the
> and pieces
> conversion
> including
> conversion to
> the report.
> Without your specifications (see:
> http://www.aspfaq.com/etiquette.asp?id=5006), I am having some
> difficulty understanding.
> You mentioned: "What I'm trying to do is print out a bill of lading
> that will give me each item on that bill, a single time."
> If all you need is a list of items on the bill of lading, one time
> only, your query should be pretty simple and involve no conversions
> of any kind. Simply list all the items, and use DISTINCT.
> Immediately after, you mention: "In calculating the number of skids
> [...]"
> May I ask what listing items only once has to do with calculating
> the number of skids?
> Obviously, I am missing something. Please forgive me when I say I
> don't know what it is.
>
>
> be
> would only
> the
> to pounds
> well
> item on it a
> What is it *about* S8W12700 that signals it gets an LB conversion?
> What column of what table tells us this?
> And what are we after since we're only listing each item once? The
> total weight of all of those items on the bill of lading? Or are we
> listing the conversion factor itself?
> What is your bill of lading going to look like when it is
> successfully printed?
> ItemName Conversion
> Canned Soup, PCS
> Bagged Rice, LB
> ItemName Weight
> Canned Soup, 240 pieces
> Bagged Rice, 6782 pounds
> ItemName Skids
> Canned Soup, 1
> Bagged Rice, 2
> What?
>
> Sincerely,
> Chris O.
> PS The link http://www.aspfaq.com/etiquette.asp?id=5006,
> is excellent when it comes to detailing how to provide
> the information that will best enable others to answer
> your questions.
>
>|||"Kim" <Kim@.discussions.microsoft.com> wrote in message
news:EF833B53-A66C-4E53-8E35-ED632B2E9A50@.microsoft.com...
> Ok. I'm going to answer your question now and then work on the DDL
and
> Sample data and I will post that as quickly as I can. Yes, I'm
trying to
> print each item one time on the BOL. Of course though there are
other fields
> that I'm trying to extract and print on the same bill of lading,
including
> the unit of measure, the delivered quantity, description of the
item, weight
> etc. All of that needs to also be printed on the BOL. That would
be very
> simple except for the conversion factor. I need the conversion
factor so
> that I can calculate what the number of skids will be as well as
the gross
> weight because all these items come in cases and I need the lbs or
pieces to
> calculate the skids and gross weight. I do this calculation in
Crystal
> Reports, not in SQL which is why you aren't seeing these
caluculations in
> this sample view. As I explained, each item can have several
conversion
> factors. I can filter out all but PCS and LB. That's fine too.
But even if
> I use DISTINCT, the output on an item that has both PCS and LB as
a
> conversion type, I will get BOTH rows on the BOL.
> I'm reading the document you refered me to now and I'll post the
samples and
> ddl as soon as I can get it.
>
Kim,
If you were writing the BOL by hand, what would make you decide to
pick PCS over LB as the conversion factor (and vice versa) for any
particular item before calculating its values and writing them in?
Sincerely,
Chris O.|||">
> Kim,
> If you were writing the BOL by hand, what would make you decide to
> pick PCS over LB as the conversion factor (and vice versa) for any
> particular item before calculating its values and writing them in?
>
> Sincerely,
> Chris O.
>
Also,
From your 1st Post:
From your 2nd Post:
CS to LB
third, I want
The above will make a lot more sense after I can see the tables and
the sample data.
Sincerely,
Chris O.