Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle group by

Posted on 2014-03-30
17
Medium Priority
?
319 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39965882
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
ID: 39966359
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39966530
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:newbie-netter
ID: 39966607
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39966715
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
ID: 39967365
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
ID: 39967391
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
ID: 39967417
Increased points to 500
0
 
LVL 143

Expert Comment

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

Author Comment

by:newbie-netter
ID: 39967739
Yes
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39968161
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 49

Expert Comment

by:PortletPaul
ID: 39968176
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
ID: 39968351
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 49

Expert Comment

by:PortletPaul
ID: 39968472
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 39968565
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
ID: 39979273
It ended up being the last join, .made it a left join and it works
0
 

Author Comment

by:newbie-netter
ID: 39979279
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

783 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