ANOTHER SQL QUESTION

[etmccarthy13]

OK im trying to write a report to pull this specific information (USING SQL)

Sample DB
PALLET#| QTY | HOLDCODE (IF APP)|
123 |100 |
124 |100 |pp
125 |100 |pp

I want the following Output

PALLET#|QTY|HOLD
123 |100|
124 |100|X
125 |100|X

I have everything working but cant seem to figure out how to replace the "PP" with "X"

Im sure its simple

 

[carnageX]

I have some code that does something like this at work...when I get back to my desk after lunch I'll find it and post it.

 

[carnageX]

Ok, here ya go. Something like this in your SELECT:

COALESCE (HoldCode, CASE HoldCode WHEN 'pp' THEN 'X' END, '') AS Hold

 

[etmccarthy13]

ODD.. Its taking it as a valid statement but is not changing anyhting on the output

 

[carnageX]

Case does matter..so if the values are stored in the database as uppercase "PP" and not lowercase "pp", you'll have to change that in the snippet I posted.

 

[etmccarthy13]

Ive tried both ways and still nothing

 

[etmccarthy13]

Here is the actual language

REPORTFILENAME=N/A
REPORTTYPE=xls
SQLSTART:
select '50000005000' as PARTN, '0001' as LGORT, WarehouseSku as MATNR, LotReferenceTwo as CHARG, SUM(QTYONHAND) AS MENGE, '' as INSMK, COALESCE (UserConditionCode, CASE UserConditionCode WHEN 'PP' THEN 'X' END, '') AS DZUSTD, 'ICS' as ERFME
from InventoryAllView
where customername = $P{CustomerName} and
WarehouseName = $P{WarehouseName} and
FacilityName = $P{FacilityName}
GROUP BY CUSTOMERNAME,WAREHOUSESKU, LOTREFERENCETWO, USERCONDITIONCODE
SQLEND:

 

[carnageX]

i suggest you use code tags to make the code more readable on the forum .

Also, are you using SQL Server, MySQL, Oracle, etc. as your database server?

 

[etmccarthy13]

IM using SQL SERVER..

I have to look into Code Tags.. Not sure how to use them lol

 

[carnageX]

Sorry, made a mistake. Should be:

COALESCE (CASE UserConditionCode WHEN 'PP' THEN 'X' END, '') AS DZUSTD

Remove the First parameter in the Coalesce statement.