need help with query

[xrbbaker]

My team mate used Business Objects to create a report that goes against
Oracle and then sent me the SQL it used to do it. Ultimately I'd like to
embed the SQL in Excel, but I'm trying to run it in Access first, so as to
get better diagnostics. When I try to run the statement I get an error
saying that there is an extra ')', and then the cursor returns to just to the
right of the first (+). Now I had never seen a (+) before, but I found out
that represents and Outer Join in Oracle. I'm guessing that my problem is
that (+) is not valid syntax in Access land. Thus I need to figure out how
to translate to someting Access likes. Problem is all the examples I can
google show LEFT JOIN in the FROM part of the SELECT, not in the WHERE, as it
occurs here, so I'm not sure how to do it.

Can anybody tell me how I can translate the (+) into something Access will
llike?

Thanks very much,

Russ

SELECT
Decode(ADMUSER.PROJPCAT.PROJ_CATG_ID,
'103','CI','104','HB','105','MD','111','DTD','112','NP'),
ADMUSER.RSRC.RSRC_NAME,
ADMUSER.PROJECT.PROJ_SHORT_NAME,
ADMUSER.PROJWBS.WBS_NAME,
ADMUSER.RSRCHOUR.WORK_DATE,
ADMUSER.RSRC.EMPLOYEE_CODE,
Sum ( ADMUSER.RSRCHOUR.HR_CNT )
FROM
ADMUSER.PROJPCAT,
ADMUSER.RSRC,
ADMUSER.PROJECT,
ADMUSER.RSRCHOUR,
ADMUSER.PROJWBS,
ADMUSER.TASKRSRC,
ADMUSER.TASK
WHERE
( ADMUSER.RSRC.RSRC_ID=ADMUSER.RSRCHOUR.RSRC_ID )
AND ( ADMUSER.TASK.TASK_ID(+)=ADMUSER.TASKRSRC.TASK_ID )
AND ( ADMUSER.PROJWBS.PROJ_ID(+)=ADMUSER.TASK.PROJ_ID )
AND ( ADMUSER.TASKRSRC.TASKRSRC_ID(+)=ADMUSER.RSRCHOUR.TASKRSRC_ID )
AND ( ADMUSER.PROJPCAT.PROJ_ID(+)=ADMUSER.PROJECT.PROJ_ID )
AND ( ADMUSER.PROJECT.PROJ_ID(+)=ADMUSER.PROJWBS.PROJ_ID )
AND ( ADMUSER.PROJWBS.PROJ_NODE_FLAG ='Y' OR ADMUSER.RSRCHOUR.PROJ_ID IS
NULL )
AND ( ADMUSER.RSRCHOUR.PROJ_ID IS NOT NULL )
AND (ADMUSER.RSRCHOUR.WORK_DATE>={ts '2007-01-01 00:00:00'})
AND ADMUSER.PROJPCAT.PROJ_CATG_TYPE_ID = 26
AND Decode(ADMUSER.PROJPCAT.PROJ_CATG_ID,
'103','CI','104','HB','105','MD','111','DTD','112','NP') LIKE 'CI'
)
GROUP BY
Decode(ADMUSER.PROJPCAT.PROJ_CATG_ID,
'103','CI','104','HB','105','MD','111','DTD','112','NP'),
ADMUSER.RSRC.RSRC_NAME,
ADMUSER.PROJECT.PROJ_SHORT_NAME,
ADMUSER.PROJWBS.WBS_NAME,
ADMUSER.RSRCHOUR.WORK_DATE,
ADMUSER.RSRC.EMPLOYEE_CODE

 

[Jerry Whittle]

Yes. (+) is Oracles way of saying OUTER JOIN. Actually more of a pre-ISO 99
way of saying it. You can read more about it at:

http://www.dba-oracle.com/art_sql_iso_99.htm

http://www.psoug.org/reference/joins.html

Oracle 9i and newer support ISO 99 so they should be able to rewrite the
query to work. 10g also supports ANSI joins.

HOWEVER Access doesn't do Decode. Therefore after you get the joins fixed,
Decode will cause the next error. You could make a long IIf statement or
create a Case statement in a code module.

Better yet would be an actual table with the value of the Decode in it. Then
join the tables on the PROJ_CATG_ID field. Something like this in a
tblPROJ_CATG table.

PROJ_CATG_ID PROJ_CATG_CODE
'103' 'CI'
'104' 'HB'
'105' 'MD'
'111' 'DTD'
'112' 'NP'

 

[xrbbaker]

Arrrgggghhh!! Thanks Jerry. Why can't all the SQLs just get along?
This is turning into a nightmare. I think I'm going to fall back to plan B.
First I'll have to invent plan B...

thanks much

 

[xrbbaker]

Jerry,

As it turned out, the query was written 2 ways. I found another place where
it was already written in Access syntax. I've manipulated it around and now
have the following:

