What is the best way to accomplish folliwing SQL in FOcus?
I have information coming from 3 tables and I have to use left outer join twice to get some enrollment. The problem is I want this report to run for the current FYP value and this value is in one of my table. Current WHERE FYP = &YYMD But I also want to run this report live giving users options to run this report selcting FYP form the parameter screen.


DECLARE @LASTPERIOD VARCHAR (6)
SET @LASTPERIOD= (SELECT FYP FROM dim_Time WHERE cast(GETDATE() as Date)=DATE)

SELECT
A.SchoolID
,B.ParentFirstName
,B.ParentLastName
,B.Address1
,B.Address2
,B.City
,B.State
,B.ZipCode
,B.PHONE1
,B.PHONE2
,B.Email1
,B.Email2
,A.ChildFirstName
,A.ChildLastName
,C_ENROLL.DATE AS ENROLLED_DATE
,a.EnrollmentDate
,C_WITHDRAW.DATE AS WITHDRAW_DATE
,a.WithdrawDate
from stage_Child_POS A
Inner JOIN stage_Parent_POS B on A.SchoolID = B.SchoolID AND a.ParentID = B.ParentID
LEFT JOIN dim_Time C_ENROLL on A.EnrollmentDate = C_ENROLL.DATE
LEFT JOIN dim_Time C_WITHDRAW on A.WithdrawDate = C_WITHDRAW.DATE
WHERE B.dss_currentflag='Y' AND A.dss_currentflag='Y'
AND a.schoolid = '0618'
AND (C_ENROLL.FYP= @LASTPERIOD
OR C_WITHDRAW.FYP=@LASTPERIOD)

This message has been edited. Last edited by: Kerry,

quote:

What is the best way to accomplish following SQL in FOcus?

... My suggestion, as always, is to do it in WebFOCUS, not SQL...

Why use SQL when: "We can do that in WebFOCUS"? ~ imho

quote:

, as always, is to do it in WebFOCUS, not SQL...

Thank you for writing back. I try to do it in FOcus however, I will have to join same table twice with two different tables. I think it will be little slow.
My other problem is how do I write this in webfocus
SELECT FYP FROM TABLE
WHERE FYP = (SELECT FYP from TABLE WHERE DATE = GETDATE()) OR EQ '&YYMD'

I take it back I was able to write code. Its awesome I didnt realize we can do a lot with Focus: Here is the Code that i used to get values.

  DEFINE FILE DIM_TIME
COMDATE/YYMD=HDATE(DIM_TIME.DIM_TIME.DAYBEGINTIME, 'YYMD');
END
 TABLE FILE DIM_TIME
 PRINT
      'DIM_TIME.DIM_TIME.FYP/A7'
      'DIM_TIME.DIM_TIME.DATE'
 WHERE COMDATE EQ '&YYMD';
 ON TABLE NOTOTAL
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS FYP FORMAT ALPHA
 END
-RUN
-READ FYP &FYP_0.A7.

I'm happy when I see that someone else "sees" that "We Can Do That In WebFOCUS!"
Another Thought: Consider using HOLD files to prevent having to read the DB more then once. TABLE the desired info in to HOLD files, then Join the HOLD files. That is normally much quicker / more efficient.

This message has been edited. Last edited by: Doug,

Please explain what "FYP" is, and how you're using it. There' some prefix operators that may make this a more efficient read of the DB and may make your life easier as well...

I've been doing it for years in WebFOCUS, but I don't believe the mantra ""We Can Do That In WebFOCUS!" is always the best solution. There are many things you can do in a DBMS SQL statement that you CANNOT do in WebFOCUS.

Please don't get me wrong, I don't believe that it is always applicable either. But, when it is, it is. imho

I have found that it's has not been applicable more in the past year then since the early 80's...

quote:

I don't believe that it is always applicable either. But, when it is, it is. imho

I agree that it is more efficient to write Dynamic SQL for efficient processing. Sometime when I need to use really complex joins, I ususally write SQL procedure and use IBI variables to get desired results.
For exmaple I am able to use IBI variables for SQL procedures parameters. in the end I have to say its awesome that we can do a lot using webfocus & SQL combination!

I think we do this all the time


In text and pseudo-code, but you'll get the idea.

First query your base data from stage_Child_POS : put in HOLD01
Then query from dim_Time where enroll EQ &LASTPERIOD : put in HOLD2
Then query from dim_Time where withdraw EQ &LASTPERIOD : put in HOLD3

match file hold01
...
file hold02
...
after match hold OLD
file hold03
...
after match hold OLD
end


Should fix it... ?