Solved

Oracle group by

Posted on 2014-03-30
17
301 Views
Last Modified: 2014-04-04
I'm getting strange results when I change my group by expression.  I'm building a string that's being sent to open query like this:
if @Group_by = 'tech'
	set @groupquery = ' group by lab_name, tech.full_name order by tech.full_name'
else if @Group_by = 'month'
	set @groupquery = ' group by r.PROD_GROUP_DESC, r.LAB_ID, to_char(trunc(r.APPROVED_DATE,''''Mon''''),''''Mon-YY'''')'
else if @Group_by = 'project'
	set @groupquery = ' group by to_char(trunc(r.APPROVED_DATE,''''Mon''''),''''Mon-YY''''), p.proj_nbr'
else if @Group_by = 'quarter'
	set @groupquery = ' group by r.PROD_GROUP_DESC, r.LAB_ID, to_char(trunc(r.APPROVED_DATE,''''Q''''),''''Q-YY'''')
	order by r.PROD_GROUP_DESC, r.LAB_ID '
else
    set @groupquery = ' group by r.rpt_nbr,s.spec_id order by r.rpt_nbr '

Open in new window


If I set @group_by = month I get these results
TECH_CENTER      APPROVED_MONTH      SPEC_NUMBER      SAMPLES
CASR      14-Jan      NULL      1
CASR      13-Jul      NULL      1
WROC      13-Nov      W10406960      2
WROC      13-May      NULL      2
CASR      13-Apr      NULL      1
CASR      13-Aug      NULL      1
CASR      13-Dec      NULL      3
CASR      14-Jan      NULL      2
CASR      13-Jun      NULL      2
CASR      14-Mar      NULL      7
CASR      13-Nov      NULL      4
CASR      13-Oct      NULL      1
CASR      13-Sep      NULL      3

If I set @groupby = project I get these results
TECH_CENTER      APPROVED_MONTH      SPEC_NUMBER      SAMPLES
CASR      13-Apr      NULL      1
CASR      13-Jul      NULL      1
CASR      13-Jun      NULL      2
WROC      13-May      NULL      2
WROC      13-Nov      W10406960      16
CASR      13-Sep      NULL      3

So it is grouping together a bunch of rows into the 13-Nov for some reason
0
Comment
Question by:newbie-netter
  • 9
  • 5
  • 3
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
not sure what you mean by "for some reason", a you are doing a GROUP BY the TO_CHAR(your_date_field, yy-mon), so it's normal?
what else would you expect?
anyhow, it's difficult to give more feedback, as we have no idea what the corresponding input data is, what the rest of the sql is, and what you would actually expect as outcome.

I do presume that reading this article will help:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 

Author Comment

by:newbie-netter
Comment Utility
You can see that when I @groupby = month that I get some dates in 2014, but when @groupby = project (and it still groups by month) I don't get any dates in 2014
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
then there must be some other code, but as I wrote above, without seening "more", we cannot tell where the issue is, except that it must be in your code.
0
 

Author Comment

