SQL Case Statement

[kahaj]

I only have experience using pretty basic Oracle. However, I'm now in need of a better understanding of SQL. I'm trying to write a query using CASE statements (as this seems to be the best approach), but am admittedly lost at the moment.

Here is what I have as a basis:

SELECT LEFT(ITEM_A, 4) AS "PREFIX",
    SUBSTRING(RIGHT(ITEM_A, 12), 5, 2) AS "SUFFIX",
    RIGHT(ITEM_A, 16) AS "INGREDIENT"
FROM THE_TABLE 
WHERE ITEM_B = 'VAL1'
    AND ITEM_C = 'VAL2' 
GROUP BY ITEM_A
ORDER BY PREFIX ASC, SUFFIX ASC

This is yielding a good result set for evaluation. Now, I need to determine which (if any) 'INGREDIENT' values are associated to multiple 'PREFIX' & 'SUFFIX' combos and what those combos are if present. I'm really hitting a brick wall here.

[pritaeas]

http://technet.microsoft.com/en-us/library/ms181765.aspx

Am not sure what you want, can you give an example of in-/output?

[dev90]

And also post your table values.

[rch1231]

Hello,

I happen to be a big fan of SQL's capability to do a auery within a query (somtimes called sub-queries). This give you the ability to generate a sub set of data and then query it, Kind of like generating a temp table but for small sets of data. I have spaced it out a bit so you can see your original query geneerating the data1 set of data and the pulling and ordering the data from there. I hope this helps with what you want to do. Try something like this for your query:

select data1.PREFIX, 
data1.SUFFIX, 
data1.INGREDIANT
from 
(
SELECT LEFT(ITEM_A, 4) AS "PREFIX",
SUBSTRING(RIGHT(ITEM_A, 12), 5, 2) AS "SUFFIX",
RIGHT(ITEM_A, 16) AS "INGREDIENT"
FROM THE_TABLE
WHERE ITEM_B = 'VAL1'
AND ITEM_C = 'VAL2' 
) data1
Order by data1.PREFIX,
 data1.SUFFIX

[ugi.nagesh]

HI

Could you provide the table structure with data and output that you are expecting from the Query.

Thanks