Help with Case

I have the following data with following conditions:

Old Type New Type

Number: 01 in table1 with Connection : 'J' in table2 'GE'

Number: 01 in table1 with Connection : 'K' in table2 'GPE'

Number: 01 in table1 with Connection: 'L' In table2 'GPA'

Number: 02 in table1 with Connection: 'I' in table2 'I02'

I used queries like:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

ELSE CASE WHEN t2.Connection = 'K' THEN 'GPE'

ELSE CASE WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END END END END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID).

I'll take an example:

An ID can have multiple connections attached to it. Let Say:

Table1 ID : 0001

Number: 01

Table2 ID: 0001

Connection: J, K, S, O, P.

Now, when i do the above query, i would get a result of 3 rows new case types for ID 0001.

I would like to just get one, and the first priority will be put on the condition of (GE).

everytime I see a Number '01' and Connection 'J', I would ignore the other connection. If i do not see Number = '01' and Connection: 'J'. i would go for the rest of the conditions.

Hope you guys see what problem i am having.

Thanks,

Jul.

Try this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

WHEN t2.Connection = 'K' THEN 'GPE'

WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

Since I do not like repeating the condition, I would do this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

|||Still, i get multiple results.....Thanks though.|||So you want only one row returned?

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN (

select id, min(Connection) [Connection]

from Table2

group by id) AS t2

ON (t1.ID = t2.ID)

|||It works....Thanks sooo much.....

Help With a query

If I have table1 and table2, which both have the same fields Product
and Qty. Can you help me with this query...

table1 has two rows
Row 1 Contains ProductA with a Qty of two
Row 2 Contains ProductB with a Qty of four

table2 has has two rows
Row 1 Contains ProductB with a Qty of two
Row 2 Contains ProductC with a Qty of three

What I want to return from a SQL query is a table with three fields...
Product, Table1QTY and Table2QTY
With the example above I would like the result to have three rows, one
for each product.

Any help?

CiarnOn 9 Nov 2004 03:48:54 -0800, Ciar?n wrote:

>If I have table1 and table2, which both have the same fields Product
>and Qty. Can you help me with this query...
>
>table1 has two rows
>Row 1 Contains ProductA with a Qty of two
>Row 2 Contains ProductB with a Qty of four
>table2 has has two rows
>Row 1 Contains ProductB with a Qty of two
>Row 2 Contains ProductC with a Qty of three
>
>What I want to return from a SQL query is a table with three fields...
>Product, Table1QTY and Table2QTY
>With the example above I would like the result to have three rows, one
>for each product.
>Any help?
>Ciarn

Hi Ciarn

Try if this works for you:

SELECT COALESCE(a.Product, b.Product) AS Product,
COALESCE(a.Qty,0) + COAELSCE(b.Qty,0) AS Qty
FROM table1 AS a
FULL OUTER JOIN table2 AS b
ON a.Product = b.Product

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>
> Hi Ciarn
> Try if this works for you:
> SELECT COALESCE(a.Product, b.Product) AS Product,
> COALESCE(a.Qty,0) + COAELSCE(b.Qty,0) AS Qty
> FROM table1 AS a
> FULL OUTER JOIN table2 AS b
> ON a.Product = b.Product
> Best, Hugo

Hugo,

Thanks a million.
Had to tweak the script ever so slightly to get the Qtys in seperate
columns like below, but wouldn't have figured it out without your
help.

SELECT COALESCE (A.Product, B.Product) AS PRODUCT, COALESCE
(A.Qty, 0) AS QTY_A, COALESCE (B.Qty, 0) AS QTY_B
FROM Sheet1 A FULL OUTER JOIN
Sheet2 B ON A.Product = B.Product
WHERE A.Qty>B.Qty

Cheers again.

Ciarn|||On 9 Nov 2004 07:19:49 -0800, Ciar?n wrote:

>Had to tweak the script ever so slightly to get the Qtys in seperate
>columns like below, but wouldn't have figured it out without your
>help.

Hi Ciarn,

I see I misread your query - somehow, I thought you wanted the total.
Apologies for that.