by:newbie-netter
Comment Utility
Here is the query that works correctly with @groupby = month
SELECT r.*, tc.Grouping as Region, tc.ExtraTechs FROM OPENQUERY(ORASERVER,'select MAX(l.tech_center) as Tech_Center, MAX(l.lab_name) as Lab_Name, MAX(p.PROD_GROUP_DESC) as PROD_GROUP_DESC,
Avg(round(r.APPROVED_DATE - r.TEST_COMPLT_DATE,1)) as COMPL_DAYS,
Avg(round(r.APPROVED_DATE - r.SUBMITTED_DATE,1)) as REQ_DAYS,
MAX(p.PROJ_NBR) as PROJ_NBR, MAX(p.TITLE) as PROJ_TITLE,
MAX(to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')) as FROM_MONTH,
MAX(to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')) as Approved_month,
MAX(sd.spec_nbr) as Spec_Number, MAX(sd.title) as Spec_Name,
SUM(NVL(samples,1)) as samples,

MAX(r.APPROVED_DATE) as Approved_Date,
COUNT(DISTINCT tech.emp_id_key) as NUM_TECHS,

COUNT(*) as Procedures, 
SUM(NVL(samples,1)) as Proc_Count, 
SUM(NVL(tech_hrs,8)*NVL(samples,1)) as tech_hrs,
SUM(NVL(process_hrs,0)) as process_hrs,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''yes'' then 1 else 0 end) as MA_MEETS,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''sc'' then 1 else 0 end) as MA_SC,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''no'' then 1 else 0 end) as MA_DM,
sum(case when r.REQUEST_TYPE = ''mdl'' then 1 else 0 end) as MA_SUM,
sum(case when r.REQUEST_TYPE = ''cpnt'' and S.MEETS_CRITERIA_ID = ''yes'' then 1 else 0 end) as CA_MEETS,
sum(case when r.REQUEST_TYPE = ''cpnt'' then 1 else 0 end) as CA_SUM,
Sum(decode(S.MEETS_CRITERIA_ID,''yes'',1,0)) as MEETS_SPEC,
Sum(decode(S.MEETS_CRITERIA_ID,''sc'',1,0)) as SEE_CONCL,
Sum(decode(S.MEETS_CRITERIA_ID,''no'',1,0)) as DOESNT_MEET,
Sum(case when S.Iteration > 1 then 1 else 0 end) as Retries,
Sum(case when sd.spec_nbr is null then 1 else 0 end) as NON_STANDARD,
MAX(tech.full_name) as tech, Max(r.rpt_nbr) as rpt_nbr,
sum(case when r.REQUEST_TYPE = ''devl'' or r.REQUEST_TYPE = ''eval'' then 1 else 0 end) as DEVELOP_TEST,
sum(case when r.REQUEST_TYPE = ''mdl'' or r.REQUEST_TYPE = ''cpnt'' then 1 else 0 end) as APPROVAL_TEST,
sum(case when r.REQUEST_TYPE = ''devl'' or r.REQUEST_TYPE = ''eval'' then NVL(tech_hrs,8)*NVL(samples,1) else 0 end) as DEVELOP_HRS,
sum(case when r.REQUEST_TYPE = ''mdl'' or r.REQUEST_TYPE = ''cpnt'' then NVL(tech_hrs,8)*NVL(samples,1) else 0 end) as APPROVAL_HRS

from 

SJTCAPP.LAB_RPT r 
left join SJTCAPP.EPM_PROJECT_PUBLIC_V p on p.PROJ_NBR = r.PROJ_NBR
join SJTCAPP.LAB l on l.LAB_ID = r.LAB_ID
left join SJTCAPP.LAB_RPT_SPEC s on s.rpt_nbr = r.rpt_nbr
left join SJTCAPP.LAB_SPEC_DEFN sd on sd.spec_id = s.spec_id
left join SJTCAPP.LAB_SPEC_ASSOC sa on sa.spec_id = sd.spec_id and sa.lab_id = r.lab_id
left join SJTCAPP.LAB_RPT_SPEC_EMP se on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration
join sjtcapp.employee tech on tech.emp_id_key = se.emp_id_key

where 
      r.approved_date >= to_date(''2012-01-01'',''YYYY-MM-DD HH24:MI:SS'') and 
      r.APPROVED_DATE < to_date(''2014-03-01'',''YYYY-MM-DD HH24:MI:SS'') and

     (p.PROD_GROUP_DESC LIKE ''Ref'' or ''-1'' in (''Ref'')) and
     (l.TECH_CENTER IN (''-1'') or ''-1'' in (''-1'')) and
    (p.PROJ_NBR IN (''652647'') or ''-1'' in (''652647'')) and 
    (p.PROJ_TYPE IN (''-1'') or ''-1'' in (''-1'')) and 

    (r.LAB_ID IN (''-1'') or ''-1'' in (''-1'')) and 
    (sd.Active is NULL or sd.Active = ''y'') and
    r.STATUS_CODE <> ''can'' and r.STATUS_CODE <> ''dft'' group by r.PROD_GROUP_DESC, r.LAB_ID, to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')') r   
  join dbo.TECHCENTERS tc on (tc.Abbreviation = r.TECH_CENTER and r.PROD_GROUP_DESC = tc.Category)

