please check my query!!

Question

this is my query but there was an error 'unknown column 'two' in 'field list' this is my query please check it for me!! i need your help SELECT a.* b.* c.* ((a.fl/ 10 )* 3.15 *c.BeratJenis) AS one  pow(((a.od/ 10 )+ 1.5 )/ 2  2 ) AS two pow((a.id/ 10 )/ 2  2 ) AS three (two-three) AS four (( one *four)/ 1000 ) AS equals FROM tbl_sticker AS a LEFT JOIN tbl_daftar_item AS b ON a.KodeItem=b.KodeItem LEFT JOIN tbl_rho AS c ON a.KodeItem=c.KodeItem WHERE a.IDSticker= '1234/18.12101-15' ; but when i tried it like this it run normally SELECT a.* b.* c.* ((a.fl/ 10 )* 3.15 *c.BeratJenis) AS one  pow(((a.od/ 10 )+ 1.5 )/ 2  2 ) AS two pow((a.id/ 10 )/ 2  2 ) AS three FROM tbl_sticker AS a LEFT JOIN tbl_daftar_item AS b ON a.KodeItem=b.KodeItem LEFT JOIN tbl_rho AS c ON a.KodeItem=c.KodeItem WHERE a.IDSticker= '1234/18.12101-15' ; ouughhh ! it so confusing!!

Suraj Sahoo · Answer

Hello I think the concept will still be the same to use CTE. Try using the CTE and I am pretty sure you can acheive.

Dwi Hyuga · Answer

this is the full script i am so sory the script was trimmed SELECT a.* b.* c.* ((a.fl/10)*3.15*c.BeratJenis) AS one pow(((a.od/10)+1.5)/22) AS two pow((a.id/10)/22) AS three (two-three) AS four ((one*four)/1000) AS equals FROM tbl_sticker AS a LEFT JOIN tbl_daftar_item AS b ON a.KodeItem=b.KodeItem LEFT JOIN tbl_rho AS c ON a.KodeItem=c.KodeItem WHERE a.IDSticker='1234/18.12101-15';

Suraj Sahoo · Answer

(two-three) This is not the way you should select values/data from two columns into one i.e. in your case four. You need to add the columns into a temporary table and then you can join to retrieve the columns. Here in your case Common Table Expression would be the apt solution: Like: With cteTable ( onetwothree) ( SELECT ((a.fl/ 10 )* 3.15 *c.BeratJenis) AS one  pow(((a.od/ 10 )+ 1.5 )/ 2  2 ) AS two pow((a.id/ 10 )/ 2  2 ) AS three FROM tbl_sticker AS a LEFT JOIN tbl_daftar_item AS b ON a.KodeItem=b.KodeItem LEFT JOIN tbl_rho AS c ON a.KodeItem=c.KodeItem WHERE a.IDSticker= '1234/18.12101-15' ; ) select one twothree ISNULL(two'') + ISNULL(three'') AS four from cteTable This will work once you understand the concept being used here. Thanks