Sql not fetching the correct values

Hi All,

I'm trying to export data in excel file depending upon the inputs provided by the user in the input box.

This is the logic I've applied in server action which gets triggered on the click of download report button:-

This Logic isn't working can somebody tell me where I am going wrong?


Thanks & Regards 

Sahil

mvp_badge
MVP
Solution

Hi sahil,

Some points you can adjust in your SQL are:

1 - Always use .[Field] for fields, with [].

2 - Example of how to use BETWEEN.

hope it's help.

Regards.

mvp_badge
MVP

Hi Sahil,

Are you getting any results from your advanced SQL? or result is empty?

Or you are getting any errors while performing that action?

Can you please share that detail? so we can diagnose it further.

Regards,

Manish Jawla


Hi Manish,

I'm getting empty excel file even though I've records for the date I enter in the input fields. There are no errors.

Regards

Sahil

mvp_badge
MVP
Solution

Hi sahil,

Some points you can adjust in your SQL are:

1 - Always use .[Field] for fields, with [].

2 - Example of how to use BETWEEN.

hope it's help.

Regards.

Hi @sahil saxena 

I think the problem is on those between in where clause.

Remove those between and test it.

I think you need something like "and {initiative}.[InitialDate] between @FromDate and @ToDate"

Regards

Gonçalo Almeida

Hi Agno,

I've changed the logic as per your advice, but still I'm getting result as empty.

 However, I just noticed I'm getting the following exception as soon as I fill start date input box and click outside.

Regards

Sahil

Hello Sahil,

I would recommend testing the SQL Query in Service Studio first (if not already done) prior to running/testing the button click logic.

For the desired Test Inputs do you get the expected Test Output in Service Studio?

Regards,

mvp_badge
MVP

Hi Sahil,

First focus on your SQL to fetch the desired data, you can start by writing simple SQL first without where condition or less numbers of joins and then slowly add more condition to refine your result, Otherwise there is no point directly jump to download the result into excel and instead of download button it would be better to have table widget so see what data you are pulling after applying the filter, then you can add the logic for download it into excel.

Regards,

Manish Jawla  

Hi Manish,

Earlier the query was working fine, it was returning all the initiatives in excel file but the business requirement changed and because of which I had to introduce these date filters in where clause. Now it is displaying empty records in excel file. The business doesn't want the records to be shown in table format rather it should be downloaded in excel format on the click of download button. 

Regards,

Sahil

mvp_badge
MVP

Hi Sahil,

Can you share the sample oml? because I tried to reproduce your issue in my personal environment and it's working fine or can you please check the data in DB as well against the scenario?

Regards,

Manish Jawla

Champion

HI 

If possible, please share the oml file.

Also please try to test SQL in the designer.

Kind regards,


Hi @sahil saxena 

In clause where add parentheses to your query, like 

...

where {initiative}.[payingMethodId]=2 and ({initiative}.[FinalDate] between @FromDate and @ToDate)

...


regards

Gonçalo Almeida

Hi Goncalo,

It is not working. The result is same.

Regards,

Sahil

Sahil.. did you Run the test ?

It works?


If not, take a screenshot from the query and from the test input tab, please

Regards

Gonçalo Almeida

Hi Everyone,

Thank you so much for your help, the issue was from database end. It has been fixed now.

Regards,

Sahil

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