Open in new window


Here is the query that doesn't work correctly with @groupby = project
SELECT r.*, tc.Grouping as Region, tc.ExtraTechs FROM OPENQUERY(ORASERVER,'select MAX(l.tech_center) as Tech_Center, MAX(l.lab_name) as Lab_Name, MAX(p.PROD_GROUP_DESC) as PROD_GROUP_DESC,
Avg(round(r.APPROVED_DATE - r.TEST_COMPLT_DATE,1)) as COMPL_DAYS,
Avg(round(r.APPROVED_DATE - r.SUBMITTED_DATE,1)) as REQ_DAYS,
MAX(p.PROJ_NBR) as PROJ_NBR, MAX(p.TITLE) as PROJ_TITLE,
MAX(to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')) as FROM_MONTH,
MAX(to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')) as Approved_month,
MAX(sd.spec_nbr) as Spec_Number, MAX(sd.title) as Spec_Name,
SUM(NVL(samples,1)) as samples,

MAX(r.APPROVED_DATE) as Approved_Date,
COUNT(DISTINCT tech.emp_id_key) as NUM_TECHS,

COUNT(*) as Procedures, 
SUM(NVL(samples,1)) as Proc_Count, 
SUM(NVL(tech_hrs,8)*NVL(samples,1)) as tech_hrs,
SUM(NVL(process_hrs,0)) as process_hrs,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''yes'' then 1 else 0 end) as MA_MEETS,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''sc'' then 1 else 0 end) as MA_SC,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''no'' then 1 else 0 end) as MA_DM,
sum(case when r.REQUEST_TYPE = ''mdl'' then 1 else 0 end) as MA_SUM,
sum(case when r.REQUEST_TYPE = ''cpnt'' and S.MEETS_CRITERIA_ID = ''yes'' then 1 else 0 end) as CA_MEETS,
sum(case when r.REQUEST_TYPE = ''cpnt'' then 1 else 0 end) as CA_SUM,
Sum(decode(S.MEETS_CRITERIA_ID,''yes'',1,0)) as MEETS_SPEC,
Sum(decode(S.MEETS_CRITERIA_ID,''sc'',1,0)) as SEE_CONCL,
Sum(decode(S.MEETS_CRITERIA_ID,''no'',1,0)) as DOESNT_MEET,
Sum(case when S.Iteration > 1 then 1 else 0 end) as Retries,
Sum(case when sd.spec_nbr is null then 1 else 0 end) as NON_STANDARD,
MAX(tech.full_name) as tech, Max(r.rpt_nbr) as rpt_nbr,
sum(case when r.REQUEST_TYPE = ''devl'' or r.REQUEST_TYPE = ''eval'' then 1 else 0 end) as DEVELOP_TEST,
sum(case when r.REQUEST_TYPE = ''mdl'' or r.REQUEST_TYPE = ''cpnt'' then 1 else 0 end) as APPROVAL_TEST,
sum(case when r.REQUEST_TYPE = ''devl'' or r.REQUEST_TYPE = ''eval'' then NVL(tech_hrs,8)*NVL(samples,1) else 0 end) as DEVELOP_HRS,
sum(case when r.REQUEST_TYPE = ''mdl'' or r.REQUEST_TYPE = ''cpnt'' then NVL(tech_hrs,8)*NVL(samples,1) else 0 end) as APPROVAL_HRS

from 