SELECT ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME,
Sum(ADMUSER_RSRCHOUR.HR_CNT) AS SumOfHR_CNT
FROM ((((ADMUSER_PROJWBS INNER JOIN (ADMUSER_RCATVAL INNER JOIN
(((ADMUSER_PROJECT INNER JOIN ADMUSER_TASK ON ADMUSER_PROJECT.PROJ_ID =
ADMUSER_TASK.PROJ_ID) INNER JOIN ((ADMUSER_RSRCHOUR INNER JOIN ADMUSER_RSRC
ON ADMUSER_RSRCHOUR.RSRC_ID = ADMUSER_RSRC.RSRC_ID) INNER JOIN
ADMUSER_TASKRSRC ON ADMUSER_RSRCHOUR.TASKRSRC_ID =
ADMUSER_TASKRSRC.TASKRSRC_ID) ON ADMUSER_TASK.TASK_ID =
ADMUSER_TASKRSRC.TASK_ID) INNER JOIN ADMUSER_RSRCRCAT ON ADMUSER_RSRC.RSRC_ID
= ADMUSER_RSRCRCAT.RSRC_ID) ON ADMUSER_RCATVAL.RSRC_CATG_ID =
ADMUSER_RSRCRCAT.RSRC_CATG_ID) ON ADMUSER_PROJWBS.PROJ_ID =
ADMUSER_PROJECT.PROJ_ID) INNER JOIN ADMUSER_PROJPCAT ON
ADMUSER_PROJECT.PROJ_ID = ADMUSER_PROJPCAT.PROJ_ID) INNER JOIN
RMISEXTN_RMIS_PROJ_INFO ON ADMUSER_PROJECT.PROJ_ID =
RMISEXTN_RMIS_PROJ_INFO.PROJ_ID) INNER JOIN RMISEXTN_RMIS_PROJ_PORTFOLIO ON
RMISEXTN_RMIS_PROJ_INFO.RMIS_PROJ_PORTFOLIO_ID =
RMISEXTN_RMIS_PROJ_PORTFOLIO.RMIS_PROJ_PORTFOLIO_ID) INNER JOIN
ADMUSER_PCATVAL ON ADMUSER_PROJPCAT.PROJ_CATG_ID =
ADMUSER_PCATVAL.PROJ_CATG_ID
WHERE (((ADMUSER_RSRCHOUR.WORK_DATE)>=#4/1/2007# And
(ADMUSER_RSRCHOUR.WORK_DATE)<#5/1/2007#) AND
((ADMUSER_PROJPCAT.PROJ_CATG_TYPE_ID)=26) AND
((ADMUSER_RSRCRCAT.RSRC_CATG_TYPE_ID)=10) AND
((ADMUSER_PROJWBS.PROJ_NODE_FLAG)="Y"))
GROUP BY ADMUSER_RSRC.RSRC_NAME, ADMUSER_PROJECT.PROJ_SHORT_NAME
ORDER BY ADMUSER_RSRC.RSRC_NAME;

Sorry about the format. When working in Access is strips out any
intelligent indenting one might have used. The above query brings back data
results like this:

RSRC_NAME PROJ_SHORT_NAME SumOfHR_CNT
ABRAHAM, ANIL DD0607124E 6
ABRAHAM, ANIL DD0607144A 159
ABRAHAM, ANIL DD0607204A 3
ABRAHAM, TRIPTI C00016064 120
ABRAHAM, TRIPTI C00016395 34
ABU-SHAIKHA, JAMIL C00013666a(new) 20

Thus the query is still god-awful in my mind, but it is the beginnings of
bringing back the right data. As you can see above I have the answer set
limited to bringing back just April's data. I have validated these results.
Now I'm at a point where I'm flat stuck.

Here is the business problem. I have a spreadsheet that lists projects and
forecasts how much of a person's time is expected to be billed against that
project, by month, for the year. What I would like to do is leave the
forecasted numbers in place, and query the DB to bring back the actuals to
compare forecasted vs actual. I'm thinking there are 2 options.

option 1: bring back the data to Excel in a format that I can easily work
with in Excel. I think that format would be like this:

RSRC_NAME PROJ_SHORT_NAME May June July
ABRAHAM, ANIL DD0607124E 6 96 79
ABRAHAM, ANIL DD0607144A 159 158.5 44
ABRAHAM, ANIL DD0607204A 3 129 130
ABRAHAM, TRIPTI C00016064 120 31 2

Option 2: Basically do the same thing, but instead of bringing it back into
Excel, bring it back into an Access table, and then query the Access table.

Other options?

My biggest challenge is figuring out how to get the data to come back
"across" in columns. I don't expect anybody to try to make sense of the
crazy sql above, but maybe if we use a simpler example, the same principle
would apply? Suppose I have a sql statement like this:

select agent, houses_sold, month from table order by month;

would return:

Smith 4 Jan
Smith 6 Feb
Smith 8 Mar

How could I create a sql statement to have it return like this?

Smith 4 6 8

Maybe if I knew that I could convert the monster above to return data back
Jan-Dec. Actually, I'm kind of doubting it. I can't imagine parsing the
monster sql into 12 chunks or sub-selects or whatever. I'd appreciate any
suggestions.

thanks very much