nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

All comments

vince_faller

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?

pmroz

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.

rwright

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").

rwright

I got the wrong link for CALCULATED - try:http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n0jxri3yohqdwbn1slekmsc882gz.htm&docsetVersion=9.4&locale=en