No announcement yet.

select statement help - advanced level

  • Filter
  • Time
  • Show
Clear All
new posts

  • select statement help - advanced level

    hi guys, i have a control application with select statement returns many values... i want to display them in between 2 dates.. i created 2 dates and works fine but i want to display them after the submission of date only..

    currently, i put the sql statement in the onValidate even and it works and created another field to display the values as label.. but it is displayed whatever the start-date and end-date are... i want them only to be displayed if the dates are selected..

    one more issue, once clicking the "ok" button it doesn't calcuate the sql directly, it needs second click to calculate!! any idea!?

    my code
    sc_lookup(data, " SELECT count(distinct(customer)) as customer,
           count(distinct case when video = 1 then customer) as video1,
           count(distinct case when video = 2 then customer end) as video3,
           count(distinct case when video = 3 then customer end) as video4,
           count(distinct case when video = 4 then customer end) as video5,
           count(distinct case when video = 5 then customer end) as video6,
           count(distinct case when video = 6 then customer end) as video7
    FROM my_table
    WHERE date(date_field) BETWEEN '$start' AND '$end'");	
    [customer] ={data[0][0]};
    [video1] ={data[0][1]};
    [video2] ={data[0][2]};
    [video3] ={data[0][3]};
    [video4] ={data[0][4]};
    [video5] ={data[0][5]};
    [video6] ={data[0][6]};
    [video7] ={data[0][7]};
    how to display the data after selecting the date fileds only? using control application? i created second block for data display and put the global variable as html in label field... but not sure why the data is still lappearing even i tried on validateSuccess event

    as there better approach for this?
    Last edited by MikeDE; 10-09-2014, 12:42 AM.

  • #2
    never mind, found the solution somehow,

    as i have the reulsts defined in label fields of the seond block "output" i've added the onLoad event this code

    if(!isset({startdate}) || empty({enddate}))
    { sc_block_display(output, 'off'); }
    else {sc_block_display(ouput, 'on');}
    still don't know why i have to click 2 times the OK button in order to get the result correctly.. first ok click gives no data or 0 or stays as it was from the previous date-range selection, second ok calculate and gets the query correct then returns the data in the block!

    any idea?