facing issue in advanced SQL
Question
Application Type
Reactive

SELECT TOP (32) {Amenities}.[Id] o0, {Amenities}.[Label] o1, {Amenities}.[Order] o2, {Amenities}.[Is_Active] o3
FROM {Amenities}
where ({Amenities}.[Is_Active] = 1) and 
      ({Amenities}.[Id]
not in
(SELECT TOP (32) {Amenities}.[Id] o0, {Amenities}.[Label] o1, {Amenities}.[Order] o2, {Amenities}.[Is_Active] o3, {Room}.[Id] o4, {Room}.[RoomNumber] o5, {Room}.[AdultsCapacity] o6, {Room}.[ChildrenCapacity] o7, {Room}.[Price] o8, {RoomAmenities}.[Id] o9, {RoomAmenities}.[RoomId] o10, {RoomAmenities}.[AmenitiesId] o11
FROM {RoomAmenities}
    Left JOIN  {Room} ON ({RoomAmenities}.[RoomId] = {Room}.[Id])) 
    Left JOIN  {Amenities} ON ({RoomAmenities}.[AmenitiesId] = {Amenities}.[Id])) 
WHERE ({Amenities}.[Is_Active] = 1)
    AND (({RoomAmenities}.[RoomId] = @RoomAmenities_RoomId) AND ({RoomAmenities}.[RoomId] IS NOT NULL))))


RukkusHotel.oml

mvp_badge
MVP
Solution

Hi Rukkunudeen,


You do that by adding a Data Action to your screen, by right clicking the screen and adding a Fetch Data from Other Sources option:




On the Data Action, you have to define the expected output (in this case a list of amenities), place the query and assign the result of the query to the output:

 


After that, you can use the data action output in the screen widgets (dropdown, list, etc.), like in the example below:


For more information, you can refer to the OutSystems documentation on Data Actions.


Kind Regards,
João

mvp_badge
MVP

Hi Rukkunudeen,


May I suggest a cleaner query?


SELECT
    {Amenities}.*
FROM
    {Amenities}
WHERE
    {Amenities}.[Is_Active] = 1
        AND NOT EXISTS (SELECT 1 FROM {RoomAmenities} WHERE {RoomAmenities}.[AmenitiesId] = {Amenities}.[Id] AND {RoomAmenities}.[RoomId] = @RoomAmenities_RoomId)



What the query does very simply is to get the active amenities with no correspondent room amenity for a given room id (input parameter).


Kind Regards,
João

Thanks, Joao. I need further help to publish it on a screen. I tried LIST, dropdown; but those are not working. How to publish the output from the SQL onto a screen. Can you please help

mvp_badge
MVP
Solution

Hi Rukkunudeen,


You do that by adding a Data Action to your screen, by right clicking the screen and adding a Fetch Data from Other Sources option:




On the Data Action, you have to define the expected output (in this case a list of amenities), place the query and assign the result of the query to the output:

 


After that, you can use the data action output in the screen widgets (dropdown, list, etc.), like in the example below:


For more information, you can refer to the OutSystems documentation on Data Actions.


Kind Regards,
João

Thanks a lot, Joao. Its working fine. (was not aware SQL output can be stored as list)

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.