By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

SQL LIKE help

P: 3
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.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
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?
Sep 23 '08 #1
Share this Question
Share on Google+
3 Replies


iburyak
Expert 100+
P: 1,017
Try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (
  3.     SELECT A.ID,A.date1,A.rmc_value,A.userid,B.formula,B.fini shedgood,B.rawmaterial,
  4.     (SELECT C.desc1 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_item,
  5.     (SELECT C.desc2 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_desc,
  6.     (SELECT D.desc1 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_item,
  7.     (SELECT D.desc2 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_desc 
  8.     FROM A,B,(SELECT MAX(A.date1) AS date1,A.formula FROM A GROUP BY A.formula) formulaID
  9.     WHERE A.formula=B.ID AND A.formula=formulaID.formula AND A.date1=formulaID.date1) a
  10. WHERE f_item like ..... 
  11. or f_desc like ... 
  12. ORDER BY formula ASC
Good Luck.
Sep 23 '08 #2

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (
  3.     SELECT A.ID,A.date1,A.rmc_value,A.userid,B.formula,B.fini shedgood,B.rawmaterial,
  4.     (SELECT C.desc1 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_item,
  5.     (SELECT C.desc2 FROM C WHERE C.ID=B.finishedgood AND B.finishedgood>0) AS f_desc,
  6.     (SELECT D.desc1 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_item,
  7.     (SELECT D.desc2 FROM D WHERE D.ID=B.rawmaterial AND B.rawmaterial>1) AS r_desc 
  8.     FROM A,B,(SELECT MAX(A.date1) AS date1,A.formula FROM A GROUP BY A.formula) formulaID
  9.     WHERE A.formula=B.ID AND A.formula=formulaID.formula AND A.date1=formulaID.date1) a
  10. WHERE f_item like ..... 
  11. or f_desc like ... 
  12. ORDER BY formula ASC
Good Luck.
Sep 23 '08 #3

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

Thanks!!!
Sep 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.