WTF Was This Guy Thinking!!! [modified]

[Kevin Marois]

I'm converting an MS Access app to C# & SQL Server. Here's part of an Access query I just came across:

IIf(TX.TX_TYPE="R",
IIf(TX.TX_MOSTYPE="P",
IIf(TX.TX_DEST_STATUS="A","PO Receipt",
IIf(TX.TX_DEST_STATUS="U","PO Receipt Hold","Other")),
IIf(TX.TX_DEST_STATUS="A","WIP Receipt",
IIf(TX.TX_DEST_STATUS="U","WIP Hold","Other"))),
IIf(TX.TX_TYPE="I",IIf(ISNULL(RTRIM(TX.TX_MOSJOB)) Or RTRIM(TX.TX_MOSJOB)="","Shipment Return","Reverse Issue"),
IIf(TX.TX_ORIGIN_STATUS=TX.TX_DEST_STATUS,"Loc Transfer",
IIf(TX.TX_ORIGIN="SCRAPPED","Reverse Scrap",
IIf(TX.TX_ORIGIN_STATUS="J","Count Adj Up",
IIf(TX.TX_ORIGIN_STATUS="D","PN Change To",
IIf(TX.TX_ORIGIN_STATUS="I","Reverse Unplanned Issue",
IIf(TX.TX_ORIGIN_STATUS="A" And TX.TX_DEST_STATUS="U","Hold",
IIf(TX.TX_ORIGIN_STATUS="U" And TX.TX_DEST_STATUS="A","Off Hold","Other"))))))))) AS [Transaction Type]

You'll notice that

TX.TX_DEST_STATUS="A"

is being evaluated more than once. Go figure

Everything makes sense in someone's mind

moved on Thursday, January 28, 2010 7:54 AM

[thrakazog]

I think step 1 is to delete it and then ask what the original intention was.

[Andrew Rissing]

With a bit of formatting, it makes 'more' sense:

IIf
(TX.TX_TYPE="R",
IIf
(TX.TX_MOSTYPE="P",
IIf
(TX.TX_DEST_STATUS="A",
"PO Receipt",
IIf
(TX.TX_DEST_STATUS="U",
"PO Receipt Hold",
"Other"
)
),
IIf
(TX.TX_DEST_STATUS="A",
"WIP Receipt",
IIf
(TX.TX_DEST_STATUS="U",
"WIP Hold",
"Other"
)
)
),
IIf
(TX.TX_TYPE="I",
IIf
(ISNULL(RTRIM(TX.TX_MOSJOB)) Or RTRIM(TX.TX_MOSJOB)="",
"Shipment Return",
"Reverse Issue"
),
IIf
(TX.TX_ORIGIN_STATUS=TX.TX_DEST_STATUS,
"Loc Transfer",
IIf(TX.TX_ORIGIN="SCRAPPED",
"Reverse Scrap",
IIf
(TX.TX_ORIGIN_STATUS="J",
"Count Adj Up",
IIf
(TX.TX_ORIGIN_STATUS="D",
"PN Change To",
IIf
(TX.TX_ORIGIN_STATUS="I",
"Reverse Unplanned Issue",
IIf
(TX.TX_ORIGIN_STATUS="A" And TX.TX_DEST_STATUS="U",
"Hold",
IIf
(TX.TX_ORIGIN_STATUS="U" And TX.TX_DEST_STATUS="A",
"Off Hold",
"Other"
)
)
)
)
)
)
)
)
) AS [Transaction Type]

Granted, nesting like this just makes it impossible to read without a gratuitous use of formatting. The original query was just not very consistent in its usage of formatting it. The above was the best I could come up with to be able to read it effectively....

[Andrew Rissing]

Btw, this looks more like a coding horror than 'wicked code'. ;P

[Kevin Marois]

I was able to clean it up a bit more, although it took awhile.

CASE WHEN TX.TX_TYPE = 'R' AND TX.TX_MOSTYPE = 'P' AND TX.TX_DEST_STATUS = 'A' THEN 'PO Receipt'
WHEN TX.TX_TYPE = 'R' AND TX.TX_MOSTYPE = 'P' AND TX.TX_DEST_STATUS = 'U' THEN 'PO Receipt Hold'
WHEN TX.TX_TYPE = 'R' AND TX.TX_MOSTYPE = 'P' THEN 'Other'
WHEN TX.TX_TYPE = 'R' AND TX.TX_DEST_STATUS = 'A' THEN 'WIP Receipt'
WHEN TX.TX_TYPE = 'R' AND TX.TX_DEST_STATUS = 'U' THEN 'WIP Hold'
WHEN TX.TX_TYPE = 'R' THEN 'Other'
WHEN TX.TX_TYPE = 'I' AND ISNULL(RTRIM(TX.TX_MOSJOB),'') = '' THEN 'Shipment Return'
WHEN TX.TX_TYPE = 'I' THEN 'Reverse Issue'
WHEN TX.TX_ORIGIN_STATUS = TX.TX_DEST_STATUS THEN 'Loc Transfer'
WHEN TX.TX_ORIGIN = 'SCRAPPED' THEN 'Reverse Scrap'
WHEN TX.TX_ORIGIN_STATUS = 'J' THEN 'Count Adj Up'
WHEN TX.TX_ORIGIN_STATUS = 'D' THEN 'PN Change To'
WHEN TX.TX_ORIGIN_STATUS = 'I' THEN 'Reverse Unplanned Issue'
WHEN TX.TX_ORIGIN_STATUS = 'A' AND TX.TX_DEST_STATUS = 'U' THEN 'Hold'
WHEN TX.TX_ORIGIN_STATUS = 'U' AND TX.TX_DEST_STATUS = 'A' THEN 'Off Hold' ELSE 'Other'
END AS Transaction_Type

Everything makes sense in someone's mind

[Marc Clifton]

It looks like Linq, pre-Linq. ;) Marc

[pawelchooch]

I've seen Garbage like this before, it usually happens when engineers pretend to be programmers... I laugh at inappropriate times

[Muneeb R Baig]

As you wrote:

Everything makes sense in someone's mind

-muneeb A thing of beauty is the joy forever.

[Jorgen Andersson]

Looks to me like a repeated event of technically challenged boss entering the room of a stressed halfwit programmer shouting: We need to add this NOW!

[GibbleCH]

This is where you write a bank of unit tests, based on the input/output of this clause. Then refactor ensuring all tests keep passing.

[IT_Guy 1]

Holy sh*t...thats just horrific man. :) as long as I don't encounter that ever! :laugh:

[ddecoy]

Nice to see people with some extra time on their hands :-\

Learning without thought is labor lost; thought without learning is perilous. (Confucius)

[megaadam]

This dude is clearly a Lisp-genius, stuck with the wrong programming languge. What a pity!

........................ Life is too shor

[sashoalm]

It's in the coding horrors section. Or was the topic moved later with all the replies? I didn't think it was possible to do so in codeproject.

There is sufficient light for those who desire to see, and there is sufficient darkness for those of a contrary disposition. Blaise Pascal

[Andrew Rissing]

It was moved.

[Wamuti]

I think he meant to make a Ladder; Only that it's lying on the wrong wall :laugh:

Wamuti: Any man can be an island, but islands to need water around them! Edmund Burke: No one could make a greater mistake than he who did nothing because he could do only a little.