SQL syntax for selecting column A or B based on column C

[Thomas Kennedy]

I have this object called searchResults which is essentially a wrapper for a SQL resultset. This Java method returns the value from column A, or B or C, depending on the value found in column DRUG_PRC_ACTION_CD.

public String getResultsDrugUnitPrice(int rowIndex)
{
	String price;
	String paInd = searchResults.getValueAt(rowIndex, "DRUG_PRC_ACTION_CD");

	if (paInd.equals("2"))
	{
		price = searchResults.getValueAt(rowIndex, "DG_MAX_ALLOW_COST");
	}
	else if (paInd.equals("3"))
	{
		price = searchResults.getValueAt(rowIndex, "DRUG_STATE_MAC");
	}
	else if (paInd.equals("5"))
	{
		price = searchResults.getValueAt(rowIndex, "DRUG_AV_WHLSL_PR");
	}
	else if (paInd.equals("7"))
	{
		price = searchResults.getValueAt(rowIndex, "WAC_DE");
	}
	else
	{
		price = "N/A";
	}
	return price;
}

Yes, the getter is taking an int argument. It's not my code.

I want to eliminate all this Java conditional processing and move this work back to the SQL query. In other words I want something like

SELECT COLUMN_X, COLUMN_Y, CASE DRUG_PRC_ACTION_CD WHEN '2' THEN DG_MAX_ALLOW_COST WHEN '3' THEN DRUG_STATE_MAC and so on ELSE 'N/A' END as 'unit_price', COLUMN_Z
so that the getter becomes something like

public String getResultsDrugUnitPrice(int rowIndex)
{
	String price;
	String paInd = searchResults.getValueAt(rowIndex, "unit_price");
	return price;
}

but I am not sure of the syntax. Can anyone show me what the SQL syntax is?

[Paul Clapham]

That looks like a reasonable approximation of working SQL code. Of course the details depend on your database, which may or may not support the "CASE" construct, or may support it in a different way than that. Did you try that? Did you have a problem with it?

[Thomas Kennedy]

Here we are:
SELECT CASE DRUG_PRC_ACTION_CD WHEN '2' THEN CAST(DG_MAX_ALLOW_COST as DECIMAL(12,2)) WHEN '3' THEN CAST(DRUG_STATE_MAC as DECIMAL(12,2)) WHEN '5' THEN CAST(DRUG_AV_WHLSL_PR as DECIMAL(12,2)) WHEN '7' THEN CAST(WAC_DE as DECIMAL(12,2)) ELSE 0.0 END AS UNIT_PRICE
I will deal with sticking in the 'N/A' later, somehow.

[Paul Clapham]

Thomas Kennedy wrote:I will deal with sticking in the 'N/A' later, somehow.

It's going to be hard to stick that string into a column which appears to be declared as a numeric type. NULL would be more practical from the SQL point of view, because you can get a null into a numeric column, but probably nulls would wreak havoc with that wrapper code you have there.

[Thomas Kennedy]

Right. Much later, there's a wrapper, inside the view, that converts all this stuff to Strings. That's probably where I will handle any necessary fiddling. I'm in the process of figuring out how to replace all the scriptlets with JSTL and EL, and where possible I'm pushing the work back into DB2. This is one of the exceptions.