Date: Thu, 17 Feb 2000 13:22:48 PST
Reply-To: wei cheng <cheng_wei@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: wei cheng <cheng_wei@HOTMAIL.COM>
Subject: Re: FW: SAS Sql Inconsistency? (Summary of Results)
Content-Type: text/plain; format=flowed
Yes, It reminds me that I also encounter a similiar problem before.
I used a simple query like:
Proc sql;
create table three as
select one.a, one.b, one.c, two.d, two.e, two.f
case when (two.e ? 'ABC') then d.g
when (two.e ? 'DEF') then d.h
else 'ERROR'
end as newvar
from firstset one left join secondset two
on one.a=two.a and one.m='A'
order by one.a,two.d;
quit;
run;
It works fine. But when I tried to add another condition to the on clause
like "on one.a=two.a and one.m='A' and two.f ne 'B'", it didn't exclude the
observations where f='B'.
I have to use additional step to do that like:
data three;
set three;
where f ne 'B';
run;
Dose anyone knows it's beacuse of an error inside this code or because of a
bug of PROC SQL?
Thanks,
Wei
>From: "Lawson, Gavin" <gavin.lawson@QR.COM.AU>
>Reply-To: "Lawson, Gavin" <gavin.lawson@QR.COM.AU>
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: FW: SAS Sql Inconsistency? (Summary of Results)
>Date: Thu, 17 Feb 2000 16:13:56 +1000
>
>There has not been a lot of response to this question, either from the list
>(SAL-L) or SI. However, I was asked to post the results of my findings,
>such as they are.
>
>SI Feedback
>-----------
>SI acknowledge that it is a defect and are working to fix it. No
>indication
>of when. We asked for a list of known problems, but the response was that
>alerts were provided with the version of the software. We looked through
>the alerts for Version 8, but did not find anything close.
>
>I searched the SI web site and found some technical notes about SQL
>problems, but nothing that was similar to this problem.
>
>Queries
>-------
>There were two queries in the original post. (A) did not work. (B) did
>work.
>
>(A) Select * From Work.SrvcInd A
> Where Exists (Select 'Valid Start Location'
> From Work.ConNote B
> , Work.StrtLocn C
> Where A.SrvcNo = B.SrvcNo
> And A.SrvcStDt = B.SrvcStDt
> And B.CN_Pfx = C.CN_Pfx
> And A.OriginCd = C.StartLn)
> ;
>(B) Select * From Work.SrvcInd A
> Where Exists (Select 'Valid Start Location'
> From (Select B.SrvcNo As SvNr
> , B.SrvcStDt As SvSt
> , C.StartLn As StartLn
> From Work.ConNote B
> , Work.StrtLocn C
> Where B.CN_Pfx = C.CN_Pfx)
> As J1
> Where A.SrvcNo = SvNr
> And A.SrvcStDt = SvSt
> And A.OriginCd = StartLn)
> ;
>
>Query By Platform/Engine
>-------------------------
> Platform Engine Query (A) Query (B)
> -------- ------ --------- ---------
> MVS 6.09 Failed Worked
> NT 6.12 Failed Worked
> NT 8.00 Failed Failed
>
>SI acknowledge these results. Interestly, we had an update version of
>Query
>(B). This update worked on all 3 platform/engines.
>
>Solution
>--------
>I finally took the cowards way out and broke the query down into two parts.
>
>
>Create Table Work.CNteStrt
> As (Select B.SrvcNo
> , B.SrvcStDt
> , C.StartLn
> From Work.ConNote B
> , Work.StrtLocn C
> Where B.CN_Pfx = C.CNteAlph);
>
>Update Work.SrvcInd A
> Set ValStrLn = 'Y'
> Where Exists (Select 'Valid Start Location'
> From Work.CNteStrt B
> Where A.SrvcNo = B.SrvcNo
> And A.SrvcStDt = B.SrvcStDt
> And A.SrvcStDt = B.SrvcStDt
> And A.OriginCd = StartLn);
>
>This works on all platforms/engines listed above, both as an update and
>query. The safest course appears to be to write simple SQL, even at the
>expense of multiple steps.
>
>Regards
>
>Gavin Lawson
>
>
>-----Original Message-----
>From: HERMANS1 [mailto:HERMANS1@WESTAT.COM]
>Sent: Friday, 11 February 2000 10:50
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: SAS Sql Inconsistency?
>
>
>I get the same result under V6.12 (MS W95) that you report. Haven't a clue
>why
>it fails. If you (and others) try the undocumented proc sql _tree; option,
>you
>will see a process tree on the log for the offending part of the program.
>Now
>delete the B.CN_Pfx = C.CN_Pfx condition in the correlated subquery and run
>the
>program again. You will see that the process trees differ only in the
>EXIST
>changes to a REPS (whatever that is) from a JOIN and the JOIN includes the
>B.CN_Pfx = C.CN_Pfx condition. Without that condition, the correlated
>subquery
>works as one would expect. So far as I can see the condtion should hold
>for
>some rows of the two tables involved, even where constrained by the other
>conditions involving the third table.
>
>The workaround that you try looks reasonable. Nonetheless I'd like to
>understand better why the first version of the query would fail. I have
>not
>encountered any similar problems in 6.12 SQL (though I tend to avoid
>correlated
>subqueries in SAS SQL, merely because they seem to run slower).
>
>I for one appreciate the care and effort you put into the posting of what
>appears to be a serious problem. I'd like to get the question resolved as
>quickly as possible. Hope that SAS-L will help you find an answer or join
>you
>in petitioning for a fix. I'll try the same program under Version 7, and
>urge
>others to try the program under Version 8 and on other platforms.
>
>Sig
>____________________Reply Separator____________________
>Subject: SAS Sql Inconsistency?
>Author: "Lawson; Gavin" <gavin.lawson@QR.COM.AU>
>Date: 2/10/00 1:47 PM
>
>Hi
>
>Can someone identify what is wrong with the following SAS SQL Select? In
>the the following code:
>
>- I build 3 members (SrvcInd, StrtLocn, Connote)
>- Test a select with exists one way, it does not give the expected results
>- I reformulate the query and it seems to work.
>
>If there is a "gotcha" in Sas Sql, does anyone know of any others that I
>should be aware of?
>
>Sorry the post is so long. I tried to shorten it by removing Cards data,
>but found that both queries then worked.
>
>Regards
>
>Gavin Lawson
>
>
>----Begin-----------------
>Data Work.SrvcInd;
> Input @1 SrvcNo $4.
> @6 SrvcStDt date9.
> @14 OriginCd $6.
> @21 StowInd $1.
> @23 ValStrLn $1.
> ;
> Cards;
>F01M 29JAN00 001061 N N
>F01M 01FEB00 001061 N N
>F01M 02FEB00 001061 N N
>F01M 03FEB00 001061 N N
>F01M 04FEB00 001061 N N
>F01M 05FEB00 001061 N N
>F01M 06FEB00 001061 N N
>F01M 07FEB00 001061 N N
>F01M 08FEB00 001061 N N
>F01M 09FEB00 001061 N N
>;
>Run;
>
>
>Data Work.StrtLocn;
> Input @1 System 1.
> @4 StartLn $6.
> @13 CN_Pfx $2.
> ;
> Cards;
>3 001702 CE
>3 001966 CI
>3 001702 CI
>3 001061 CI
>3 001934 CI
>3 001258 CI
>3 001966 CK
>3 001258 CS
>3 001934 CS
>3 001966 CY
>3 001258 CY
>3 001934 CY
>3 001702 CY
>3 001061 CY
>3 001258 D
>3 001702 EY
>3 001258 EZ
>3 001966 EZ
>3 001702 EZ
>3 001934 EZ
>3 001061 EZ
>3 001702 FA
>3 001966 FA
>3 001934 FA
>3 001258 FA
>3 001061 FA
>3 001702 FC
>3 001966 FC
>3 001258 FC
>Run;
>
>Data Work.Connote;
> Input @1 SrvcNo $4.
> @10 SrvcStDt date9.
> @19 Cn_Pfx $2.
> ;
> Cards;
>F01M 29JAN00 GC
>F01M 01FEB00 CI
>F01M 02FEB00 CI
>F01M 03FEB00 CY
>F01M 04FEB00 CI
>F01M 05FEB00 EZ
>F01M 06FEB00 EZ
>F01M 07FEB00 CI
>Run;
>
>
>Proc Sql;
> /* ----------------------------------------------------------------- */
> /* These selects are just to prove the data loaded okay */
> /* ----------------------------------------------------------------- */
> Title;
> Title1 'Work.SrvcInd';
> Select * From Work.SrvcInd;
> Title;
> Title1 'Work.StrtLocn';
> Select * From Work.StrtLocn;
> Title;
> Title1 'Work.Connote';
> Select * From Work.Connote;
>
> /* ----------------------------------------------------------------- */
> /* A join to prove the data matches okay. */
> /* ----------------------------------------------------------------- */
> Title;
> Title1 'Join Work.SrvcInd to Work.Connote to Work.StrtLocn' ;
> Select *
> From Work.SrvcInd A
> , Work.ConNote B
> , Work.StrtLocn C
> Where B.CN_Pfx = C.CN_Pfx
> And A.SrvcNo = B.SrvcNo
> And A.SrvcStDt = B.SrvcStDt
> And A.OriginCd = C.StartLn
> ;
>
> /*
>-----------------------------------------------------------------------
>*/
> /* This select is the cause of my post. It returns 0 rows which I do
>not
>*/
> /* expect.
>*/
> /*
>-----------------------------------------------------------------------
>*/
> Title;
> Title1 'Select From SrvcInd Where Exists - Does Not Work';
> Select * From Work.SrvcInd A
> Where Exists (Select 'Valid Start Location'
> From Work.ConNote B
> , Work.StrtLocn C
> Where A.SrvcNo = B.SrvcNo
> And A.SrvcStDt = B.SrvcStDt
> And B.CN_Pfx = C.CN_Pfx
> And A.OriginCd = C.StartLn)
> ;
> /*
>-----------------------------------------------------------------------
>*/
> /* This select does seem to work. It is reforumlation of the above
>query
>*/
> /*
>-----------------------------------------------------------------------
>*/
> Title1 'Second Exists Test - Seems to Work';
> Select * From Work.SrvcInd A
> Where Exists (Select 'Valid Start Location'
> From (Select B.SrvcNo As SvNr
> , B.SrvcStDt As SvSt
> , C.StartLn As StartLn
> From Work.ConNote B
> , Work.StrtLocn C
> Where B.CN_Pfx = C.CN_Pfx)
> As J1
> Where A.SrvcNo = SvNr
> And A.SrvcStDt = SvSt
> And A.OriginCd = StartLn)
> ;
>Quit;
>-----End SAS Code
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager of Queensland Rail.
>
>This message has been swept by MIMESweeper for the presence of computer
>viruses. No warranty is given that this message upon its receipt is
>virus free and no liability is accepted by the sender in this respect.
>
>This email is a message only; does not constitute advice and should not
>be relied upon as such.
>**********************************************************************
>************************************************************
>This message has been swept by MIMESweeper for known viruses
>************************************************************
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager of Queensland Rail.
>
>This message has been swept by MIMESweeper for the presence of computer
>viruses. No warranty is given that this message upon its receipt is
>virus free and no liability is accepted by the sender in this respect.
>
>This email is a message only; does not constitute advice and should not
>be relied upon as such.
>**********************************************************************
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
| 
