Back in the late 60s, early 70s, “Fourth Generation Languages” (4GL) seemed like the future. “Write programs without a programmer!” they promised. “It’s a specification-based language, you just tell the computer what should happen, and it figures out how to do it!”

The most famous and probably most widely deployed 4GL, when all its variants and dialects are taken as a single unit, has to be SQL. SQL is the epitome of a specification-based language: each SQL statement represents an expression in relational algebra, and the database gets to use its own internal logic for deciding the best way to execute that statement, making database tuning a dark art involving statistical modeling and a lot of, “I dunno, but when I use this hint it runs fast.”

Speaking of “programs without a programmer”, Nicholas G is trying to update some old SQL queries to a new database engine. That’s where this was found:

	DECODE(TRPT.TIERS_DESTINATAIRE_AMONT1,TRPT.TIERS_DEPART_AMONT2,
	0,
	Decode(TRPT.Tiers_Destinataire_Amont2,TRPT.Tiers_Depart_Amont3,Decode(Tiers_Destinataire_Amt2.Tie_Type_Depot,'PLF',0,
			Decode(Tiers_Depart_Amt2.Tie_Depot_Debord, TRPT.Tiers_Destinataire_Amont2,0,Decode(TRPT.Tiers_Destinataire_Amont2,TRPT.Tiers_Depart_Amont3,0,1
))), 
			DECODE(TRPT.Tiers_Destinataire_Amont3,TRPT.Tiers_Depart_Amont4,	
				DECODE(TIERS_DEPART_AMT3.TIE_DEPOT_DEBORD, TRPT.TIERS_DESTINATAIRE_AMONT3,
					0,
					1), 
				DECODE(Tiers_Depart_Amt1.Tie_Type_Depot,'PLF',
					DECODE(Tiers_Destinataire_Amt1.Tie_Type_Depot,'PLF',
						0,
						Decode(Tiers_Depart_Amt2.Tie_Type_Depot,'PLF',
							Decode(Tiers_Destinataire_Amt2.Tie_Type_Depot,'PLF',
								0,
								Decode(Tiers_Depart_Amt3.Tie_Type_Depot,'PLF',
									Decode(Tiers_Destinataire_Amt3.Tie_Type_Depot,'PLF',
										0,
										1),
									1)
								),
							1)
						),
						1)	)))

I could decode this, but I don’t want to. For those unfamiliar, the SQL DECODE is SQL’s strange blend of a ternary and a switch. The first argument is your input value. Each successive pair of arguments is the value you wish to compare it to, and the value to return if the input and the compare value are equal. The last parameter is your “default” case. E.g., DECODE(a, 1, "It's one!", 2, "It's two!", "I don't know what it is.") would return “It’s one!” if a==1, “It’s two!” if a==2, and “I don’t know…” in any other case.

Like a ternary, they have their place. Also like a ternary, not like this. Not like this.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!