Often times when inserting a sentence(string) into a predefined database, we run into column character limitations.
Yes, you can use a truncate function but that end up creating meaningful sentences. Not to mention depending on where is breaks, sentence now could have words that you never want to see/display.
Let’s say sentence is, “Quick brown fox jumps over the lazy dog” and breaking at 19(i.e. getting the 19 leftmost characters), would make it “Quick brown fox jum”.
I know nothing harmful there but I think I we can be little smarter than that.
Here is what I got, and the final result will be something like, “Quick brown fox“.
Just by looking at it seems like this is just an SQL join (or in Coldfusion just a QoQ) but I ran into a complication because one query contains SQL arithmatic and other doesn’t. Obviously SQL JOIN is also out of the picture.
So this is my solution,
<cfquery name="q1" datasource="myDataSource">
SELECT T1.ItemNo, T1.UnitSize, T2.uCount, (T1.UnitSize * T2.uCount) AS ItemUsedAmt
FROM TABLE1 AS T1,
(SELECT DISTINCT ItemNo, SUM(UnitCount) AS uCount
WHERE PO = '#ARGUMENTS.PO#'
GROUP BY ItemNo) AS T2
WHERE T2.ItemNo = T1.ItemNo
(my answer is inspired with internet research and I wish i wrote down the website that lead me to this solution.)