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

x
?
Solved

Cross referencing a column and adding to another

Posted on 2009-05-13
9
Medium Priority
?
176 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 2000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

927 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