SJTCAPP.LAB_RPT r 
left join SJTCAPP.EPM_PROJECT_PUBLIC_V p on p.PROJ_NBR = r.PROJ_NBR
join SJTCAPP.LAB l on l.LAB_ID = r.LAB_ID
left join SJTCAPP.LAB_RPT_SPEC s on s.rpt_nbr = r.rpt_nbr
left join SJTCAPP.LAB_SPEC_DEFN sd on sd.spec_id = s.spec_id
left join SJTCAPP.LAB_SPEC_ASSOC sa on sa.spec_id = sd.spec_id and sa.lab_id = r.lab_id
left join SJTCAPP.LAB_RPT_SPEC_EMP se on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration
join sjtcapp.employee tech on tech.emp_id_key = se.emp_id_key

where 
      r.approved_date >= to_date(''2012-01-01'',''YYYY-MM-DD HH24:MI:SS'') and 
      r.APPROVED_DATE < to_date(''2014-03-01'',''YYYY-MM-DD HH24:MI:SS'') and

     (p.PROD_GROUP_DESC LIKE ''Ref'' or ''-1'' in (''Ref'')) and
     (l.TECH_CENTER IN (''-1'') or ''-1'' in (''-1'')) and
    (p.PROJ_NBR IN (''652647'') or ''-1'' in (''652647'')) and 
    (p.PROJ_TYPE IN (''-1'') or ''-1'' in (''-1'')) and 

    (r.LAB_ID IN (''-1'') or ''-1'' in (''-1'')) and 
    (sd.Active is NULL or sd.Active = ''y'') and
    r.STATUS_CODE <> ''can'' and r.STATUS_CODE <> ''dft'' group by to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY''), p.proj_nbr') r   
  join dbo.TECHCENTERS tc on (tc.Abbreviation = r.TECH_CENTER and r.PROD_GROUP_DESC = tc.Category)

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I see the issue:
MAX(to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')) as FROM_MONTH,
MAX(to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')) as Approved_month,

you must FIRST apply MAX, and then To_CHAR ... otherwise you will apply the MAX on the string values, and not on the date values...

hence, this should work correctly in both cases:
to_char(MAX(trunc(r.APPROVED_DATE,''Mon'')),''Mon-YY'') as FROM_MONTH,
to_char(MAX(to_char(trunc(r.APPROVED_DATE,''Mon'')),''Mon-YY'') as Approved_month,

Open in new window


see also this article, then:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 

Author Comment

by:newbie-netter
Comment Utility
That made so much sense, but still didn't resolve my issue.

This query is being generated by a stored procedure in SQL Server which is accessing the Oracle linked server.

Here's the data that is making up the query, so I should have dates in 2014 as well as 2013
TECH_CENTER	FROM_MONTH	APPROVED_MONTH	SAMPLES
WROC	13-May	13-May	1
WROC	13-May	13-May	1
CASR	13-Apr	13-Apr	1
CASR	13-Jun	13-Jun	1
CASR	13-Dec	13-Dec	1
CASR	13-Sep	13-Sep	1
CASR	13-Jun	13-Jun	1
CASR	13-Aug	13-Aug	1
CASR	13-Sep	13-Sep	1
CASR	14-Jan	14-Jan	1
CASR	13-Jul	13-Jul	1
CASR	13-Sep	13-Sep	1
CASR	14-Jan	14-Jan	1
CASR	13-Nov	13-Nov	1
CASR	13-Nov	13-Nov	1
CASR	13-Oct	13-Oct	1
CASR	13-Nov	13-Nov	1
CASR	13-Nov	13-Nov	1
CASR	13-Dec	13-Dec	1
CASR	13-Dec	13-Dec	1
WROC	13-Nov	13-Nov	2
CASR	14-Jan	14-Jan	1

Open in new window

Here's the query I'm using now after the change
SELECT r.*, tc.Grouping as Region, tc.ExtraTechs FROM OPENQUERY(ORASERVER,'select MAX(l.tech_center) as Tech_Center, MAX(l.lab_name) as Lab_Name, MAX(p.PROD_GROUP_DESC) as PROD_GROUP_DESC,
Avg(round(r.APPROVED_DATE - r.TEST_COMPLT_DATE,1)) as COMPL_DAYS,
Avg(round(r.APPROVED_DATE - r.SUBMITTED_DATE,1)) as REQ_DAYS,
MAX(p.PROJ_NBR) as PROJ_NBR, MAX(p.TITLE) as PROJ_TITLE,
to_char(MAX(trunc(r.APPROVED_DATE,''Mon'')),''Mon-YY'') as FROM_MONTH,
to_char(MAX(trunc(r.APPROVED_DATE,''Mon'')),''Mon-YY'') as Approved_month,
MAX(sd.spec_nbr) as Spec_Number, MAX(sd.title) as Spec_Name,
SUM(NVL(samples,1)) as samples,

MAX(r.APPROVED_DATE) as Approved_Date,
COUNT(DISTINCT tech.emp_id_key) as NUM_TECHS,

COUNT(*) as Procedures, 
SUM(NVL(samples,1)) as Proc_Count, 
SUM(NVL(tech_hrs,8)*NVL(samples,1)) as tech_hrs,
SUM(NVL(process_hrs,0)) as process_hrs,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''yes'' then 1 else 0 end) as MA_MEETS,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''sc'' then 1 else 0 end) as MA_SC,
sum(case when r.REQUEST_TYPE = ''mdl'' and S.MEETS_CRITERIA_ID = ''no'' then 1 else 0 end) as MA_DM,
sum(case when r.REQUEST_TYPE = ''mdl'' then 1 else 0 end) as MA_SUM,
sum(case when r.REQUEST_TYPE = ''cpnt'' and S.MEETS_CRITERIA_ID = ''yes'' then 1 else 0 end) as CA_MEETS,
sum(case when r.REQUEST_TYPE = ''cpnt'' then 1 else 0 end) as CA_SUM,
Sum(decode(S.MEETS_CRITERIA_ID,''yes'',1,0)) as MEETS_SPEC,
Sum(decode(S.MEETS_CRITERIA_ID,''sc'',1,0)) as SEE_CONCL,
Sum(decode(S.MEETS_CRITERIA_ID,''no'',1,0)) as DOESNT_MEET,
Sum(case when S.Iteration > 1 then 1 else 0 end) as Retries,
Sum(case when sd.spec_nbr is null then 1 else 0 end) as NON_STANDARD,
MAX(tech.full_name) as tech, Max(r.rpt_nbr) as rpt_nbr,
sum(case when r.REQUEST_TYPE = ''devl'' or r.REQUEST_TYPE = ''eval'' then 1 else 0 end) as DEVELOP_TEST,
sum(case when r.REQUEST_TYPE = ''mdl'' or r.REQUEST_TYPE = ''cpnt'' then 1 else 0 end) as APPROVAL_TEST,
sum(case when r.REQUEST_TYPE = ''devl'' or r.REQUEST_TYPE = ''eval'' then NVL(tech_hrs,8)*NVL(samples,1) else 0 end) as DEVELOP_HRS,
sum(case when r.REQUEST_TYPE = ''mdl'' or r.REQUEST_TYPE = ''cpnt'' then NVL(tech_hrs,8)*NVL(samples,1) else 0 end) as APPROVAL_HRS

from 

SJTCAPP.LAB_RPT r 
left join SJTCAPP.EPM_PROJECT_PUBLIC_V p on p.PROJ_NBR = r.PROJ_NBR
join SJTCAPP.LAB l on l.LAB_ID = r.LAB_ID
left join SJTCAPP.LAB_RPT_SPEC s on s.rpt_nbr = r.rpt_nbr
left join SJTCAPP.LAB_SPEC_DEFN sd on sd.spec_id = s.spec_id
left join SJTCAPP.LAB_SPEC_ASSOC sa on sa.spec_id = sd.spec_id and sa.lab_id = r.lab_id
left join SJTCAPP.LAB_RPT_SPEC_EMP se on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration
join sjtcapp.employee tech on tech.emp_id_key = se.emp_id_key

where 
      r.approved_date >= to_date(''2012-01-01'',''YYYY-MM-DD HH24:MI:SS'') and 
      r.APPROVED_DATE < to_date(''2014-03-01'',''YYYY-MM-DD HH24:MI:SS'') and

     (p.PROD_GROUP_DESC LIKE ''Ref'' or ''-1'' in (''Ref'')) and
     (l.TECH_CENTER IN (''-1'') or ''-1'' in (''-1'')) and
    (p.PROJ_NBR IN (''652647'') or ''-1'' in (''652647'')) and 
    (p.PROJ_TYPE IN (''-1'') or ''-1'' in (''-1'')) and 

    (r.LAB_ID IN (''-1'') or ''-1'' in (''-1'')) and 
    (sd.Active is NULL or sd.Active = ''y'') and
    r.STATUS_CODE <> ''can'' and r.STATUS_CODE <> ''dft'' group by p.proj_nbr, to_char(trunc(r.APPROVED_DATE,''Mon''),''Mon-YY'')') r   
  join dbo.TECHCENTERS tc on (tc.Abbreviation = r.TECH_CENTER and r.PROD_GROUP_DESC = tc.Category)

Open in new window

0
 

Author Comment

by:newbie-netter
Comment Utility
I also tried this but results were the same
	set @groupquery = ' group by p.proj_nbr, trunc(r.APPROVED_DATE,''''Year''''), trunc(r.APPROVED_DATE,''''Mon'''') '

Open in new window

0
 

Author Comment

by:newbie-netter
Comment Utility
Increased points to 500
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the fields named "*date", are they really "date" data type?
0
 

Author Comment

by:newbie-netter
Comment Utility
Yes
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
If you are grouping by month why use MAX() at all on that column?

& why have 2 columns that produce exactly the same data?
/* these are exactly the same, and should't need MAX() */
	, to_char(MAX(trunc(r.APPROVED_DATE, '' Mon '')), '' Mon - YY '') AS FROM_MONTH
	, to_char(MAX(trunc(r.APPROVED_DATE, '' Mon '')), '' Mon - YY '') AS Approved_month

...

GROUP BY p.proj_nbr
	, to_char(trunc(r.APPROVED_DATE, '' Mon ''), '' Mon - YY '')

Open in new window

&& How do you know what p.proj_nbr each row relates to if you don't include p.proj_nbr in the columns?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'm not sure what you are trying to achieve but it looks like a reporting SP that will accept a bunch of parameters to achieve different report variants and while you are attempting to modify the logic through different group by clauses you aren't intending to alter the columns that are output; I'm not sure that's going to work.

Providing us with sample data and expected results would assist in resolving your question.
0
 

Author Comment

by:newbie-netter
Comment Utility
I'm not always grouping by month, so that's why the max
2 columns for backwards compatibility with other reports
Proj number is included in result set, I just removed it to keep it simple
Looks like you're at a loss so you are being critical
The method I'm using works great for me and provides powerful stored procedures. Just having an issue with this one.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Yes of course I'm "being critical" - that is the job description.

>>" I just removed it to keep it simple"

Often "simplification" makes answering harder rather than simpler, and so it seems here. If you are outputting the project identity, does this also mean that the columns are "dynamic" by parameters too?

Can you provide the whole SP perhaps?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
I am also at a "dead point", as my visibility is limited.
I would hence request you to write a reduced test set (create table, insert rows, create procedure execute procedure) which shows the issue at it's simplest way.

Very likely, doing this, you will find the issue even yourself.
without this exercice, we will not be able to find the error for sure.
0
 

Author Comment

by:newbie-netter
Comment Utility
It ended up being the last join, .made it a left join and it works
0
 

Author Comment

by:newbie-netter
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for newbie-netter's comment #a39979273

for the following reason:

Looked into the data being returned and found the issue
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now