Inline View assist please

[satinsilhouette]

Hi,
I posted yesterday and got some help from SantaMufasa. Thank you again.
Today I trying to write an inline view in Oracle, which the syntax I will give here generally works fine in SQL or as a T-SQL statement sent through CR XI.

It isn't working here.

What I need is to pull the three fields from the inline view and show them in the main query so they show up like fields. I have looked online for some help but the syntax I attempted to use didn't work. It seems to be lacking something. It will error out on the additional fields pulling up from the inline view.

Any suggestions as to why this normal query will work in SQL and not in Oracle I would greatly appreciate.

Here's the code sample:

SELECT table1.AGING_DATE,
table2.ACCT_CLASS_id,
table6.NAME,
table2.table2_id,
table2.name,
table2.in_date_time,
table2.out_date_time,
table2.tot_acct_bal,
table2.num_of_stmts_sent,
table2.frst_det_bill_date,
table3.client_ID,
table3.client_NAME,
table.FC_NAME,
table4.client_ID,
table1.table2_ID,
table1.AMOUNT,
Inlineview.Post_Date,
Inlineview.LPAmount,
Inlineview.TXID

FROM (((((table1 table1
LEFT OUTER JOIN table4 table4 ON table1.BUCKET_ID=table4.BUCKET_ID)
LEFT OUTER JOIN table2 table2 ON table1.table2_ID=table2.table2_ID)
LEFT OUTER JOIN table3 table3 ON table4.client_ID=table3.client_ID)
LEFT OUTER JOIN table table ON table3.FC=table.FC)
LEFT OUTER JOIN table7 table6 ON table2.ACCT_id=table6.ACCT_id)
LEFT OUTER JOIN
(select max(table8.post_date)as "Post_Date",table8.amount as"LPAmount", table8.table2_id as "TXID"
from table8
where table8.tx_type = 2
Group By table8.tx_post_date,table8.amount,table8.table2_id
) InlineView
where
InlineView.TXID = table2.table2_ID

Thanks!

 

[BJCooperIT]

Please define "isn't working". Are you receiving a syntax error? Is the data wrong?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[satinsilhouette]

I am sorry I thought I stated that I was erroring out on the fields I am trying to pull from the inline view. The error generally says I am missing a key word.

Thanks so much!
satinsilhouette

 

[satinsilhouette]

Thank you BJ for your assist, I figured it out. If you are requesting any type of datatype other than interger out of an inline view you need to put quotes around the table name and the field name for it to work.

Thanks so much!
satinsilhouette

 

[SantaMufasa]

Actually, Satin, I believe that the issue is that when you create any type of view (in-line or otherwise), if you wish to refer to composite expressions (i.e., expressions made up of operations and/or functions), you must assign an alias reference name to the composite expression. The alias may be in quotes or not.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Dagon]

The problem is actually that you put Post_Date in quotes in the inline select but not in the outer select. Putting quotes round it means it becomes case sensitive. Because the version in the outer loop is not in quotes, the case sensitivity is lost there and Oracle expects to see a column called "POST_DATE" but it actually gets "Post_Date". You need to have both in quotes or neither. It has nothing to do with data types.

 

[cmmrfrds]

LEFT OUTER JOIN
(select max(table8.post_date)as "Post_Date",table8.amount as"LPAmount", table8.table2_id as "TXID"
from table8
where table8.tx_type = 2
Group By table8.tx_post_date,table8.amount,table8.table2_id
) InlineView
ON InlineView.TXID = table2.table2_ID

Not sure why you need below?
where
InlineView.TXID = table2.table2_ID