Jump to content
  • 0

Sql - Exists not working properly


HappyAlligator
 Share

Question

Hello Caspio community!

I have a little idea but i can't make it work, is something like this:

CASE
  WHEN EXISTS(SELECT name FROM myTable WHERE name = target.[@field:name]) THEN 1
ELSE 0
END

My problem i get the "Not records found" of Caspio instead of 0 and i don't know where i'm mistaking. In the future I pretend to count how much tickets a client have and show it, if that client never create  a ticket is not going to be in myTicketsTable yet for obvious reasons

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hi @HappyAlligator - are you using a Report DataPage? If yes, I suggest using the Filter and Criteria in your Report. Or you can use View instead. For more information, you can check these links: 
https://howto.caspio.com/parameters/tech-tip-custom-filter-elements/
https://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-filter-data/
https://howto.caspio.com/datapages/reports/search-and-filter-comparison-types/

Link to comment
Share on other sites

  • 0
Posted (edited)

Yes, i am using a Report Datapage, I tried to use:

IsNull(
(
SELECT COUNT(name)
FROM _v_My_View
WHERE My_View_name = target.[@field:name]
),0)

but don't work either so i decided to go for something much more simple in the beginning (what i post before) to see if i can make it work.

The problem in general as far i can see is when there is no such target.[@field:name] in my table/view when instead of 0 or NULL i just get "error". @Meekeeemaybe you know what to do, i ran out of ideas.

Edited by HappyAlligator
Link to comment
Share on other sites

  • 0

Hi, just add in the previous comments above, do you want to put a value like zero(0) once the SELECT statement returns null? If that is the case, you may try this one: 

CASE
  WHEN (SELECT name FROM myTable WHERE name = target.[@field:name]) = '' THEN 1
ELSE 0
END

Link to comment
Share on other sites

  • 0

Hello @HappyAlligator,

If you are getting No records found in the report, make sure that there is at least one record on your Table. Then, to check if the value is existing in the Table or not, you can use this formula:

CASE WHEN (SELECT Count(Name) FROM Table WHERE Name = target.[@field:Name]) < 0 THEN 1 ELSE 0 END

Hope this helps! :) 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...