Small change needed for my SQL 2

[Andrzejek]

I have this SQL:

[blue]
SELECT MYPROJNO.PSPROJNO_TYPE_OF_PROJECT [/blue]|| ' ' ||

( select substr(max(sys_connect_by_path(PSDSNO_DESIGNNUMBER,',')),2) 
 from
 (select PSDSNO_PROJECTNUMBER, PSDSNO_DESIGNNUMBER, row_number() 
over (partition by PSDSNO_PROJECTNUMBER order by PSDSNO_DESIGNNUMBER) rn
 from S4111000.PSDSNO_INFORMATION 
 WHERE PSDSNO_PROJECTNUMBER =([b]'BRF-006-1(114)--38-78'[/b]))
 start with rn = 1
 connect by prior rn = rn -1
 and prior PSDSNO_PROJECTNUMBER = PSDSNO_PROJECTNUMBER)  
[blue]
AS " ", [b]MYLET.PSLET_PROJECTNUMBER AS "Project Number", [/b]
 MYSTATUS.PSSTATUS_STATUS AS "Status", 
 MYLET.PSLET_ROAD_SQUAD_FULLNAME AS "Assigned To", 
 MYWKCODE.PSWKCODE_DESCRIPTION AS "Type of Work", 
 TO_CHAR(MYLET.PSLET_PLANTURNINDATE, 'MM/DD/YYYY') AS "Plan Turn-In", 
 TO_CHAR(MYLET.PSLET_CONTRACTLETDATE, 'MM/DD/YYYY') AS "Letting Date", 
 '$' || MYLET.PSLET_CURFIVEYREST AS "Program Est", 
 '$' || MYLET.PSLET_CURDEVELOPEST AS "Develop Est", 
 MYLET.PSLET_AWARDEDCONTRACT AS "Awarded", 
 MYLET.PSLET_PAID_TO_DATE AS "Paid To Date", 
 MYLET.PSLET_FINALCONTRACT AS "Final Project $" 
 FROM S4111000.PSLET_INFORMATION MYLET INNER JOIN 
 S4111000.PSPROJNO_INFORMATION MYPROJNO 
 ON MYLET.PSLET_PROJECTID = MYPROJNO.PSPROJNO_PROJECTID AND 
 MYLET.PSLET_PROJECTNUMBER = MYPROJNO.PSPROJNO_PROJECTNUMBER INNER JOIN 
 S4111000.PSSTATUS MYSTATUS ON MYLET.PSLET_DELBYTE = MYSTATUS.PSSTATUS_DELBYTE 
 INNER JOIN 
 S4111000.PSWKCODE_INFORMATION MYWKCODE ON 
 MYLET.PSLET_WORKCODE1 = MYWKCODE.PSWKCODE_WORKCODE4 
 WHERE (MYLET.PSLET_PROJECTNAME = '98-36-002-010-04') 
 AND (NOT (MYPROJNO.PSPROJNO_PROJECT_RANK = 12 OR 
 MYPROJNO.PSPROJNO_PROJECT_RANK = 13)) 
 ORDER BY "Letting Date", "Plan Turn-In", "Project Number"[/blue]

If I just run BLUE part of this SQL, it runs fine.
If I just run BLACK part inside () it runs fine.

I can run this whole SQL fine, but I would like to replace hardcoded (bolded) Project Number 'BRF-006-1(114)--38-78' with the field from BLUE part (MYLET.PSLET_PROJECTNUMBER AS "Project Number") and that's where the problem is.

How can I read the Project Number from my BLUE Sql inside my BLACK Select statement?

Anybody has any ideas?

Have fun.

---- Andy

 

[SantaMufasa]

Andy,

First, I propose that you use code alignment to cause more intuitive understanding of your code. Then, to resolve your issue, simply move your "BLACK part inside ()" down to your BLUE part's from statement:

[blue]SELECT MYPROJNO.PSPROJNO_TYPE_OF_PROJECT || ' ' ||[/blue][black][b]x[/b][/black][blue] AS " "
      ,MYLET.PSLET_PROJECTNUMBER AS "Project Number"
      ,MYSTATUS.PSSTATUS_STATUS AS "Status"
      ,MYLET.PSLET_ROAD_SQUAD_FULLNAME AS "Assigned To"
      ,MYWKCODE.PSWKCODE_DESCRIPTION AS "Type of Work"
      ,TO_CHAR(MYLET.PSLET_PLANTURNINDATE, 'MM/DD/YYYY') AS "Plan Turn-In"
      ,TO_CHAR(MYLET.PSLET_CONTRACTLETDATE, 'MM/DD/YYYY') AS "Letting Date"
      ,'$' || MYLET.PSLET_CURFIVEYREST AS "Program Est"
      ,'$' || MYLET.PSLET_CURDEVELOPEST AS "Develop Est"
      ,MYLET.PSLET_AWARDEDCONTRACT AS "Awarded"
      ,MYLET.PSLET_PAID_TO_DATE AS "Paid To Date"
      ,MYLET.PSLET_FINALCONTRACT AS "Final Project $" 
  FROM [/blue][black](select substr(max(sys_connect_by_path(PSDSNO_DESIGNNUMBER,',')),2) AS X
          from (select PSDSNO_PROJECTNUMBER
                      ,PSDSNO_DESIGNNUMBER
                      ,row_number() over (partition by PSDSNO_PROJECTNUMBER
                                          order by PSDSNO_DESIGNNUMBER) rn
                  from S4111000.PSDSNO_INFORMATION 
                 WHERE PSDSNO_PROJECTNUMBER =('BRF-006-1(114)--38-78')
                )
         start with rn = 1
       connect by prior rn = rn -1
              and prior PSDSNO_PROJECTNUMBER = PSDSNO_PROJECTNUMBER)[/black][blue]
      ,S4111000.PSLET_INFORMATION MYLET
           INNER JOIN S4111000.PSPROJNO_INFORMATION MYPROJNO 
                   ON MYLET.PSLET_PROJECTID = MYPROJNO.PSPROJNO_PROJECTID
                  AND MYLET.PSLET_PROJECTNUMBER = MYPROJNO.PSPROJNO_PROJECTNUMBER
           INNER JOIN S4111000.PSSTATUS MYSTATUS
                   ON MYLET.PSLET_DELBYTE = MYSTATUS.PSSTATUS_DELBYTE 
           INNER JOIN S4111000.PSWKCODE_INFORMATION MYWKCODE
                   ON MYLET.PSLET_WORKCODE1 = MYWKCODE.PSWKCODE_WORKCODE4 
 WHERE (MYLET.PSLET_PROJECTNAME = '98-36-002-010-04') 
   AND (NOT (MYPROJNO.PSPROJNO_PROJECT_RANK = 12
         OR  MYPROJNO.PSPROJNO_PROJECT_RANK = 13)
       ) 
 ORDER BY "Letting Date", "Plan Turn-In", "Project Number"[/blue]

Notice that in your "BLACK" code, I placed an alias (i.e. "x") on the expression your want to display so that you can refer to "x" as an expression in your outer SELECT where you now have the inner SELECT (black in parentheses). You can also then refer to your BLUE SQL's "Project Number" in your outer SQL's WHERE statement to join the inner SELECT's rows to the out SELECT's project number.

If you have questions, please post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Andrzejek]

Thanks Santa,
That makes more sense and it works, but the original questions is still there - How do I replace a hardcoded Project Number ('BRF-006-1(114)--38-78' in BLACK portion) with MYLET.PSLET_PROJECTNUMBER or "Project Number" (from BLUE sql) ?

MYLET table is not mentioned in black sql.
Can I refear to it somehow in BLACK select?

If I replace 'BRF-006-1(114)--38-78' with "Project Number" I get ORA-00904: "Project Number": invalid identifier

If I replace 'BRF-006-1(114)--38-78' with MYLET.PSLET_PROJECTNUMBER ORA-00904: "MYLET"."PSLET_PROJECTNUMBER": invalid identifier

