Sql - Exists not working properly

[HappyAlligator]

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

[NailDyanC]

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

[Kurumi]

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/

[HappyAlligator]

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 January 12, 2022 by HappyAlligator

[HappyAlligator]

My problem in general is to know what Caspio bring when there is no data in the SELECT or in the field of the SELECT.

null, blank, "", " "?

[Kurumi]

Hi @HappyAlligator - you may check these forum posts:

 

[cheonsa]

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!  

[cheonsa]

Just to add, if you would like to use exists function, you may check the correct format here:

https://www.w3schools.com/sql/sql_exists.asp

 

Cheers!