Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL QUESTION
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQL QUESTION
 Login/Join
 
Guru
posted
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,


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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 Music


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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'


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
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.



WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Good One 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,


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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...
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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...
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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!


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Master
posted Hide Post
I think we do this all the time Smiler


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... ?


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL QUESTION

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.