SELECT MYPROJNO.PSPROJNO_TYPE_OF_PROJECT || ' ' ||x AS " "
      ,[COLOR=red yellow]MYLET.PSLET_PROJECTNUMBER AS "Project Number"[/color]
      ,MYSTATUS.PSSTATUS_STATUS AS "Status"
      ,MYLET.PSLET_ROAD_SQUAD_FULLNAME AS "Assigned To"
      ,MYWKCODE.PSWKCODE_DESCRIPTION AS "Type of Work"
      ,TO_CHAR(MYLET.PSLET_PLANTURNINDATE, 'MM/DD/YYYY') AS "Plan Turn-In"
      ,TO_CHAR(MYLET.PSLET_CONTRACTLETDATE, 'MM/DD/YYYY') AS "Letting Date"
      ,'$' || MYLET.PSLET_CURFIVEYREST AS "Program Est"
      ,'$' || MYLET.PSLET_CURDEVELOPEST AS "Develop Est"
      ,MYLET.PSLET_AWARDEDCONTRACT AS "Awarded"
      ,MYLET.PSLET_PAID_TO_DATE AS "Paid To Date"
      ,MYLET.PSLET_FINALCONTRACT AS "Final Project $" 
  FROM (select substr(max(sys_connect_by_path(PSDSNO_DESIGNNUMBER,',')),2) AS X
          from (select PSDSNO_PROJECTNUMBER
                      ,PSDSNO_DESIGNNUMBER
                      ,row_number() over (partition by PSDSNO_PROJECTNUMBER
                                          order by PSDSNO_DESIGNNUMBER) rn
                  from S4111000.PSDSNO_INFORMATION 
                 WHERE PSDSNO_PROJECTNUMBER =[COLOR=red yellow]('BRF-006-1(114)--38-78')[/color]
                )
         start with rn = 1
       connect by prior rn = rn -1
              and prior PSDSNO_PROJECTNUMBER = PSDSNO_PROJECTNUMBER)
      ,S4111000.PSLET_INFORMATION MYLET
           INNER JOIN S4111000.PSPROJNO_INFORMATION MYPROJNO 
                   ON MYLET.PSLET_PROJECTID = MYPROJNO.PSPROJNO_PROJECTID
                  AND MYLET.PSLET_PROJECTNUMBER = MYPROJNO.PSPROJNO_PROJECTNUMBER
           INNER JOIN S4111000.PSSTATUS MYSTATUS
                   ON MYLET.PSLET_DELBYTE = MYSTATUS.PSSTATUS_DELBYTE 
           INNER JOIN S4111000.PSWKCODE_INFORMATION MYWKCODE
                   ON MYLET.PSLET_WORKCODE1 = MYWKCODE.PSWKCODE_WORKCODE4 
 WHERE (MYLET.PSLET_PROJECTNAME = '98-36-002-010-04') 
   AND (NOT (MYPROJNO.PSPROJNO_PROJECT_RANK = 12
         OR  MYPROJNO.PSPROJNO_PROJECT_RANK = 13)
       ) 
 ORDER BY "Letting Date", "Plan Turn-In", "Project Number"

Have fun.

---- Andy

 

[jaxtell]

how about this?

SELECT MYPROJNO.PSPROJNO_TYPE_OF_PROJECT || ' ' ||x AS " "
      ,MYLET.PSLET_PROJECTNUMBER AS "Project Number"
      ,MYSTATUS.PSSTATUS_STATUS AS "Status"
      ,MYLET.PSLET_ROAD_SQUAD_FULLNAME AS "Assigned To"
      ,MYWKCODE.PSWKCODE_DESCRIPTION AS "Type of Work"
      ,TO_CHAR(MYLET.PSLET_PLANTURNINDATE, 'MM/DD/YYYY') AS "Plan Turn-In"
      ,TO_CHAR(MYLET.PSLET_CONTRACTLETDATE, 'MM/DD/YYYY') AS "Letting Date"
      ,'$' || MYLET.PSLET_CURFIVEYREST AS "Program Est"
      ,'$' || MYLET.PSLET_CURDEVELOPEST AS "Develop Est"
      ,MYLET.PSLET_AWARDEDCONTRACT AS "Awarded"
      ,MYLET.PSLET_PAID_TO_DATE AS "Paid To Date"
      ,MYLET.PSLET_FINALCONTRACT AS "Final Project $" 
  FROM (select substr(max(sys_connect_by_path(PSDSNO_DESIGNNUMBER,',')),2) AS X, [b]psdsno_projectnumber as pn[/b]
          from (select PSDSNO_PROJECTNUMBER
                      ,PSDSNO_DESIGNNUMBER
                      ,row_number() over (partition by PSDSNO_PROJECTNUMBER
                                          order by PSDSNO_DESIGNNUMBER) rn
                  from S4111000.PSDSNO_INFORMATION 
                )
         start with rn = 1
       connect by prior rn = rn -1
              and prior PSDSNO_PROJECTNUMBER = PSDSNO_PROJECTNUMBER)
      ,S4111000.PSLET_INFORMATION MYLET
           INNER JOIN S4111000.PSPROJNO_INFORMATION MYPROJNO 
                   ON MYLET.PSLET_PROJECTID = MYPROJNO.PSPROJNO_PROJECTID
                  AND MYLET.PSLET_PROJECTNUMBER = MYPROJNO.PSPROJNO_PROJECTNUMBER
           INNER JOIN S4111000.PSSTATUS MYSTATUS
                   ON MYLET.PSLET_DELBYTE = MYSTATUS.PSSTATUS_DELBYTE 
           INNER JOIN S4111000.PSWKCODE_INFORMATION MYWKCODE
                   ON MYLET.PSLET_WORKCODE1 = MYWKCODE.PSWKCODE_WORKCODE4 
 WHERE [b]mylet.pslet_project_number = pn
