SQL LIKE help

[alaurent71]

I have a SELECT statement THAT has several embedded SELECT statements. I want to search using LIKE on the result of the embedded SELECT (AS f_item, AS f_desc, AS r_item or AS r_desc).

The very confusing SELECT statement is as follows:

SELECT A.ID,A.date1,A. rmc_value,A.use rid,B.formula,B .finishedgood,B .rawmaterial,(S ELECT C.desc1 FROM C WHERE C.ID=B.finished good AND B.finishedgood> 0) AS f_item,(SELECT C.desc2 FROM C WHERE C.ID=B.finished good AND B.finishedgood> 0) AS f_desc,(SELECT D.desc1 FROM D WHERE D.ID=B.rawmater ial AND B.rawmaterial>1 ) AS r_item,(SELECT D.desc2 FROM D WHERE D.ID=B.rawmater ial AND B.rawmaterial>1 ) AS r_desc
FROM A,B,(SELECT MAX(A.date1) AS date1,A.formula FROM A GROUP BY A.formula) formulaID
WHERE A.formula=B.ID AND A.formula=formu laID.formula AND A.date1=formula ID.date1
ORDER BY B.formula ASC

The SELECT statement returns the data I want, I just can't use LIKE against any of the (AS f_item, AS f_desc, AS r_item or AS r_desc) fields.

Any Ideas?

[iburyak]

Try this:

SELECT *
FROM (
	SELECT A.ID,A.date1,A.rmc_value,A.userid,B.formula,B.fini shedgood,B.rawmaterial,
	(SELECT C.desc1 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_item,
	(SELECT C.desc2 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_desc,
	(SELECT D.desc1 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_item,
	(SELECT D.desc2 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_desc 
	FROM A,B,(SELECT MAX(A.date1) AS date1,A.formula FROM A GROUP BY A.formula) formulaID
	WHERE A.formula=B.ID AND A.formula=formulaID.formula AND A.date1=formulaID.date1) a
WHERE f_item like ..... 
or f_desc like ... 
ORDER BY formula ASC

Good Luck.

[alaurent71]

That's what I tried originally.
I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'f_item'.

The WHERE part of the SQL looks like this:
WHERE f_item LIKE '%ztb%'

Try this:

SELECT *
FROM (
	SELECT A.ID,A.date1,A.rmc_value,A.userid,B.formula,B.fini shedgood,B.rawmaterial,
	(SELECT C.desc1 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_item,
	(SELECT C.desc2 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_desc,
	(SELECT D.desc1 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_item,
	(SELECT D.desc2 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_desc 
	FROM A,B,(SELECT MAX(A.date1) AS date1,A.formula FROM A GROUP BY A.formula) formulaID
	WHERE A.formula=B.ID AND A.formula=formulaID.formula AND A.date1=formulaID.date1) a
WHERE f_item like ..... 
or f_desc like ... 
ORDER BY formula ASC

Good Luck.

[alaurent71]

I take that back, it did work. I was putting my brackets in the wrong place.

Thanks!!!