locked
IF statement within WHERE clause RRS feed

  • Question

  • Hello Experts

    Is it possible to put a IF statement within a where clause?

    here are the two statements

    startdate between ldStartDate AND ldEndDate
    
    and
    
    ALLTRIM(UPPER(TRANSFORM(startdate))) = m.ldstartdate

    please help 

    Saturday, February 4, 2012 3:53 AM

Answers

  • Do you have date or datetime field in your database? If it's a datetime, then you may want to use

     

    ldStartDate = thisform.txtFromDate.value
    
    ldEndDate = IIF(empty(thisform.txtEndDate.value), thisform.txtFromDate.value, thisform.txtEndDate.value) + 1 && add one extra day
    
    select ...
    
    WHERE a.StartDate >=m.ldStartDate and a.StartDate < m.ldEndDate
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by mikelvfp Sunday, February 5, 2012 5:56 PM
    Sunday, February 5, 2012 4:28 PM
  • You still overlook and ignore the most important part of my and naomis answer: When using between on datetimes, add +1 day to the enddate.

    It might help to think in a concrete example:

    Assume you have an appointment on the 1st March 2012 from 9:00 am to 10:00 am. Then what's stored in the appointment record are startdate =  1st March 2012, 9:00 am and enddate = 1st March 2012, 10:00 am.

    Now the user enters 1st March 2012 for searching appintments at that date, so you set ldStartdate = CTOD("03/01/2012") and set enddate to the same date. That means in terms of datetimes you now search between 1st March 2012, 0 am (midnight) up to the same datetime (!)

    That means you search within a 0second interval. No surprise you don't find anything in it. You would only find appointments with a startdatetime at midnight, and only that value.

    Think in datetimes, even if you allow the user to enter dates for searches, you need to think in datetimes, as your data is in datetimes! So this translates to start and end datetimes with a difference of 1 day, from midnight to midnight.

    All the datetimes of the 1st March 2012 are between midnight of that day and midnight of the next day! So to search for the data of a day, enddate must be one higher.

    Now foxpros feature to be able to compute the next date by +1 comes into play, as you use CTOD() that will work, if you'd use CTOT() that would also work, but just add one second(!).

    So in terms of code you need:

    ldStartDate = CTOD(thisform.txtFromDate.value)
    ldEndDate = CTOD(thisform.txtToDate.value)+1
    IF EMPTY(ldEndDate)
    ldEndDate = m.ldStartDate+1
    ENDIF

    You always need +1 day. Even if the user wants data from 1st up to 3rd march, 3rd march translates to midnight 3rd march, that's just the begin of that day, but you want all data of that day, too, so you have to add 24h or 1 day - always.

    That's just the consequence of thinking in datetimes, you have to do that consequently. Querying for a single day means querying for 24h, it's an interval that has a length of a whole day. If you data was holding the date and time seperately you could find the data of a certain day without a shifted enddate, but would introduce all the other problems of eg checking overlaps et, which is much easier to cope with in datetimes format.

    The other way around is alsow possible, then you would put TTOD(startdate) to chop off the time portion and then could query for all data of a single day via TTOD(startdate) = entered date.

    In my oppinion it's much easier to cope with this by adding 24h to the enddate to justify you're searching within datetime data than using TTOD(), even more so having in mind you surely have indexed startdate, so it's much better to use where clauses using startdate and not an expression like TTOD(startdate).

    Bye, Olaf.

    • Edited by Olaf Doschke Sunday, February 5, 2012 5:38 PM
    • Marked as answer by mikelvfp Sunday, February 5, 2012 5:55 PM
    Sunday, February 5, 2012 5:26 PM

All replies

  • Hi,

    you can use IIF Statemet with where clause.

    but you can also like this

    lcSql = "select * from YourTableName " 

    if(condition = .t.)

       lcSQL = lcSQL + "where startdate between ldStartDate AND ldEndDate"

    else 

       lcSQL = lcSQL + "where ALLTRIM(UPPER(TRANSFORM(startdate))) = m.ldstartdate"

    endif 

    eval(lcSQL)

     

    HTH

    Kalpesh


    Please "Mark as Answer" if this post answered your question. :)

    Kalpesh Chhatrala | Software Developer | Rajkot | India

    Kalpesh 's Blog

    VFP Form to C#, Vb.Net Conversion Utility
    Saturday, February 4, 2012 5:43 AM
    Answerer
  • Hello Experts

    Is it possible to put a IF statement within a where clause?

    here are the two statements

    startdate between ldStartDate AND ldEndDate
    
    and
    
    ALLTRIM(UPPER(TRANSFORM(startdate))) = m.ldstartdate


    You have to explain how the IF statement should work in the WHERE clause.

    Solution from Kalpesh will work, you just have to use

    &lcSql

    instead of eval(lcSql)

    Saturday, February 4, 2012 12:13 PM
  • HI Pavel I think your right, the reason I want to use an IF statement is because it could either be one or the other. (between dates or single date)

    here is what I have so far

     

    SELECT a.startdate, a.enddate, ;
           ALLTRIM(c.firstname)+" "+ALLTRIM(c.LastName) AS NAME,;
    b.hst, b.subtotal, b.grandtotal FROM appointment a ;
    LEFT JOIN billing b ON a.appointid = b.appointid ;
    INNER JOIN customers c ON c.customerid = a.customerid ;
    WHERE startdate between ldStartDate AND ldEndDate AND ;
    ALLTRIM(UPPER(TRANSFORM(startdate))) = m.ldstartdate ;
    ORDER BY a.startdate ;
    INTO CURSOR curoutput

    I tried using Kelpesh example but it wont work. BTW I am doing Automation from VFP to EXCEL.

     

    Saturday, February 4, 2012 1:46 PM
  • You can use between with equal dates

    ldStartDate = DATE(2011,1,5)

    ldEndDate  = DATE(2011,1,5)

    SELECT ...;

    WHERE startdate between ldStartDate AND ldEndDate AND ;
    ...

    Where is the problem?

     


    Systems Analyst
    Saturday, February 4, 2012 2:23 PM
  • First of all, why do you convert the date by ALLTRIM(UPPER(TRANSFORM(startdate))) ? Second, you don't need the IF statement in SQL command but you may use it before the SQL:

    ldStartDate = <Your Start date value>
    IF <EndDateIsDefined>
      ldEndDate = <Your End date value>
    ELSE
      ldEndDate = m.ldStartDate
    ENDIF
    
    SELECT a.startdate, a.enddate, ;
           ALLTRIM(c.firstname)+" "+ALLTRIM(c.LastName) AS NAME,;
    b.hst, b.subtotal, b.grandtotal FROM appointment a ;
    LEFT JOIN billing b ON a.appointid = b.appointid ;
    INNER JOIN customers c ON c.customerid = a.customerid ;
    WHERE a.startdate between m.ldStartDate AND m.ldEndDate ;
    ORDER BY a.startdate ;
    INTO CURSOR curoutput
    
    

    Of course, if you remove the ALLTRIM(UPPER(... conversion you may use the approach described by Kalpesh.

    • Proposed as answer by Naomi N Sunday, February 5, 2012 4:25 PM
    Saturday, February 4, 2012 3:11 PM
  • Ok it works only one problem my search for between dates works, my search for a single date does not, it just doesnt find anything although records do exist?

    heres what I have

    ldStartDate = CTOD(thisform.txtFromDate.value)
    IF VARTYPE(ldEndDate) <> thisform.txtToDate.value
      ldEndDate = CTOD(thisform.txtToDate.value)
    ELSE
      ldEndDate = m.ldStartDate
    ENDIF
    
    
    SELECT a.startdate, a.enddate, ;
           ALLTRIM(c.firstname)+" "+ALLTRIM(c.LastName) AS NAME,;
    b.hst, b.subtotal, b.grandtotal FROM appointment a ;
    LEFT JOIN billing b ON a.appointid = b.appointid ;
    INNER JOIN customers c ON c.customerid = a.customerid ;
    WHERE a.startdate between m.ldStartDate AND m.ldEndDate ;
    ORDER BY a.startdate ;
    INTO CURSOR curoutput

    please help

     

    Saturday, February 4, 2012 4:55 PM
  • Place SET STEP ON at the first line and look at all variable values in debugger. It should tell what's wrong.

    BTW, the test  "IF VARTYPE(ldEndDate) <> thisform.txtToDate.value"  does not look as a standard programming practice. I would recommend

    ldStartDate = CTOD(thisform.txtFromDate.value)
    
    IF EMPTY(thisform.txtToDate.value)
      ldEndDate = m.ldStartDate
    ELSE
      ldEndDate = CTOD(thisform.txtToDate.value)
    ENDIF
    
    
    

    Also the input textboxes could be of Date data type and you don't need any conversion. Place empty date to their initial value.

    Saturday, February 4, 2012 7:45 PM
  • Including Pavels suggestion will surely help, but even if ldEndDate = m.ldStartDate make sure you're comparing the right stuff. If I remember correctly your startdate and enddate fields are actually datetimes, comparing a datetime with a date is possible, vfp does an indirect type conversion of the date to a datetime with midnight as it's time portion, but only that datetime at midnight will then be equal to that date.

    So if you want to query the data of a date, you better simply select between adate and adate+1, which makes the time interval searched midnight of the date in question until midnight of the next day.

    Besides, make your txtbox.value = date() or {} in the property window, and it is of type date and you will not need CTOD on it and have validation of the entered date, eg it's impossible to enter the 30.02. into a textbox with a date value, while it's possible to do so if the value is just a char value looking like a date.

    Bye, Olaf.

    Sunday, February 5, 2012 1:00 PM
  • thank you Olaf I noticed I was comparing the wrong fields heres what I have but it still does not find my single dates? it only finds my between dates

    ldEndDate = CTOD(thisform.txtToDate.value)
    ldStartDate = CTOD(thisform.txtFromDate.value)
    
    IF EMPTY(thisform.txtToDate.value)
    	ldEndDate = m.ldStartDate
    ENDIF
    
    SELECT a.startdate, a.enddate, ;
           ALLTRIM(c.firstname)+" "+ALLTRIM(c.LastName) AS NAME,;
    b.subtotal, b.hst, b.grandtotal FROM appointment a ;
    LEFT JOIN billing b ON a.appointid = b.appointid ;
    INNER JOIN customers c ON a.customerid = c.customerid ;
    WHERE BETWEEN(a.startdate, m.ldStartDate, m.ldEndDate) ;
    into cursor csrTemp nofilter

    I already tried making the txtboxes = DATE() I still get the same result? I thought maybe changing the way i use the between function would help but it doesnt.

    Sunday, February 5, 2012 2:45 PM
  • Do you have date or datetime field in your database? If it's a datetime, then you may want to use

     

    ldStartDate = thisform.txtFromDate.value
    
    ldEndDate = IIF(empty(thisform.txtEndDate.value), thisform.txtFromDate.value, thisform.txtEndDate.value) + 1 && add one extra day
    
    select ...
    
    WHERE a.StartDate >=m.ldStartDate and a.StartDate < m.ldEndDate
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by mikelvfp Sunday, February 5, 2012 5:56 PM
    Sunday, February 5, 2012 4:28 PM
  • Hi naomi

     

    In my database appointment.startdate and appointment.enddate are both datetime, but my txtFrom and txtTo are not bound to any of my tables. I have a calender button right beside each txtbox, txtboxes are char, I call this calender converting my char txtbox to CTOD

    txtfromdate

    LOCAL dtresponse, oformCal as Form 
    LOCAL dt
    
    dt = CTOD(thisform.txtFromDate.value)  && convert char type to date type
    dt = IIF(EMPTY(dt), DATE(), dt)
    
     		
    do form cal NOSHOW NAME oformCal WITH dt TO dtresponse
    oformCal.Top = thisform.top + this.txtFromDate.Top + 55
    oformCal.Left = thisform.Left + this.txtFromDate.Left + 5
    oformCal.Show(1)
    IF !EMPTY(dtresponse)
    	thisform.txtFromDate.Value = TRANSFORM(dtresponse)  && convert back to string (character type)
    ENDIF
    thisform.Refresh()

     

    txttodate

    LOCAL dtresponse, oformCal as Form 
    LOCAL dt
    
    dt = CTOD(thisform.txtToDate.value)  && convert char type to date type
    dt = IIF(EMPTY(dt), DATE(), dt)
    
     		
    do form cal NOSHOW NAME oformCal WITH dt TO dtresponse
    oformCal.Top = thisform.top + this.txtToDate.Top + 55
    oformCal.Left = thisform.Left + this.txtToDate.Left + 5
    oformCal.Show(1)
    IF !EMPTY(dtresponse)
    	thisform.txtToDate.Value = TRANSFORM(dtresponse)  && convert back to string (character type)
    ENDIF
    thisform.Refresh()

    hopefully this will help solve my problem, maybe i should have mentioned this from the beginning


    please help

    Sunday, February 5, 2012 4:50 PM
  • You still overlook and ignore the most important part of my and naomis answer: When using between on datetimes, add +1 day to the enddate.

    It might help to think in a concrete example:

    Assume you have an appointment on the 1st March 2012 from 9:00 am to 10:00 am. Then what's stored in the appointment record are startdate =  1st March 2012, 9:00 am and enddate = 1st March 2012, 10:00 am.

    Now the user enters 1st March 2012 for searching appintments at that date, so you set ldStartdate = CTOD("03/01/2012") and set enddate to the same date. That means in terms of datetimes you now search between 1st March 2012, 0 am (midnight) up to the same datetime (!)

    That means you search within a 0second interval. No surprise you don't find anything in it. You would only find appointments with a startdatetime at midnight, and only that value.

    Think in datetimes, even if you allow the user to enter dates for searches, you need to think in datetimes, as your data is in datetimes! So this translates to start and end datetimes with a difference of 1 day, from midnight to midnight.

    All the datetimes of the 1st March 2012 are between midnight of that day and midnight of the next day! So to search for the data of a day, enddate must be one higher.

    Now foxpros feature to be able to compute the next date by +1 comes into play, as you use CTOD() that will work, if you'd use CTOT() that would also work, but just add one second(!).

    So in terms of code you need:

    ldStartDate = CTOD(thisform.txtFromDate.value)
    ldEndDate = CTOD(thisform.txtToDate.value)+1
    IF EMPTY(ldEndDate)
    ldEndDate = m.ldStartDate+1
    ENDIF

    You always need +1 day. Even if the user wants data from 1st up to 3rd march, 3rd march translates to midnight 3rd march, that's just the begin of that day, but you want all data of that day, too, so you have to add 24h or 1 day - always.

    That's just the consequence of thinking in datetimes, you have to do that consequently. Querying for a single day means querying for 24h, it's an interval that has a length of a whole day. If you data was holding the date and time seperately you could find the data of a certain day without a shifted enddate, but would introduce all the other problems of eg checking overlaps et, which is much easier to cope with in datetimes format.

    The other way around is alsow possible, then you would put TTOD(startdate) to chop off the time portion and then could query for all data of a single day via TTOD(startdate) = entered date.

    In my oppinion it's much easier to cope with this by adding 24h to the enddate to justify you're searching within datetime data than using TTOD(), even more so having in mind you surely have indexed startdate, so it's much better to use where clauses using startdate and not an expression like TTOD(startdate).

    Bye, Olaf.

    • Edited by Olaf Doschke Sunday, February 5, 2012 5:38 PM
    • Marked as answer by mikelvfp Sunday, February 5, 2012 5:55 PM
    Sunday, February 5, 2012 5:26 PM
  • > I already tried making the txtboxes = DATE() I still get the same result?

    In regard to that you already told more to namoi, in that you're using another component, a calendar. If you'd set value = date(), you would only use the native textbox an no further control to choose dates, the textbox set to a date will only allow the entry of valid dates. A calender component overwriting the textbox value with a char value again, will destroy this effect.

    On the other hand a calendar is fine, then stay with it, as long as you can be sure CTOD(textbox.value) mostly will create a valid date and not an empty date, everything is fine. And in the worst case CTOD() just converts some nonvalid date to the empty date. That's ok.

    Bye, Olaf.

    Sunday, February 5, 2012 5:31 PM
  • I did it, I did it, finally oh my this drove me nuts. I thank you all for your help, If you guys lived in Toronto Id take you all for Dinner.

    Thank You Thank You

    Naomi actually solved it, and Olaf enlightened me.

    ldStartDate = CTOD(thisform.txtFromDate.value)
    
    ldEndDate = CTOD(thisform.txtToDate.value)+1
    
    IF EMPTY(ldEndDate)
    ldEndDate = m.ldStartDate+1 ENDIF
    Sunday, February 5, 2012 5:55 PM