Page 1 of 1
regarding query repository
Posted: Mon Dec 04, 2006 4:09 pm
by vijaykumar
from the command line interface iam giving command select @ID,NAME where @ID like '%ROOT%' from DS_JOBOBJECTS, iam getting error unexpected verb token was taken was "SELECT" scanned was select.
anyone help me plz.
cheers;
vijay
Posted: Mon Dec 04, 2006 4:55 pm
by ray.wurlod
Which command line?
What precisely is the error message?
Did you make any typing errors when entering the query? It may be that the query environment has not been advised that the backspace key is also the erase key.
Posted: Mon Dec 04, 2006 6:08 pm
by vijaykumar
Hi Gurus;
plz help me
iam tying from the command interface.
select @ID,NAME "JOB_NAME" where @id like '%ROOT' from DS_JOBOBJECTS.
why is my select statement not working, is that the syntax error.iam getting below error
ERROR:illegal use of verb select.
cheers;
vijay
Posted: Mon Dec 04, 2006 6:21 pm
by narasimha
Try
Code: Select all
select @ID,NAME "JOB_NAME" from DS_JOBOBJECTS where @ID like '%ROOT';
Posted: Mon Dec 04, 2006 6:29 pm
by kumar_s
So its simple 'FROM' 'WHERE' statement misplace.
Posted: Mon Dec 04, 2006 6:46 pm
by vijaykumar
hi,
Thanks very much, i didn't get any error but the query is not displaying o/p, it doesn't comeup with errors but doesnot give any o/p.
i have tried the other query also, i have given.
select @ID,NAME "JOB_NAME",OBJID,OBJTYPE,OBJNAME,OBJIDNO FROM DS_JOBOBJECTS where @ID like '%ROOT' and OLETYPE like '%DEFN' and OBJTYPE = 'J' orderby OBJIDNO.
the problem is iam not getting any error but the query doesn't write any o/p.it doesn't display any o/p.
plz coorect me if i have done any syntax mistakes.
cheers;
abhi
Posted: Mon Dec 04, 2006 6:58 pm
by narasimha
A slight syntax problem. It is order by and not orderby
Other than that the syntax is ok.
You are not getting any results because you might not have any results matching your query criteria. Check your where clause to match your requirement.
Code: Select all
select @ID,NAME "JOB_NAME",OBJID,OBJTYPE,OBJNAME,OBJIDNO FROM DS_JOBOBJECTS where @ID like '%ROOT' and OLETYPE like '%DEFN' and OBJTYPE = 'J' order by OBJIDNO;
Posted: Mon Dec 04, 2006 7:10 pm
by kumar_s
And the OLETYPE for Jobs are 'CJobDefn'.
So if you change your query as following you can get the result.
Code: Select all
select @ID,NAME "JOB_NAME",OBJID,OBJTYPE,OBJNAME,OBJIDNO FROM DS_JOBOBJECTS where @ID like '%ROOT' and OLETYPE like '%Defn' and OBJTYPE = 'J' order by OBJIDNO
Posted: Mon Dec 04, 2006 7:15 pm
by ray.wurlod
The point made by narasimha is that data values are case sensitive. This is true in all databases.
This is why "DEFN" did not return any rows; the correct partial value is "Defn".
Beware also that OBJIDNO is not a numeric data type, so sorting by it will give "numbers" sorted alphabetically 1, 10, 100, 101, 11, 110, 111, 2, ...
If you do want to sort, ORDER BY is two words, not one. This is true in all forms of SQL.