Need a little help with a custom query

[Rich S]

Sooooo close, yet so far...

 

Here's what I have:

 

 

"SELECT ATTENDANCE.ATT_COMMENT,ATTENDANCE.ATT_DATE, ATTENDANCE.ATTENDANCE_CODEID, ATTENDANCE.ID, ATTENDANCE.PERIODID, ATTENDANCE.SCHOOLID, ATTENDANCE.STUDENTID, ATTENDANCE.TOTAL_MINUTES, ATTENDANCE.YEARID

FROM ATTENDANCE

JOIN STUDENTS

ON STUDENTS.ID=ATTENDANCE.STUDENTID

WHERE ATTENDANCE.YEARID=22

AND ATTENDANCE.PERIODID>0

AND (ATTENDANCE.ATT_DATE ≥ MAIN::ReportFromDate__gxd AND ATTENDANCE.ATT_DATE ≤ MAIN::ReportToDate__gxd)"

 

The query works fine up until the blue text--I'm trying to filter records outside the date range from being downloaded. I tried using the BETWEEN SQL command but I didn't have much luck with that, either, so I'm just not getting it. *sigh* If you could help with the last part of the request, I'd really appreciate it.

 

TIA for your help!

Rich

[dansmith65]

Are you not getting the correct records, or are you getting an error?

 

Why do you have (parentheses) around the last line in blue?  My guess is that it will work if you remove those; they are all 'AND' criteria, so parentheses are not needed.

[Rich S]

I'm getting an error message. In addition, I removed the parentheses but no dice.

 

I just stripped away most of the coding so I could determine what the real culprit is here so now the query looks like this (using Actual Oracle, hence all the additional punctuation):

 

 

SELECT "ATTENDANCE"."ATT_COMMENT", "ATTENDANCE"."ATT_DATE", "ATTENDANCE"."ATTENDANCE_CODEID", "ATTENDANCE"."ID", "ATTENDANCE"."PERIODID", "ATTENDANCE"."SCHOOLID", "ATTENDANCE"."STUDENTID", "ATTENDANCE"."YEARID"

FROM "ATTENDANCE"

WHERE "ATTENDANCE"."ATT_DATE"

BETWEEN "2013-01-01" AND "2013-02-01"

 

Still no dice...and I changed the date format around, too, hoping that was the troublemaker. Nope. It looks like it's the BETWEEN function that's giving me grief, but that's a standard SQL command so I'm scratching my brain raw.

 

*Sigh* I'm not a drinking man, but this brain hernia is giving me pause to start.

[dansmith65]

For FileMaker SQL, it expects the date in one of these formats:

 

DATE '2012-06-05'

{ D '2012-06-05' }

{06/05/2012}

 

You can use this custom function to convert a FileMaker date into the first format: http://www.fmfunctions.com/fid/343

[fmsavey]

If you are using this select statement in an import statement then try this.  Put the following in the "Calcuated SQL Text" part of the function.  Good luck.

 

 

Substitute (

 

"SELECT ATTENDANCE.ATT_COMMENT,ATTENDANCE.ATT_DATE, ATTENDANCE.ATTENDANCE_CODEID, ATTENDANCE.ID, ATTENDANCE.PERIODID, ATTENDANCE.SCHOOLID, ATTENDANCE.STUDENTID, ATTENDANCE.TOTAL_MINUTES, ATTENDANCE.YEARID

 

FROM ATTENDANCE

 

JOIN STUDENTS

 

ON STUDENTS.ID=ATTENDANCE.STUDENTID

 

WHERE ATTENDANCE.YEARID=22

 

AND ATTENDANCE.PERIODID>0

 

AND (ATTENDANCE.ATT_DATE Between '<from date>' and '<to date>' ";

 

[ "<from date>";  MAIN::ReportFromDate__gxd ]; [ "<to date>"; MAIN::ReportToDate__gxd ]

 

)

[Rich S]

Thanks for your help!

 

After hours of trying yours and others' suggestions, we found the problem: the source database expects the date format in DD-MON-YY format, e.g., 03-FEB-13. *sigh* So, I created a CF to convert the dates to that format, tweaked the code, and got it to work using this:

 

 

"SELECT ATTENDANCE.ATT_DATE, ATTENDANCE.ATT_COMMENT, ATTENDANCE.ATTENDANCE_CODEID, ATTENDANCE.ID, ATTENDANCE.PERIODID, ATTENDANCE.SCHOOLID, ATTENDANCE.STUDENTID, ATTENDANCE.YEARID,STUDENTS.GRADE_LEVEL

FROM ATTENDANCE

JOIN STUDENTS

ON STUDENTS.ID=STUDENTID

WHERE ATTENDANCE.ATT_DATE >=" & "'" & MAIN::ReportFromDate__gct & "'" & " AND " & "ATTENDANCE.ATT_DATE <=" & "'" & MAIN::ReportToDate__gct & "'" &

"AND ATTENDANCE.YEARID=22

AND ATTENDANCE.PERIODID>0" &

 

Case (

         MAIN::_g_SchoolID__gcn = "All"; ""; 

         Left ( MAIN::_g_SchoolID__gcn; 4 ) = "2279"; "AND ATTENDANCE.SCHOOLID=" & MAIN::_g_SchoolID__gcn

         )

 

 

Why there's no ending quotation mark needed, I don't know.