Usually id is supposed to be UNIQUE. Since this is not your case, try: SELECT DISTINCT appln_id FROM Application WHERE ipc_class_symbol MATCHES '(^[[:<:]]B62D 6[[:>:]]*)' OR ipc_class_symbol MATCHES '(^[[:<:]]B60[[:>:]]*)'
Cheers, Riccardo 2014-08-20 15:10 GMT+02:00 'Curtis Mosters' via OrientDB < [email protected]>: > Thanks Luca, > > well now I fixed the little ")" mistake from your code and got: > > SELECT appln_id FROM Application WHERE ipc_class_symbol MATCHES '(^[[:<:]]B62D > 6[[:>:]]*)' AND ipc_class_symbol MATCHES '(^[[:<:]]B60[[:>:]]*)' > > It runs without errors. But I don't get a result back. In MySQL I got 22 > entries. So maybe some problems with the MATCHES section? In SQL the > command is REGEX. > > But what I think is that the whole command differs from the SQL above. > Let's say the example is: > > id,symbol > 1,EG4 > 2,B65 > 2,B60K > 2,B62D 6 > 3,EA5 > ... > > In that case I should just get the id "2" as result. I think the problem > is that he is taking a row and looking if both MATCHES are working. But he > will never find that case. So going to the next. But never combining rows. > Can that be Luca? > > Am Mittwoch, 20. August 2014 13:36:41 UTC+2 schrieb Lvc@: >> >> Hi Curtis, >> the inner SELECT is not valid. You should put a FROM. >> >> SELECT FROM Application WHERE symbol MATCHES '(^[[:<:]]B62D 6[[:>:]]*)') AND >> symbol MATCHES '(^[[:<:]]B60[[:>:]]*)' >> >> ? >> >> Lvc@ >> ᐧ >> >> >> On 20 August 2014 12:50, 'Curtis Mosters' via OrientDB < >> [email protected]> wrote: >> >>> Hey guys, I don't want to waste too much time in my first "big" example. >>> I need to transform a simple query from MySQL into OrientDB query. >>> >>> Homefully someone of you could quickly help me. Otherwhise I need to >>> spend some time testing, that's ok. Alright (btw I neutralized the names): >>> >>> SELECT ipc1.id >>> FROM ( >>> SELECT ipc.id >>> FROM table1 ipc >>> WHERE ipc.symbol REGEXP '(^[[:<:]]B62D 6[[:>:]]*)' >>> LIMIT 0, 100 >>> ) AS ipc1 >>> JOIN ( >>> SELECT ipc.id >>> FROM table1 ipc >>> WHERE ipc.symbol REGEXP '(^[[:<:]]B60[[:>:]]*)' >>> ) AS ipc2 >>> ON ipc1.id = ipc2.id >>> >>> Just in my minds I would do something like: >>> >>> SELECT id FROM Application WHERE (SELECT symbol MATCHES '(^[[:<:]]B62D >>> 6[[:>:]]*)') AND WHERE (SELECT symbol MATCHES '(^[[:<:]]B60[[:>:]]*)') >>> >>> Application is the new class btw. If I test this I just get: >>> >>> com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error >>> on parsing command at position #111: Invalid keyword 'SELECT' Command: >>> SELECT id FROM Application WHERE (SELECT symbol MATCHES '(^[[:<:]]B62D >>> 6[[:>:]]*)') AND WHERE (SELECT symbol MATCHES '(^[[:<:]]B60[[:>:]]*)') >>> ------------------------------------------------------------ >>> -----------------------------------------------------------^ >>> >>> Ohh and please don't say I should merge both id's. Maybe that's a good >>> way for later on or so. But for now let's take it easy and just transform >>> the query =) Thanks. >>> >>> -- >>> >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "OrientDB" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > > --- > You received this message because you are subscribed to the Google Groups > "OrientDB" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
