| LEFT OUTER JOIN ??? -quick question [message #232437] |
Fri, 20 April 2007 09:17  |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
I am using oracle with php. I am trying to accomplish a left outer join.
Here are the two queries.
Query 1, contains all the data on the left the will show up with data on the right.
Query 1 =
((SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )))
Query 2 - is appended to query 1, while query still displays results when there are no matches on query 2.
Query 2 =
"SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ORDER BY JOBCODE ASC");
|
|
|
|
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232457 is a reply to message #232437] |
Fri, 20 April 2007 10:57  |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
Before I go to the union....
here is my situation...
$left_outer_join_monthly_data = oci_parse($c, "select * from (SELECT JOB_CODE AS JOBCODE_A FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' ) ORDER BY JOBCODE_A ASC) q1, (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ORDER BY JOBCODE ASC) q2 where q2.jobcode (+) = q1.jobcode_A");
oci_execute($left_outer_join_monthly_data);
$i = 1;
while($res=oci_fetch_array($left_outer_join_monthly_data))
{
$jobcode = $res['JOBCODE_A'];
echo '<tr><td>'.$jobcode.'</td>';
$forecast = 0;
if(isset($res['FORECAST'])){
$forecast = $res['FORECAST'];
}
$forecast = number_format($forecast,0);
echo '<td> $'.$forecast.'</td>';
}
The query is just fine, as far as doing the union and not throwing errors...
However, when I display the results... JOBCODES are on the left, and the Jan thru feb goes across the top. So the forecasts go on each row.
So the problem is that every JOBCODE FROM QUERY 1 is listed, but it doesn't add any of query 2, i am certain that i am just not outputing it right... what is wrong?
All the best,
Hyrum S
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232458 is a reply to message #232437] |
Fri, 20 April 2007 10:58 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
Here are the exact results:
Year: 20--
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
Job Code
02572 $0
03678 $0
03692 $0
04724 $0
04724A $0
04724B $0
04725 $0
05011 $0
05012 $0
05014 $0
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232461 is a reply to message #232459] |
Fri, 20 April 2007 11:11 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
hmmm....
If I were to UNION both queries wouldn't that only display results that had both results in query 1 and query 2? I always want results from query 1, but when they exist i want forecasts from query 2 to go with their respected job.
Regards,
Hyrum
|
|
|
|
|
|
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232488 is a reply to message #232484] |
Fri, 20 April 2007 13:02 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
here is what i put...
$left_outer_join_monthly_data = oci_parse($c, "select nv1(q1.jobcode, q2.jobcode), b.forecast from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 FULL OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 on q2.jobcode = q1.jobcode");
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232491 is a reply to message #232437] |
Fri, 20 April 2007 13:13 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
Version 10G.
Here is the error message.
Warning: oci_execute() [function.oci-execute]: ORA-00904: "PCT_COMP_CODE": invalid identifier in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 120
Warning: oci_fetch_array() [function.oci-fetch-array]: ¨Æ÷ in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 122
Here is my code:
$left_outer_join_monthly_data = oci_parse($c, "select q1.jobcode, q2.jobcode from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 FULL OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 on q1.jobcode = q2.jobcode");
*Note there was no error with the left outer join.. so there error is from the FULL OUTER JOIN.
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232503 is a reply to message #232493] |
Fri, 20 April 2007 14:41 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
Yeah, i just changed it to left and the error stopped -
Here are some of the results I get:
ear: 20--
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
Job Code
02572
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03678
03692
04724
04724A
04724B
04725
05011
05012
05014
05023
05023A4
05023A42
05023C25
05023C26
05023C27
05023C28
05023C29
05023C30
05023C31
05023C32
05023C33
As you can see Forecast_Input(query 2) has 24 forecasts, hence JOBCODE 03678 appears 24 times- and no forecasts appear horizantly.
Would splitting the query solve that?
By splitting it would I do something like do a right AND left outer join or something?
Regards,
Hyrum
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232505 is a reply to message #232504] |
Fri, 20 April 2007 14:57 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
How do I split a query?
Like this ???
$left_outer_join_monthly_data = oci_parse($c, "select q1.jobcode, q2.jobcode from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 LEFT OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 on q1.jobcode = q2.jobcode") q3 RIGHT OUTER JOIN q3 ON q3.jobcode = q2jobcode;
Is that getting closer?
-Hyrum
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232510 is a reply to message #232437] |
Fri, 20 April 2007 15:16 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
I am getting way close, I am just off on the syntax...
I put $left_outer_join_monthly_data = oci_parse($c, "select * from (SELECT JOB_CODE AS JOBCODE FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )) q1 LEFT OUTER JOIN (SELECT JOBCODE, FORECAST FROM FORECAST_INPUT WHERE MONTHPERIOD LIKE '%$forecast_point%' ) q2 (on q2.jobcode = q1.jobcode) UNION q1 (on q2.jobcode = q1.jobcode) right outer join q2 (on q2.jobcode = q1.jobcode)");
Do I only need one ON condition? Or are they in the wrong spot?
Thanks so much -
-Hyrum
Here is the error...
Warning: oci_execute() [function.oci-execute]: ORA-00905: missing keyword in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 121
Warning: oci_fetch_array() [function.oci-fetch-array]: ¨Æ÷ in D:\Forecasts.laytoncompanies.com\report_current_job.php on line 123
|
|
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232513 is a reply to message #232437] |
Fri, 20 April 2007 15:33 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
Dude, I am a flipping retard!! Thanks a bunch that worked.
This is correct!
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
Job Code
02572
03678
03692
04724
04724A
04724B
04725
05011
05012
05014
05023
05023A4
05023A42
05023C25
05023C26
05023C27
05023C28
05023C29
05023C30
05023C31
05023C32
05023C33
05023C34
05023C35
05023C36
05023C37
05023C38
05023C39
05023C43
Best Regards --
Thanks a bunch again.
-Hyrum
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232515 is a reply to message #232512] |
Fri, 20 April 2007 15:45 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
One other issue....
I can return jobcode great as you see by the left...
but when i return the FORECAST column $res['FORECAST'] I get a big fat "undefined variable" error... it seems like the query only returns the job codes.
|
|
|
|
| Re: LEFT OUTER JOIN ??? -quick question [message #232516 is a reply to message #232437] |
Fri, 20 April 2007 15:48 |
hdogg
Messages: 94
Registered: March 2007
|
Member |
|
|
|
actually, i think i got it, i found out i didn't added to every sub query... it does throw errors on the records that have no forecasts... would it be better to do an isset(forecast) on the results and populate an array?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
