Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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
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.