468,316 Members | 2,151 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

SQL LIKE help

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
3 1521
iburyak
1,017 Expert 512MB
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
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
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.

Similar topics

9 posts views Thread by Tom | last post: by
6 posts views Thread by wukexin | last post: by
3 posts views Thread by Colin J. Williams | last post: by
7 posts views Thread by Corepaul | last post: by
5 posts views Thread by Steve | last post: by
8 posts views Thread by Mark | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.