and[/b] (MYLET.PSLET_PROJECTNAME = '98-36-002-010-04') 
   AND (NOT (MYPROJNO.PSPROJNO_PROJECT_RANK = 12
         OR  MYPROJNO.PSPROJNO_PROJECT_RANK = 13)
       ) 
 ORDER BY "Letting Date", "Plan Turn-In", "Project Number"

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[SantaMufasa]

Jaxtell,

Frankly, that was my thought, but I was worried that moving the WHERE down to the outer query will return so many addition rows to the inner connect by...start with that it might disrupt the rest of Andy's query logic.

So, if Andy can perform a smoke test on your code, that would allay my concern, above.

Let us know, Andy, how the test of Jaxtell's code goes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Andrzejek]

Got: ORA-00937: not a single-group group function
and highlighted:

SELECT MYPROJNO.PSPROJNO_TYPE_OF_PROJECT || ' ' ||x AS " "
      ,MYLET.PSLET_PROJECTNUMBER AS "Project Number"
      ,MYSTATUS.PSSTATUS_STATUS AS "Status"
      ,MYLET.PSLET_ROAD_SQUAD_FULLNAME AS "Assigned To"
      ,MYWKCODE.PSWKCODE_DESCRIPTION AS "Type of Work"
      ,TO_CHAR(MYLET.PSLET_PLANTURNINDATE, 'MM/DD/YYYY') AS "Plan Turn-In"
      ,TO_CHAR(MYLET.PSLET_CONTRACTLETDATE, 'MM/DD/YYYY') AS "Letting Date"
      ,'$' || MYLET.PSLET_CURFIVEYREST AS "Program Est"
      ,'$' || MYLET.PSLET_CURDEVELOPEST AS "Develop Est"
      ,MYLET.PSLET_AWARDEDCONTRACT AS "Awarded"
      ,MYLET.PSLET_PAID_TO_DATE AS "Paid To Date"
      ,MYLET.PSLET_FINALCONTRACT AS "Final Project $" 
  FROM (select substr(max(sys_connect_by_path(PSDSNO_DESIGNNUMBER,',')),2) AS X, [COLOR=red yellow]psdsno_projectnumber[/color] as pn
          from (select PSDSNO_PROJECTNUMBER
                      ,PSDSNO_DESIGNNUMBER
                      ,row_number() over (partition by PSDSNO_PROJECTNUMBER
                                          order by PSDSNO_DESIGNNUMBER) rn
                  from S4111000.PSDSNO_INFORMATION 
                )
         start with rn = 1
       connect by prior rn = rn -1
              and prior PSDSNO_PROJECTNUMBER = PSDSNO_PROJECTNUMBER)
      ,S4111000.PSLET_INFORMATION MYLET
           INNER JOIN S4111000.PSPROJNO_INFORMATION MYPROJNO 
                   ON MYLET.PSLET_PROJECTID = MYPROJNO.PSPROJNO_PROJECTID
                  AND MYLET.PSLET_PROJECTNUMBER = MYPROJNO.PSPROJNO_PROJECTNUMBER
           INNER JOIN S4111000.PSSTATUS MYSTATUS
                   ON MYLET.PSLET_DELBYTE = MYSTATUS.PSSTATUS_DELBYTE 
           INNER JOIN S4111000.PSWKCODE_INFORMATION MYWKCODE
                   ON MYLET.PSLET_WORKCODE1 = MYWKCODE.PSWKCODE_WORKCODE4 
 WHERE mylet.pslet_projectnumber = pn
and (MYLET.PSLET_PROJECTNAME = '98-36-002-010-04') 
   AND (NOT (MYPROJNO.PSPROJNO_PROJECT_RANK = 12
         OR  MYPROJNO.PSPROJNO_PROJECT_RANK = 13)
       ) 
 ORDER BY "Letting Date", "Plan Turn-In", "Project Number"

But just to let you know - I used another approach: I have another query where I get what I wanted from the inner Select and I filter on it for each record. Seams to work just fine. I was just hoping to get it in just one SELECT query, but what can you do.....

I do appreciate your help though.

Have fun.

---- Andy

 

[jaxtell]

Hi Andy,
A

 group by psdsno_projectnumber

after

and prior PSDSNO_PROJECTNUMBER = PSDSNO_PROJECTNUMBER

and before the ")" should take care of that issue.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Andrzejek]

Thanks jaxtell and Santa,
SQL started to work fine.

I don't know how do you do it - looking at somebody's weird SQL and figure out how to fix it. I'm looking at my data that I do know and I can't figure it out......

Have fun.

---- Andy

 

[jaxtell]

Hi Andy,
I lucked out. If you check the 10g forum you'll see that I had a very similar problem just prior to your question. I spent enough hours struggling through it that I won't soon forget

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/