Solved

Cross referencing a column and adding to another

Posted on 2009-05-13
9
170 Views
Last Modified: 2012-05-06
This is the current infromation the sql query below pulls, what I need to do is add a field
called ACN and then count in tblACNRequest the # of Para and Lines that show up for requests
that match what I am pulling.  So for instance I have saved the 1st para and line in tblACNRequest  so in my query it would count a 1 towards that para and line.

UIC    Para  Line  Grade  DMOS Gender Auth  Asgn  OS  VAC  UNIT  Status
PULAA  101   01    O5     42B0       I      1         1      0    0    MTOE   Critical
PULAA  101   02    O4     42B0       I      1          0        0    1   MTOE         Critical

This is what is saved in tblACNRequest
intRecID  strACN    Para   Line  strUIC  dtExpire  dtTimeStamp
    1    200917-05     101     01 PULAA  5/1/2009   5/1/2009
SELECT   at.UPC as UIC,
                at.AUTH_PARA_DSG as Para,
                at.AUTH_LINE_DSG as Line,
                at.GRADE as Grade, 
                substring(at.POSC, 1, 4) as DMOS, 
                case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
               at.AUTH_STR,
               at.ASGN_STR,
             case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end as 'OS',
               at.AUTH_STR + case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR as VAC, 
             case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
             case s.strStat when 'C' then 'Critical' when 'L' then 'Locked' else 'None' end as Status
from         SIPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC Inner Join
                SIPERS.MNLOCAL_APVSE_UIC_TBL mn on mn.UIC = at.UPC Left Outer join
                tblStatUic as s on s.strUIC = at.UPC WHERE substring(at.POSC, 1, 4) = '42B0'

Open in new window

0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 3

Accepted Solution

by:
alchemyst999 earned 500 total points
ID: 24374715
The attached code may work

Cheers,
Al
SELECT   at.UPC as UIC,
                at.AUTH_PARA_DSG as Para,
                at.AUTH_LINE_DSG as Line,
                at.GRADE as Grade, 
                substring(at.POSC, 1, 4) as DMOS, 
                case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
               at.AUTH_STR,
               at.ASGN_STR,
             case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end as 'OS',
               at.AUTH_STR + case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR as VAC, 
             case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
             case s.strStat when 'C' then 'Critical' when 'L' then 'Locked' else 'None' end as Status,
			 plc.PLCount as ACN
from         SIPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC Inner Join
                SIPERS.MNLOCAL_APVSE_UIC_TBL mn on mn.UIC = at.UPC Left Outer join
                tblStatUic as s on s.strUIC = at.UPC WHERE substring(at.POSC, 1, 4) = '42B0' Left join
				(SELECT AR.Para, AR.Line, COUNT(*) As PLCount
				FROM 	tblACNRequest As AR
				GROUP BY AR.Para, AR.Line) As plc on (at.AUTH_PARA_DSG = plc.para) and (at.AUTH_LINE_DSG = plc.line)

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 24375244
HI, I got this to work somewhat, but the problem is that it shows the same field 7 times but only counts one of them.

SELECT      at.UPC as UIC,
        at.AUTH_PARA_DSG as Para,
        at.AUTH_LINE_DSG as Line,
        at.GRADE as Grade,
        substring(at.POSC, 1, 4) as DMOS,
        case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
      at.AUTH_STR,
      at.ASGN_STR,
      case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end as 'OS',
      (select Count(r.strUIc) tblACNReques where r.strPara in (select at.AUTH_PARA_DSG from SIDPERS..SIDPERS.PERS_AUTH_STR_TBL where at.auth_Para_DSG = r.strPara) and
      r.strLine in (Select at.AUTH_LINE_DSG from SIDPERS..SIDPERS.PERS_AUTH_STR_TBL where at.auth_Line_DSG = r.strLine)) as ACN,
      at.AUTH_STR + case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR as VAC,
      case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
      case s.strStat when 'C' then 'Critical' when 'L' then 'Locked' else 'None' end as Status
from    SIDPERS..SIDPERS.PERS_AUTH_STR_TBL at INNER JOIN
        SIDPERS..SIDPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC Inner Join
        SIDPERS..SIDPERS.MNLOCAL_APVSE_UIC_TBL mn on mn.UIC = at.UPC Left Outer join
       tbtblStatUic as s on s.strUIC = at.UPC
Group by  at.upc, at.AUTH_PARA_DSG, at.AUTH_LINE_DSG, at.GRADE, at.POSC, at.AUTH_PERS_IDENT,  at.AUTH_STR, at.ASGN_STR, at.AUTH_DOC_POSN_TITLE, r.strPara, r.strLine, s.strStat, at.DOC_NBR


0
 
LVL 41

Expert Comment

by:Sharath
ID: 24375804
What is your expected result for the sample set provided?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:kdeutsch
ID: 24375936
Hi,
teh resulat should be like this, only with the field ACn now added
UIC    Para  Line  Grade  DMOS Gender Auth  Asgn  OS  VAC  UNIT  Status
PULAA  101   01    O5     42B0       I      1         1      0    0    MTOE   Critical
PULAA  101   02    O4     42B0       I      1          0        0    1   MTOE         Critical

But what I get is each of these multiplied 7 times, but only one of them has the ACN count and the rest are null
0
 
LVL 3

Expert Comment

by:alchemyst999
ID: 24376109
Well, you could always add 'WHERE ACN IS NOT NULL' to the end, but that doesnt realy sort the issues with the code out (and at first glance that will be running in cubic time/O(N^3)).

Was there any issues the code I added above?
0
 

Author Comment

by:kdeutsch
ID: 24376211
Hi,
Here are the erros it gave me
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'Left'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'As'.

I took out the where statement and then it gives me this error
LE DB provider 'sidpers' does not contain table '"sidpers"."PERS_UNIT_TBL"'.  The table either does not exist or the current user does not have permissions on that table.

But if I take out added sql it works just fine and has access to table.

0
 
LVL 3

Expert Comment

by:alchemyst999
ID: 24376434
Is it supposed to be 'SIPERS' or 'SIDPERS..SIDPERS', you seem to have switched up.
0
 

Author Comment

by:kdeutsch
ID: 24378454
Hi,
It is sidpers..sidpers, I cut out the link protion before so it did not look so messed up on page.
0
 

Author Closing Comment

by:kdeutsch
ID: 31580943
Closing old question, this worked but changed order or group by
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

630 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