please check my query!!

Dwi Hyuga

10y1.1kSQL

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!!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Sahoo10y ago
Accepted 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.
0
Dwi Hyuga10y ago
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)/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';
0
Suraj Sahoo10y ago
```
(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 ( one,two,three)
(
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, two,three, ISNULL(two,'') + ISNULL(three,'') AS four from cteTable

This will work once you understand the concept being used here.
Thanks
0