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.

Complex Dataset Filtering

I have an Item Report that lists over 50,000 items, we need to restrict using
one of several different methods.
Some times they will run the report with all items (no Where clause in
the SQL Statement).
Some times they will want to see only the items they call "common"
which are stored in a table (normally I would just do a join to that table).
Lastly, they want to run the report for a specific item (where Item = @.ItemNumber)
I would prefer not to create three different reports.
My first hope was to create all three datasets and then dynamically assigne
the dataset depending on a paramater, I have not been able to accomplish this.
Any suggestion would sure be great.
TerryPiece of cake...well, not at all. I don't have the time to write a specific
answer right now, but I promise that next week I will do it.
By now, I'll copy-paste an answer that I gave in another forum. I hope it
helps you:
My scenario was the following.
I had a parameterized report. There's only one parameter (called @.id_acm)
and the values are taken from a query (in the report>report parameters...
menu I choose "From Query" instead of "non-queried" and so a drop down list
is shown to the user) The source query for the parameter values looked like
this:
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And the query used to get the information for the report (the one I wrote in
the DataSet) looked like this:
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE acm.id_acm = @.id_acm
I also did NOT specified any default value for the parameter.
When my boss executed the report he noticed that it showed anything until he
selected a value from the drop down list. My boss told me that he wanted to
see all the information by default, and if he needed information about a
specific person he would select the person from the drop down list. Here
comes the tricky part.
I noticed that all the current (and future) values for the id_acm field in
the tbl_acm table were greater than 0.
So I modified the source query for the parameter like this:
SELECT -5 AS param_value, 'Everyone' AS param_label
UNION
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And I specified a default value of -5 for the @.id_acm parameter.
Also, I modified the principal query like this:
IF @.id_acm = -5
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
END ELSE
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
WHERE acm.id_acm = @.id_acm
END
Let me explain this: when the user selects "Everyone" the principal query
returns the information of all the persons (because there's no WHERE clause)
If the user selects a specific name, then the principal query returns only
that person's information (because there's a WHERE clause filtering the data)
By default the paramter value is -5 and so the report shows everyone's
information.
Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
fact, if you have n parameters you'll have to nest n IF statemens and you'll
get 2^n posibilities for the WHERE clauses (that's the big pain)
I don't know if this helps in your scenario, but I hope it does.|||U Rock! That toally solved my problem.
I tried something simular prior but I really used more of a SQL Notation.
Thanks, I had all but givin up!
"F. Dwarf" wrote:
> Piece of cake...well, not at all. I don't have the time to write a specific
> answer right now, but I promise that next week I will do it.
> By now, I'll copy-paste an answer that I gave in another forum. I hope it
> helps you:
> My scenario was the following.
> I had a parameterized report. There's only one parameter (called @.id_acm)
> and the values are taken from a query (in the report>report parameters...
> menu I choose "From Query" instead of "non-queried" and so a drop down list
> is shown to the user) The source query for the parameter values looked like
> this:
> SELECT id_acm AS param_value, nombre AS param_label
> FROM tbl_acm
> And the query used to get the information for the report (the one I wrote in
> the DataSet) looked like this:
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
> WHERE acm.id_acm = @.id_acm
> I also did NOT specified any default value for the parameter.
> When my boss executed the report he noticed that it showed anything until he
> selected a value from the drop down list. My boss told me that he wanted to
> see all the information by default, and if he needed information about a
> specific person he would select the person from the drop down list. Here
> comes the tricky part.
> I noticed that all the current (and future) values for the id_acm field in
> the tbl_acm table were greater than 0.
> So I modified the source query for the parameter like this:
> SELECT -5 AS param_value, 'Everyone' AS param_label
> UNION
> SELECT id_acm AS param_value, nombre AS param_label
> FROM tbl_acm
> And I specified a default value of -5 for the @.id_acm parameter.
> Also, I modified the principal query like this:
> IF @.id_acm = -5
> BEGIN
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> END ELSE
> BEGIN
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> WHERE acm.id_acm = @.id_acm
> END
> Let me explain this: when the user selects "Everyone" the principal query
> returns the information of all the persons (because there's no WHERE clause)
> If the user selects a specific name, then the principal query returns only
> that person's information (because there's a WHERE clause filtering the data)
> By default the paramter value is -5 and so the report shows everyone's
> information.
> Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
> fact, if you have n parameters you'll have to nest n IF statemens and you'll
> get 2^n posibilities for the WHERE clauses (that's the big pain)
> I don't know if this helps in your scenario, but I hope it does.|||What I do in my reports is something like
SELECT * FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE (acm.id_acm = @.id_acm or @.id_acm = -5)
"Terry B" wrote:
> U Rock! That toally solved my problem.
> I tried something simular prior but I really used more of a SQL Notation.
> Thanks, I had all but givin up!
>
> "F. Dwarf" wrote:
> > Piece of cake...well, not at all. I don't have the time to write a specific
> > answer right now, but I promise that next week I will do it.
> > By now, I'll copy-paste an answer that I gave in another forum. I hope it
> > helps you:
> >
> > My scenario was the following.
> > I had a parameterized report. There's only one parameter (called @.id_acm)
> > and the values are taken from a query (in the report>report parameters...
> > menu I choose "From Query" instead of "non-queried" and so a drop down list
> > is shown to the user) The source query for the parameter values looked like
> > this:
> >
> > SELECT id_acm AS param_value, nombre AS param_label
> > FROM tbl_acm
> >
> > And the query used to get the information for the report (the one I wrote in
> > the DataSet) looked like this:
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
> > WHERE acm.id_acm = @.id_acm
> >
> > I also did NOT specified any default value for the parameter.
> >
> > When my boss executed the report he noticed that it showed anything until he
> > selected a value from the drop down list. My boss told me that he wanted to
> > see all the information by default, and if he needed information about a
> > specific person he would select the person from the drop down list. Here
> > comes the tricky part.
> > I noticed that all the current (and future) values for the id_acm field in
> > the tbl_acm table were greater than 0.
> > So I modified the source query for the parameter like this:
> >
> > SELECT -5 AS param_value, 'Everyone' AS param_label
> > UNION
> > SELECT id_acm AS param_value, nombre AS param_label
> > FROM tbl_acm
> >
> > And I specified a default value of -5 for the @.id_acm parameter.
> > Also, I modified the principal query like this:
> >
> > IF @.id_acm = -5
> > BEGIN
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> > END ELSE
> > BEGIN
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> > WHERE acm.id_acm = @.id_acm
> > END
> >
> > Let me explain this: when the user selects "Everyone" the principal query
> > returns the information of all the persons (because there's no WHERE clause)
> > If the user selects a specific name, then the principal query returns only
> > that person's information (because there's a WHERE clause filtering the data)
> > By default the paramter value is -5 and so the report shows everyone's
> > information.
> > Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
> > fact, if you have n parameters you'll have to nest n IF statemens and you'll
> > get 2^n posibilities for the WHERE clauses (that's the big pain)
> > I don't know if this helps in your scenario, but I hope it does.

Comparing two tables with different fields

I have two tables which only have one key field in common. I want to synchronise them in the sense that the key items from one table also end up in the second table. I dont know how to do this. This is the situation on the tables:

table1
Fruit | color | taste |
Apple | Red | sweet |
pear | brown | sour |
orange| orange | sweet|
kiwi | brown | sweet

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4

Field1 is present in both tables but has more records in the first table than in the second. I want items in table1 that aren't present in table 2 to be added to table2. In other words, if more fruit is listed in table A I want it to be added to table 2. In this case, the orange and the kiwi should be added to the second table:

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4
orange| NULL | NULL
kiwi | NULL | NULL

Con someone help me?insert into table2 (fruit)
select fruit from table1
where not exists (select 1 from table2 where fruit=table1.fruit)

or

insert into table2 (fruit)
select fruit from table1
where fruit not in (select fruit from table2)

Comparing two tables

IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.
thanks in advance for your help!One quick way is to use EM to script each table then compare both files.|||I was referring to the data in the tables. Sorry for the confusion.|||If you know perl, you should do it with a script like that:

#!C:/Perl/bin/perl.exe
# SYNTAXE : perl script.pl server db user pwd

use DBI;

my @.param = @.ARGV;
my $server = $param[0];
my $database = $param[1];
my $user = $param[2];
my $password = $param[3];

my $dsn = "Driver={SQL Server};Server=$server;Database=$database;Uid=$use r;Pwd=$password;" ;
my $dbh = DBI->connect("dbi:ADO:$dsn") or die "Impossible connection: $DBI::errstr";

my $sth1 = $dbh->prepare( q{
SELECT * FROM table1
}) or die "Can't prepare statement: $DBI::errstr";

my $sth2 = $dbh->prepare( q{
SELECT * FROM table2
}) or die "Can't prepare statement: $DBI::errstr";

my $rc1 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";
my $rc2 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";

while ( @.row1 = $sth1->fetchrow_array and @.row2 = $sth2->fetchrow_array )
{
# do your tests here
}

$rc = $dbh->disconnect;|||Sorry, I don't know pearl. Is there anything on sqlserver?|||Don't see what, except a sql script that you have to write (like the one in perl) :)|||IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.

thanks in advance for your help!What exactly do you mean by "items"? Do your tables have Primary Keys (if not, this problems gets incredibly ugly)? What exactly do you want displayed when a difference is found?

The best bet might be to compose a sample set of data that contains a pair of 5 row tables with one row "missing" in each of them, and the output that you'd like to get from comparing them.

-PatP|||You mean like

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
CREATE TABLE myTable00(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd'

INSERT INTO myTable00(Col2)
SELECT 'a' UNION ALL
SELECT 'x' UNION ALL
SELECT 'c' UNION ALL
SELECT 'z'
GO

SELECT a.Col1, a.Col2, b.Col2
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
WHERE a.Col2 <> b.Col2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||Try this:
http://www.sqlservercentral.com/scripts/contributions/596.asp|||Ummm ... isn't it much easier than that? How about

SELECT value1, value2, value3, value4, value5
FROM table1
WHERE value1 NOT IN (SELECT value1 FROM table2)