1

I work in a school district that uses Powerschool, and Powerschool has a custom report system that allows users to enter in SQL queries directly. When I use the custom report :

SELECT T.DCID, T.LastFirst, T.teachernumber, T.Users_DCID
FROM TEACHERS T 
WHERE T.STATUS=1 and T.SCHOOLID=~(curschoolid)
ORDER BY T.LAST_NAME ASC, T.FIRST_NAME ASC

I get a populated table like this:

DCID    lastfirst   teachernumber   users_dcid

5433    -------------   216335  5433
7855    -------------   216430  7855
7576    -------------   216376  7576
26021   -------------   7365739166  9748

But when I modify my query to add in selecting for certain data:

SELECT T.DCID, T.LastFirst, T.teachernumber, T.Users_DCID
FROM TEACHERS T 
WHERE T.teachernumber IN (216335, 216430, 7365739166) and T.STATUS=1 and T.SCHOOLID=~(curschoolid)
ORDER BY T.LAST_NAME ASC, T.FIRST_NAME ASC

The output is:

No data available in table

I've looked up the documentation and t.teachernumber is a string(20). So I tried adding CONVERT(string(20), 216335) to the WHERE IN () but that still came up with nothing.

What is wrong withy my query?

Devin
  • 11
  • 1
  • 4
    use `'216335'` single quotes around strings – Hogan Feb 24 '17 at 19:30
  • One of the reasons I am modifying this is because I am modifying a plugin as well. The plugin takes a list of teacher numbers and creates a query to select them, and then modifies them in bulk. I need to get the query logic worked out in a report before I can modify the plugin. – Devin Feb 24 '17 at 19:34
  • sounds like an ambitious quest. Good luck! – Hogan Feb 24 '17 at 19:36
  • Hogan, that seems to have corrected my issue. I tried this with double quotes and it did not work, is sql always done with single quotes? Thanks for the help!!! – Devin Feb 24 '17 at 19:42
  • Yes SQL is always with single quotes, C and C like are double quotes and JavaScript is both. Because JavaScript is the crazy step-child. – Hogan Feb 24 '17 at 20:05

2 Answers2

7

You want this:

WHERE T.teachernumber IN ('216335', '216430', '7365739166') -- etc

String constants use single quotes

Hogan
  • 69,564
  • 10
  • 76
  • 117
0
SELECT T.DCID, T.LastFirst, T.teachernumber, T.Users_DCID FROM TEACHERS T 
       WHERE T.teachernumber IN ('216335', '216430', '7365739166') 
             AND T.STATUS=1 AND T.SCHOOLID=~(curschoolid)
ORDER BY T.LAST_NAME ASC, T.FIRST_NAME ASC
ℛɑƒæĿᴿᴹᴿ
  • 4,983
  • 4
  • 38
  • 58