52681_en-US

liliconcom

SQL with 'Name%'

Hi,

I have a SQL query within open database and searching for something like 'FAIL%'. I like to search for any name that is FAIL***. How do you achieve that? Currently it is only selecting FAIL%.

 

 

dt3 = Open Database(
"DSN=" || one_product || ";UID=**;PWD=***;",
"select count(distinct BoardNo) from " || station || "  where boardno in (select BoardNo from (select BoardNo,result,starttime from " ||
station || " where BoardNo in (select BoardNo from (select BoardNo,result,count(result) as test  from " || station ||
" where result like 'FAIL%' group by BoardNo having test >1 order by starttime desc)as comm) order by BoardNo,starttime desc) as t where result like 'PASS' group by BoardNo)and date(starttime) between '"
|| dateb || "' and '" || datee || "'",
"ABC"
);

 

The name station and dateb and datee has been created. Thanks for your help.

Re: SQL with 'Name%'

Thanks for the suggestions.

Re: SQL with 'Name%'

There is another way to approach this.

 

The FROM clause allows for multiple tables. For example, 

FROM t1, t2, t3 WHERE t1.something = t2.something and t1.something = t3.something.

 

These tables do not have to be tables, they can be queries, even queries on the same table (eg reflexive join). So, for example, in your case, in a FROM clause you could have:

SELECT d1., d2., etc

FROM 

(SELECT DISTINCT boardno, starttime, result FROM station WHERE result LIKE '%FAIL%' AND DATE(starttime) BETWEEN '1/31/2015' AND '12/31/2015'
GROUP BY boardno HAVING COUNT(*) > 1) AS d1 /* table of fails */,

(SELECT DISTINCT  boardno, starttime, result FROM station
WHERE DATE(starttime) BETWEEN '1/31/2015' AND '12/31/2015' AND result LIKE '%PASS%') AS d2 /* table of passes */

WHERE d1. = d2., etc

 

You don't really need the ORDER BY until the very end - here, after the last WHERE clause - because it's really only a display thing - the GROUP BY are the operative clauses here - your selection is based on a summary statistic - in this case a count(). Also, in your query, the starttime is sort of ambiguous. Better to explicity state it and where it's coming from - so in my example, I'm bringing it in from both tables. I've used COUNT(*) which returns the number of rows (See http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n123fsko39j44pn16zlt087e1m2h.htm&docsetVersion=9.4&locale=en)

Re: SQL with 'Name%'

Thanks.

Re: SQL with 'Name%'I got the wrong link for CALCULATED - try:http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n0jxri3yohqdwbn1slekmsc882gz.htm&docsetVersion=9.4&locale=enRe: SQL with 'Name%'

A couple of other asides.

 

It looks like your result field doesn't have standard answers, hence 'FAIL%'. So, you might want to test for '%FAIL%', and/or use in conjunction with BTRIM(). Also, you might want to UPPER() the field to make sure it's upper case (not sure the JMP function, check it out). And I second the observation on PASS - you might want '%PASS%' or something similar.

 

Another observation - you might need to replace HAVING test > 1 with HAVING CALCULATED test > 1 (see "http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n12wnf0zqw2frvn1bghzrncuh89m.htm&docsetVersion=9.4&locale=en").

Re: SQL with 'Name%'

I took the liberty of reformatting your query as it was pretty difficult to read.  I substituted some fake names and date values, and added aliases to inner queries:

SELECT COUNT(DISTINCT d3.BOARDNO)
  FROM DB.STATION d3
 WHERE d3.BOARDNO IN
       (SELECT t.BOARDNO
          FROM (SELECT d2.BOARDNO, d2.RESULT, d2.STARTTIME
                  FROM DB.STATION d2
                 WHERE d2.BOARDNO IN
                       (SELECT comm.BOARDNO
                          FROM (SELECT d1.BOARDNO, d1.RESULT, COUNT(d1.RESULT) AS TEST
                                  FROM DB.STATION d1
                                 WHERE d1.RESULT LIKE 'FAIL%'
                                 GROUP BY d1.BOARDNO
                                HAVING TEST > 1
                                 ORDER BY d1.STARTTIME DESC) AS COMM)
                 ORDER BY d2.BOARDNO, d2.STARTTIME DESC) AS T
         WHERE RESULT LIKE 'PASS'
         GROUP BY t.BOARDNO)
   AND DATE(d3.STARTTIME) BETWEEN '1/31/2015' AND '12/31/2015'

As Vince said LIKE 'FAIL%' should work.  I notice that you are also saying LIKE 'PASS'.  If you don't need wildcards, then = 'PASS' will work the same.

 

 

I would print out the SQL statements to your log, then start by running the inner queries directly on your database.  If you're using Oracle you can use a tool like PL/SQL Developer or Toad.  Also you could put aliases in each subquery to distinguish the station database table.

 

Re: SQL with 'Name%'

That should work.  I do it all the time. Without seeing the db, can't really say what's wrong.  

 

Have you tried taking the final string and running in SQL to see what the output is?