1 Topic by sonixax (edited by sonixax 2022-03-06 16:12:09)

  • Registered: 2014-10-04
  • Posts: 154

Topic: Databse Caclucated field IIF or CASE

hi everyone,
Can I use IIF in my query in mvdb?

I just tried them both but not working. maybe my query have problem!

here it is:

(SELECT id_boxes, is_terminated IIF(boxes.id = bookings.id_boxes AND bookings.is_terminated = False,'Booked','Free') FROM bookings)

And this is CASE

(SELECT id_boxes,is_terminated CASE WHEN boxes.id = bookings.id_boxes AND bookings.is_terminated = False THEN 'Ja' ELSE 'Nein' END category FROM bookings)

I just want to know if a box is booked or not. it better to return boolean but I don't know how to replace yes no with other words in grid!

Many Thanks

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Databse Caclucated field IIF or CASE

SELECT id_boxes,is_terminated CASE WHEN boxes.id = bookings.id_boxes AND bookings.is_terminated = False THEN 'Ja' ELSE 'Nein' END category FROM bookings


is_terminated - after is_terminated need comma (,)
boxes.id - WHAT TABLE boxes ??? You have only one table (FROM bookings). What are the relationships between these tables - boxes AND bookings?
How to JOIN them ?


How you answer these questions is how you get SELECT ...

  • Registered: 2014-10-04
  • Posts: 154

Re: Databse Caclucated field IIF or CASE

let me show you database schema. maybe it can help.

I have two tables, one boxes and another one is bookings.

I want to have a calculated field in my boxes table which is run a query to find a record with two conditions in bookings:

1- bookings.id_boxes = boxes.id
2- bookings.is_terminated = false (is_terminated is a boolean field)

if find any result then it should return true, yes, ja or similar.
if cannot find any reslut then it will be false, no, nein or similar.

so I can use that field for my future uses like to show if box is booked or cannot booked it again cause its not available.
I also attached project file maybe can help.
many many thanks

Post's attachments

Attachment icon Screenshot 2022-03-06 234219.png 29.15 kb, 85 downloads since 2022-03-06 

  • Registered: 2014-10-04
  • Posts: 154

Re: Databse Caclucated field IIF or CASE

here is the project files

Post's attachments

Attachment icon au2.zip 357.39 kb, 225 downloads since 2022-03-06 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Databse Caclucated field IIF or CASE

Hi Sonixax, Hi Sparrow,
Is this not the same topic as  you posted a few weeks ago?
Can you not go with color coding your boxes to show if they are booked or available as was suggested and so you don't need to worry about yes/no or ja/nein or adding the status to the label?
http://myvisualdatabase.com/forum/viewt … 844#p43844
Derek.

  • Registered: 2014-10-04
  • Posts: 154

Re: Databse Caclucated field IIF or CASE

Hi
Actually no its not, its another problem but same project.
I want to mark a box as unavailable automatically if there is a active booking for it in another table.

I believe using a query is better option rather than delphi script. Its not about changing colors.

Many many Thanks

7 Reply by sparrow (edited by sparrow 2022-03-07 20:01:23)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Databse Caclucated field IIF or CASE

Hi Derek, Sonixax

You need to decide which table you will have as the main one for obtaining data and which one as a reference. The output of information and the solution of the SQL query will be different in FRON .. JOIN ... in this case. Below is the solution for bookings.
Try like this


Further clarification of the request may be required.
is_terminated and other fields can contain NULL , 0, 1, 2 ... . Now checking 0, 1.


And please fill your tables with data to understand and be able to check the request.
It will also make it easier for you to debug your work.

8 Reply by sparrow (edited by sparrow 2022-03-07 20:07:57)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Databse Caclucated field IIF or CASE

Your proj

Post's attachments

Attachment icon au2.zip 357.68 kb, 200 downloads since 2022-03-07 

9 Reply by sparrow (edited by sparrow 2022-03-07 20:38:59)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Databse Caclucated field IIF or CASE

bookings.id_boxes = boxes.id - not quite a clear requirement.
bookings.id_boxes will always be set to boxes.id according to table relationships.
And if bookings.id_boxes cannot be NULL then bookings.id_boxes = boxes.id is always true.
Then everything is simplified.

10 Reply by sonixax (edited by sonixax 2022-03-09 10:03:30)

  • Registered: 2014-10-04
  • Posts: 154

Re: Databse Caclucated field IIF or CASE

Many Many Thanks,
It works but there is a small problem.
query only shows 2 records! which is means it only shows bookings records.
the purpose of my grid views is to show all records (in boxes) and if there is a booking for a certain record mark it as not available.

I believe right join can do the job but its not supported sad

maybe i should do that using scripts and not query? in such a case i can have problem when users want to create a new record for already booked box.

and if I have to using scripts how can I have loop through all gridbox rows?

Many Many Thanks

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Databse Caclucated field IIF or CASE

I understand you correctly that all records from BOXES should be in the tablegrid and their status (yes / no)
should depend on the records in the BOOKINGS table.


If there are two records in the BOOKINGS table with the same ID from BOXES but with a different is_terminated value,
what should we do then? What to display (yes/no)?


Could you draw or fill in the table as you imagine it in the table? With what fields? I will try to help you.

  • Registered: 2014-10-04
  • Posts: 154

Re: Databse Caclucated field IIF or CASE

sparrow wrote:

I understand you correctly that all records from BOXES should be in the tablegrid and their status (yes / no)
should depend on the records in the BOOKINGS table.


If there are two records in the BOOKINGS table with the same ID from BOXES but with a different is_terminated value,
what should we do then? What to display (yes/no)?


Could you draw or fill in the table as you imagine it in the table? With what fields? I will try to help you.


Hi,
Sorry for late,
Yes its correct, It must show all boxes like what I had in my original project (of course filtered by level per grid)
and if there is at least one active booking box marked as not free. so it can simply show yes/no or true false.

a box can booked by two customer at the same time so to mark as unavailable (is_free = false) there must be at least one active booking for that box.

Many Many Thanks

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Databse Caclucated field IIF or CASE

something like this

Post's attachments

Attachment icon au2-m.zip 357.64 kb, 280 downloads since 2022-03-15 

  • Registered: 2014-10-04
  • Posts: 154

Re: Databse Caclucated field IIF or CASE

Many many Thanks,
That was what exactly I need.
I thought MYVDB not supporting nested query