[v_field] variable not working!!

Applications Grid / Reports
, ,
#1

Hi all,

-I have this problem: return all rows from db of a certain month which are missing for the previous month.
-I manage to use this query:

*SELECT company, Month

FROM new_clients_view

WHERE new_clients_view.Month = ‘[v_Month]’
AND new_clients_view.company NOT IN (SELECT new_clients_view.company
FROM new_clients_view
WHERE new_clients_view.Month = “DATE_FORMAT([v_Month] - INTERVAL 1 MONTH, ‘%Y-%m’)”)"; *

…separated in grid sql and onscriptInit event.

->SQL:

“SELECT company, Month FROM new_clients_view”

->OnscriptInit event:

"// Check for record
$condition1 = ‘[v_Month]’;

$condition2 = sc_exec_sql(“SELECT new_clients_view.company FROM new_clients_view
where new_clients_view.Month = DATE_FORMAT([v_Month] - INTERVAL 1 MONTH, ‘%Y-%m’)”);

$check_sql = “SELECT DISTINCT new_clients_view.company”
. " FROM new_clients_view"
. " WHERE new_clients_view.Month = ‘" . $condition1 . "’ AND new_clients_view.company NOT IN ‘".$condition2."’";

sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
{new_clients_view.company} = {rs[0][0]};
{new_clients_view.Month} = {rs[0][1]};
}
else // No row found
{
{new_clients_view.company} = ‘’;
{new_clients_view.Month} = ‘’;
}

-The issue: when start to run application, it request “Month” at first, but returns all rows with all registered months in db, not the one i have inputted at first.

…why does this happen??
Any idea?
I would appreciate any comment, thank you!

#2

I have a problem with this:

$condition2 = sc_exec_sql(“SELECT new_clients_view.company FROM new_clients_view
where new_clients_view.Month = DATE_FORMAT([v_Month] - INTERVAL 1 MONTH, ‘%Y-%m’)”);

$condition2 will have the returnvalue from sc_exec_sql.

So

$check_sql = “SELECT DISTINCT new_clients_view.company”
. " FROM new_clients_view"
. " WHERE new_clients_view.Month = ‘" . $condition1 . "’ AND new_clients_view.company NOT IN ‘".$condition2."’";

$condtion2 will not have the correct syntax?

I would suggest

$condition2 = “SELECT new_clients_view.company FROM new_clients_view where new_clients_view.Month = DATE_FORMAT(’”.[v_Month]."’ - INTERVAL 1 MONTH, ‘%Y-%m’)";

or something like that.

#3

Hey Albert, thanks for replying!
I tried this and still the same result…
instead of displaying only the record that have new_clients_view.Month = ‘2012-05’ (the test) it shows for all months!

#4

You’re welcome. Are you sure the sql you are using is right? I advise you to go to application and set checkboxes for debugging sql. Then all sql will be displayed before execution. This way you can grab the sql from the screen and test it in sql builder to see what might be wrong. Sometimes, SC is adding his own code to it if this sql is in the sql box of the menu which might disturb your expected results.