Solved

Cross referencing a column and adding to another

Posted on 2009-05-13
9
166 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
  • 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 40

Expert Comment

by:Sharath
ID: 24375804
What is your expected result for the sample set provided?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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