Display of Data

[daniel32]

Hi,

Got a question for everyone. I have a datawindow select statement with a union that is working ok. But, for whatever reason when the code below was run in the application the data retrieved was not displaying in the datawindow.

convert(char(8),dbFFENDER_WORK_SCHEDULE.scheduled_date,112) > =
convert(char(8),:start_date,112)) and
convert(char(8),dbFFENDER_WORK_SCHEDULE.scheduled_date, 112) <=
convert(char(8),:end_date,112))

We changed the retrieval arguments for the start and end date to strings instead of datetimes and wrote the above lines of code as followed.

convert(char(8), dbFFENDER_WORK_SCHEDULE.scheduled_date >= :start_date
convert(char(8), dbFFENDER_WORK_SCHEDULE.scheduled_date <= :end_date.

When we did this, the data displayed in the datawindow when the application was run.

Does anyone have any idea as to why the data would not display when we were using datetime for the retrieval arguments instead of string.

TIA,

Dan Sellers

 

[Guest_imported]

> But, for whatever reason when the code below was run
> in the application the data retrieved was not displaying > in the datawindow.

Do you mean that there were no rows retrieved or that
you couldn't see them on the DW?

If it is the first case (I guess) then it seems like the
SELECT statement that PB creates ain't exactly what you
would expect.

To see for your own do this:

1) Create a MultiLine Edit control, say mle_sql

2) In the sqlpreview(..) event of the DW you wish to
populate enter:

mle_sql.Text = sqlsyntax

3) Review the actuall SQL statement PB is creating
when you do your Retrieve()

 

[daniel32]

There was data retrieved, but, it was not displayed in the datawindow. The SQL statement worked fine when we ran it separately from the datawindow.

 

[rt63]

By any chance, have u put front end filters in the datawindow?

 

[daniel32]

No, no filters or anything like that.

 

[Guest_imported]

I still find it hard to think that the SQL statement
that PB created returned rows but then PB wasn't able
to dispaly them. How are you sure that there were
indeed some rows retrieved?

You mentioned that:
> The SQL statement worked fine when we ran it separately
> from the datawindow.

Was that the actuall statement that PB generated (that
was the reason for that MLE trick - to capture that)
or the one you figured that would get generated?

You see, PB amongst others might apply some mask to
the retrieval arguments or use certain quotation characters
or ... etc etc

Compare the differences in what displays in that MLE for
the different cases of datetime and string and you will
notice that. Now comes into play the actuall database
implementation. Not all databases treat query arguments
in the same way. The usage or type of quotation characters
the format and decimal char (if numeric) etc etc make
quite some difference.

The safest way is usually to have either integer (Integer,
Long etc) or &quot;-delimited string retrieval arguments. But
even that can't be guaranteed.

On the other hand if there were rows retrieved but not
displayed then this is very peculiar.

 

[daniel32]

We set a variable of ll_rows to capture the number of rows retrieved and it came back with a number greater than 0 meaning there was data retrieved. But, like you said phasma this is very pecuilar. Because, even though there was data retrieved and we were sure of it, it wouldn't display until we changed the retireval arguments to string.

I guess it is just one for the ages...

 

[Guest_imported]

OK I see. Seems to be related with the relative
position of the planets or something...