Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

cbaqir
Valued Contributor

Where criterion SQL Syntax

These should be so simple and neither are working. What is wrong?

Objects:
LOAD ID,
   ObjectType
,
   ObjectModifiedTimeUtc;
SQL SELECT *
FROM Objects
where ObjectType = 'AnnotationTopic';

Annotations:
LOAD ID,
FileName,
   AnnotationTitle,
AnnotationText,
AnnotationAuthor,
   AnnotationParentObjectId,
AnnotationCreationTime,
AnnotationModificationTime
SQL SELECT *
FROM Annotations
where FileName in ('*AU FINANCIAL*');

7-6-2016 11-29-06 AM.gif

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Where criterion SQL Syntax

It looks like you are using the QvServerObjectConnector. AFAIK that connector does not support Where clause. I believe it only supports a basic "SELECT Field" syntax.

If you want to do filtering, do it in the preceding load statement.

Objects:
LOAD ID,
  ObjectType
,
  ObjectModifiedTimeUtc

where ObjectType = 'AnnotationTopic';

;
SQL SELECT *
FROM Objects;


Also be aware that I have encountered a bug with the Objects table where output is sometimes incomplete, but no error is thrown.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com



View solution in original post

6 Replies
Highlighted
sinanozdemir
Valued Contributor III

Re: Where criterion SQL Syntax

I would use LIKE operator in SQL so:

Objects:
LOAD ID,
  ObjectType
,
  ObjectModifiedTimeUtc;
SQL SELECT *
FROM Objects
where ObjectType Like 'Annotation Topic';



And in this one, I would swap out "*" for "%". I am assuming you are using MS SQL.

Annotations:
LOAD ID,
FileName,
  AnnotationTitle,
AnnotationText,
AnnotationAuthor,
  AnnotationParentObjectId,
AnnotationCreationTime,
AnnotationModificationTime
SQL SELECT *
FROM Annotations
where FileName Like '%AU FINANCIAL%';

Hope this helps.

cbaqir
Valued Contributor

Re: Where criterion SQL Syntax

I tried this as well but it didn't work. ???

MVP & Luminary
MVP & Luminary

Re: Where criterion SQL Syntax

It looks like you are using the QvServerObjectConnector. AFAIK that connector does not support Where clause. I believe it only supports a basic "SELECT Field" syntax.

If you want to do filtering, do it in the preceding load statement.

Objects:
LOAD ID,
  ObjectType
,
  ObjectModifiedTimeUtc

where ObjectType = 'AnnotationTopic';

;
SQL SELECT *
FROM Objects;


Also be aware that I have encountered a bug with the Objects table where output is sometimes incomplete, but no error is thrown.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com



View solution in original post

cbaqir
Valued Contributor

Re: Where criterion SQL Syntax

Thanks!

MVP & Luminary
MVP & Luminary

Re: Where criterion SQL Syntax

Hi Cassandra,

It looks like you may be working on something similar to something I never finished due to lack of interest.

Looking for help with new Notes & Annotations tool

I've attached the code as far as I got. Feel free to use any of it that helps.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

cbaqir
Valued Contributor

Re: Where criterion SQL Syntax

What about using the 'like' condition? I'm still having issues with that.