Solved

Cross referencing a column and adding to another

Posted on 2009-05-13
9
168 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
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: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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 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