Sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sql server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-09 : 08:22:24

Kuy writes "Dear SQLTeam,

I'm new to Sql 2000 please help!
How do I rewrite the following queries from VFP to SQL 2000 without using the temporary tables.
---------------------------------------------
lcWhereClause1 ="between(s.examdate, date()-100, date()) and s.examscore > 0"

select s.ssn, s.exam, s.examdate, ;
s.examsite, s.examstate, s.schedid, s.certif_ptr,;
r.pass_fail, r.percent, c.schoolcode;
from (Scheduletbl) s, (Resultstbl) r, (Certifstbl) c;
where &lcWhereClause1 and s.examstate="NY" and s.biztype="1";
and s.schedid=r.schdrecno and s.certif_ptr = c.certid;
into table abc

sele dist exam, fidoname as examname;
from (examstbl);
where between(date(), exam_start, exam_end);
into table tblexam

sele abc.*, padr(allt(s.trucity) + ", " + s.trustate,30," ") as sitecity,;
c.schoolname, e.examname;
from abc, (sitetbl) s, (schooltbl) c, tblexam e;
where abc.examsite=s.examsite and abc.schoolcode=c.schoolcode and;
abc.exam= e.exam;
into table tmrep order by abc.schoolcode

sele schoolcode as DETAIL8, upper(schoolname) as DETAIL9, exam as DETAIL1,;
upper(examname) as DETAIL2, sum(iif(pass_fail$"PF", 1, 0)) as DETAIL3,;
sum(iif(pass_fail="P",1,0)) as DETAIL4,;
sum(iif(pass_fail="F",1,0)) as DETAIL5,;
sum(iif(pass_fail="N",1,0)) as DETAIL6;
from tmrep;
group by DETAIL8, detail2 ;
into table tmp1 order by DETAIL9

--------------------------------------------------
Thank you,

Kuy

"

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-12-09 : 09:44:13

[code]

CREATE TABLE #abc (s.ssn <datatype>, s.exam< datatype>, s.examdate <datatype>,
s.examsite <datatype>, s.examstate <datatype>, s.schedid <datatype>, s.certif_ptr <datatype>,
r.pass_fail <datatype>, r.percent <datatype>, c.schoolcode <datatype>)

INSERT INTO #abc (s.ssn, s.exam, s.examdate,
s.examsite, s.examstate, s.schedid, s.certif_ptr,
r.pass_fail, r.percent, c.schoolcode)
SELECT s.ssn, s.exam, s.examdate,
s.examsite, s.examstate, s.schedid, s.certif_ptr,
r.pass_fail, r.percent, c.schoolcode
FROM (Scheduletbl) s
INNER JOIN (Resultstbl) r ON
s.schedid=r.schdrecno
INNER JOIN (Certifstbl) c ON
s.certif_ptr = c.certid
WHERE (s.examdate BETWEEN GETDATE()-100 AND GETDATE()) AND (s.examscore > 0)

OR This style select is valid to...

select s.ssn, s.exam, s.examdate,
s.examsite, s.examstate, s.schedid, s.certif_ptr,
r.pass_fail, r.percent, c.schoolcode
FROM (Scheduletbl) s, (Resultstbl) r, (Certifstbl) c
WHERE s.schedid=r.schdrecno
AND s.certif_ptr = c.certid
AND (s.examdate BETWEEN GETDATE()-100 AND GETDATE())
AND (s.examscore > 0) as abc

#abc represents a temporary table
as abc represents a derived table just like s,r or c

use either as needed or directly to abc if that table is an actual .dbf file. ZAP it first I guess. It all depends.... INTO table INTO Cursor etc.

[/code]

They make a grunting sound when agitated, hungry, or calling their young. Otherwise, they make no sound at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources