Basic Select Query Help (MySql)

Scriptcase 9 General
#1

Hi,

This seems so basic that I’m at my wits end and don’t understand why I can’t get this query to work in a grid. I need to know how many people were hired and fired in a given date range. When I use the SQL builder and use regular dates (as I did in the Hireddates line below), the query works as expected. But as soon as I make the dates into variables (so I can use it in the grid), the query no longer shows a 1 for the ‘hired’ folks anymore - it runs fine with no errors, but the Hired column is blank. Here is the query:

SELECT
name,
location,
HireDate,
termed,
if(termed is null,0,1) as ended,
if((HireDate BETWEEN ‘2018-03-04’ AND ‘2018-03-16’),1,null) as Hireddates,
if((HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’),1,null) as Hired
FROM
sec_users
WHERE
(termed >= ‘[recrut_start]’) AND (termed <= ‘[recrut_end]’) OR
(HireDate >= ‘[recrut_start]’) AND (HireDate <= ‘[recrut_end]’)

The query pulls the correct record count, so the variables are working to the best of my knowledge. Maybe there’s an easier way of doing this, but the last two columns of the query are identical other than the dates being written out vs using variables… and when I run the grid, and enter the same dates to meet the variable prompts, Hireddates shows a 1 for the folks that were hired between those dates, but ‘Hired’ is all null. I’ve tried converting the HireDate and the variables to Unix time, and still get the same result.

Any idea what I’m missing here? It’s driving me absolutely nuts…

TIA…Mark.

#2

maybe the where clause needs extra parentheses?

SELECT
name,
location,
HireDate,
termed,
if(termed is null,0,1) as ended,
if((HireDate BETWEEN ‘2018-03-04’ AND ‘2018-03-16’),1,null) as Hireddates,
if((HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’),1,null) as Hired
FROM
sec_users
WHERE
((termed >= ‘[recrut_start]’) AND (termed <= ‘[recrut_end]’)) OR
((HireDate >= ‘[recrut_start]’) AND (HireDate <= ‘[recrut_end]’))

#3

Thanks for the tip bwalujo,

I added the extra parentheses and still getting a blank field for folks hired between the two date variables. The records in the result are definitely between the date variables, but the Hired column is still null. It’s as though the query translates the variables in my WHERE clause but not in my IF for the Hired column.

SELECT
name,
location,
HireDate,
termed,
if(termed is null,null,1) as ended,
if(HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’,1,null) as Hired
FROM
sec_users
WHERE
((termed >= ‘[recrut_start]’) AND (termed <= ‘[recrut_end]’)) OR
((HireDate >= ‘[recrut_start]’) AND (HireDate <= ‘[recrut_end]’))

Really appreciate the suggestion though!

#4

Another thing to try, instead of using between [] and []…try using >= and <=

#5

Thanks again bwalujo,

I had tried that already but tried it again. It should absolutely work…but it doesn’t. I’m going to try and do a sub query to get the 1 on the Hired and join them to the employee that way. Shouldn’t need to, but as you can see, something just doesn’t work properly when I do the variables. Again, I appreciate your suggestion! Part of me is glad I wasn’t off base on this =)

For the record:

SELECT
name,
location,
HireDate,
termed,
if(termed is null,null,1) as ended,
if(HireDate >= ‘2018-03-04’ AND HireDate <= ‘2018-03-16’,1,null) as testdate,
if(HireDate >= “[recrut_start]” AND HireDate <= “[recrut_end]”,1,null) as test
FROM
sec_users
WHERE
(((termed >= ‘[recrut_start]’) AND (termed <= ‘[recrut_end]’)) OR
((HireDate >= ‘[recrut_start]’) AND (HireDate <= ‘[recrut_end]’)))

Generates the attachment. When the dates are manually entered in the string, works like a champ…but using a calendar to populate variables, shows the correct records, but doesn’t show the ‘1’ for test.

Cheers…Mark.

temp.png

#6

Check the format of the ‘[recrut_start]’ and ‘[recrut_end]’. Echo them to see the exact format. Maybe the are not in the correct format. If not change the format. I had this problem before and solved it using this method

maybe this can help

#7

How about using the debug-mode ?

If you switch on the ‘Debug-Mode’ in the application, it should show you all SQL commands when they are executed.
Maybe you can see what’s going wrong in your SELECT…

This should be available for any Scriptcase application in the development and the deplayment.

You can find the setting in Application > settings > error settings (almost at the end of the page).

Regards

#8

Thanks to nonkelmike and GunerEibl for the feedback and I’m on to something based on the advice. Turning on Debug shows my variables are not being translated like they are in the rest of the query. When I run this query:

SELECT
Account,
name,
avg_hours,
HireDate,
Location,
If(termed IS NULL,NULL,1) as ended,
If(HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’,1,null) as started,
If(termed IS NULL,NULL,avg_hours) as LostHrs,
if(termed IS NULL,NULL,datediff(termed,HireDate)) AS duration,
termed
FROM
sec_users
WHERE
HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’ OR termed BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’
ORDER BY
CASE WHEN termed is null THEN 1 ELSE 2 END, name

I get this in debug mode:

SIZE=12px: SELECT name, Location, HireDate, termed, If(termed IS NULL,NULL,1) as ended, If(termed IS NULL,NULL,avg_hours) as losthrs, if(termed IS NULL,NULL,datediff(termed,HireDate)) as duration, If(HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’,1,null) as started, avg_hours from sec_users where HireDate BETWEEN ‘20180302’ AND ‘20180327’ OR termed BETWEEN ‘20180302’ AND ‘20180327’ order by Location asc, CASE WHEN termed is null THEN 1 ELSE 2 END asc, name asc LIMIT 0,17 [/SIZE]

As you can see, the variables in the top part of my select (looking to get a 1 if the HireDate is between the start and end dates) are left as the raw code, versus converting to the dates as they did in the latter portion of the query. That explains why I don’t get my ‘1’! I’ve never experienced this sort of behavior in a select grid. Any ideas?

Thanks again for your help! Now I know I’m not crazy…

#9

how and where are you passing the value into [recrut_start]?

#10

This seems to be a bug in ScriptCase :confused:

I tried to simplyfy it a bit with a SQL statement as follows:

I used a grid based on a table with two fields only (id, test_1) and [t_id] = 2

SELECT
      id,
      test_1, 
      [t_id] as temp_id 
FROM
      test_1
WHERE
      id > [t_id]

The application for sure knows the value of [t_id] before executing the SQL:
If I put an echo into onApplicationInit like

echo "Global var:" . [t_id];

it does show the value.

But it does not replace the [t_id] in the SQL statement (first part). However it does in the second part:

Debug-output:
(pdo-mysql): SELECT id, test_1, [t_id] as temp_id from test_1 where id > 2 LIMIT 0,12

I would suggest, that you report this as a bug to ScriptCase.

It would be great, if you keep us updated about the answer from Scriptcase :wink:

#11

Good morning (afternoon for you!) Gunter,

To answer your question, I have a control pop up, prompting for start and end dates. The user would just click on the little calendar icon, and select the appropriate dates. I’ve used the approach on many many applications I’ve created, so figured it should work just fine. I put my date variables into the Title Header so I could confirm they were being created/formatted properly (and they were), but didn’t think about the debug mode that shows where the real problem exists.

I will definitely report the bug and keep this thread up to date with their feedback. Thanks for all your help and confirming the problem!

Have a great weekend,

Mark.