>SELECT COALESCE (A.Product, B.Product) AS PRODUCT, COALESCE
>(A.Qty, 0) AS QTY_A, COALESCE (B.Qty, 0) AS QTY_B
>FROM Sheet1 A FULL OUTER JOIN
> Sheet2 B ON A.Product = B.Product
>WHERE A.Qty>B.Qty

Are you sure about the WHERE clause? With default ANSI settings, this
would remove the rows for all products that are pressent in only Sheet1 or
in only Sheet2, since a comparison involving NULL will never evaluate to
TRUE.

Since this requirement is far from obvious from your first post, I wonder
why you included this WHERE clause and what the acutal business problem
you have to solve is.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with 1toMany update/case stmt?

Hi all,
Looking for a way to do this in the minimum # of stmts I can.
Basically, this is doing a table1(1) to table2(many) update join. When
the t2 row has myindex = 0, then update column t1.c1.
The problem is that for each row in t2, it's index only matches 1 column
out of the 5 (c1..c5) in t1 for a given tuple. The other 4 columns
should not be updated in that case.
I tried to accomplish this my defaulting back to the orignal t1.cx value,
but I find that it is putting the ORIGINAL pre-update stmt value in
there, rather than the last value it was updated to during the update
operation.
Is there an option, or a way in a case stmt to say 'in this case, leave
the value alone and don't change it?'
Thanks!
UPDATE table1
SET c1 = CASE WHEN t2.myindex = 0 THEN t2.newvalue ELSE t1.c1 END,
c2 = CASE WHEN t2.myindex = 1 THEN t2.newvalue ELSE t1.c2 END,
c3 = CASE WHEN t2.myindex = 2 THEN t2.newvalue ELSE t1.c3 END,
c4 = CASE WHEN t2.myindex = 3 THEN t2.newvalue ELSE t1.c4 END,
c5 = CASE WHEN t2.myindex = 4 THEN t2.newvalue ELSE t1.c5 END
FROM table1 t1 FULL JOIN table2 t2 ON
( t1.mykey = t2.mykey
AND t1.mydate >= t2.fromdate
AND (t1.mydate <= t2.thrudate OR t2.thrudate IS NULL)
)
WHERE t1.status = 'x'
AND t2.status = 'y'you need to aggregate the values in table2 before you do the update. becaus
e
you're replacing the values, you need to essentially select the correct valu
e
in table2 before updating the row in table1. make it a 1=1 relationship
before doing the update. The problem with your current design is that there
is no definitive way to guarantee the order of the updates, so each time the
query runs, you could get different results.
In a side note, the predicate of your update statement doesn't make sense.
Why are you using a full join in an update statement? I'm not even sure what
happens to the rows in which the table1 columns are null. Are they ignored?
"xnews user" wrote:

>
> Hi all,
> Looking for a way to do this in the minimum # of stmts I can.
> Basically, this is doing a table1(1) to table2(many) update join. When
> the t2 row has myindex = 0, then update column t1.c1.
> The problem is that for each row in t2, it's index only matches 1 column
> out of the 5 (c1..c5) in t1 for a given tuple. The other 4 columns
> should not be updated in that case.
> I tried to accomplish this my defaulting back to the orignal t1.cx value,
> but I find that it is putting the ORIGINAL pre-update stmt value in
> there, rather than the last value it was updated to during the update
> operation.
> Is there an option, or a way in a case stmt to say 'in this case, leave
> the value alone and don't change it?'
> Thanks!
>
> UPDATE table1
> SET c1 = CASE WHEN t2.myindex = 0 THEN t2.newvalue ELSE t1.c1 END,
> c2 = CASE WHEN t2.myindex = 1 THEN t2.newvalue ELSE t1.c2 END,
> c3 = CASE WHEN t2.myindex = 2 THEN t2.newvalue ELSE t1.c3 END,
> c4 = CASE WHEN t2.myindex = 3 THEN t2.newvalue ELSE t1.c4 END,
> c5 = CASE WHEN t2.myindex = 4 THEN t2.newvalue ELSE t1.c5 END
> FROM table1 t1 FULL JOIN table2 t2 ON
> ( t1.mykey = t2.mykey
> AND t1.mydate >= t2.fromdate
> AND (t1.mydate <= t2.thrudate OR t2.thrudate IS NULL)
> )
> WHERE t1.status = 'x'
> AND t2.status = 'y'
>