Home » SQL & PL/SQL » SQL & PL/SQL » Queries (merged 4)
icon5.gif  Queries (merged 4) [message #277243] Mon, 29 October 2007 05:29 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi all,

I need to diplay in this format


Data References
1

I written this script

select 'Data References' from dual
union
select APP_ORIG_SYS_XREF,count( *)
from omf_api_app_evw
group by app_orig_sys_xref
having count(*)>1

when i complied this script i got error like "query block has incorrect number of result columns"..pls help me out to solve this prob
icon5.gif  Query [message #277249 is a reply to message #277243] Mon, 29 October 2007 05:37 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi,

This is my requirement

APP_REQ_ADV_AMT should be equal to
APP_PURCHASE_PRICE
+APP_ADMIN_FEE
-APP_CASH_DEPOSIT
-APP_POST_SALE_TAX
-APP_RUNNING_COS_DEPOSIT
-APP_BUYUP_CASH_DEP


Please help me how to write the query in below that requirement without using function...Just i need simple query
Re: Query [message #277253 is a reply to message #277249] Mon, 29 October 2007 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select APP_PURCHASE_PRICE
+APP_ADMIN_FEE
-APP_CASH_DEPOSIT
-APP_POST_SALE_TAX
-APP_RUNNING_COS_DEPOSIT
-APP_BUYUP_CASH_DEP
as APP_REQ_ADV_AMT
from mytable
/

Regards
Michel
Re: Query Help [message #277256 is a reply to message #277243] Mon, 29 October 2007 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't want to read guidelines and documentation.
Go on like that!

Regards
Michel
Re: Query [message #277260 is a reply to message #277249] Mon, 29 October 2007 05:55 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
thnks for u reply

But "APP_REQ_ADV_AMT " is a column name ... I have to compare this column(APP_REQ_ADV_AMT ) with the other column... But ur query to mentioned tht column as Aliase name
Re: Query [message #277263 is a reply to message #277260] Mon, 29 October 2007 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you already try?

read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

(I know it is useless to repeat this once more to you but who knows sometimes someone becomes smarter so I let you a chance.)

Regards
Michel
icon5.gif  Date query [message #277291 is a reply to message #277243] Mon, 29 October 2007 07:40 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi all,

This is my requirement

APP_FIRST_PMT_DT + APP_TERM Should equal to APP_MATURITY_DT

In my table
APP_FIRST_PMT_DT---date datatype(Enter the first payment date.)
APP_TERM ----number datatype(Enter the term/number of payments.)
APP_MATURITY_DT---Date Datatype

please write the query for the above requirement
Re: Date query [message #277294 is a reply to message #277291] Mon, 29 October 2007 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I love your subjects.

Regards
Michel
Re: Queries (merged 3) [message #277315 is a reply to message #277243] Mon, 29 October 2007 09:25 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Thank you Michel
icon5.gif  Using Subquery [message #277332 is a reply to message #277243] Mon, 29 October 2007 11:11 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
HI ALL,

This is the first table script

create table OMF_API_NEDFLEET_TXN
(
RCA_NO NUMBER,
RCA_ASSET_REGN_NO VARCHAR2(30),
RCA_CARD_NO VARCHAR2(30),
RCA_TXN_DATE DATE,
RCA_NET_AMOUNT NUMBER,
RCA_VAT_AMOUNT NUMBER,
RCA_GROSS_AMOUNT NUMBER,
RCA_DESCRIPTION VARCHAR2(240),
RCA_KILOMETERS NUMBER,
RCA_TXN_REFERENCE VARCHAR2(30),
CREATED_BY VARCHAR2(30) default 'CON',
CREATION_DATE DATE default SYSDATE,
LAST_UPDATED_BY VARCHAR2(30) default 'CON',
LAST_UPDATE_DATE DATE default SYSDATE,
NEDTXN_ID NUMBER default '0',
NEDTXN_RUN_DT DATE default SYSDATE
)

Second Table script
create table OMF_API_TXNS
(
TXN_ACC_ORIG_SYS_XREF VARCHAR2(30),
TXN_SEQ NUMBER,
TXN_RUN_DT DATE,
TXN_TCD_CODE VARCHAR2(30),
TXN_REASON_CD VARCHAR2(30),
TXN_MODE_CD VARCHAR2(30),
TXN_REFERENCE VARCHAR2(30),
TXN_COMMENT VARCHAR2(240),
TXN_BALANCE_AMT NUMBER,
TXN_POST_DT DATE,
TXN_DT DATE,
TXN_AMT NUMBER,
TXN_SPR_SPREAD VARCHAR2(30),
PAL_AMT_ADV NUMBER,
PAL_AMT_INT NUMBER,
PAL_AMT_LC NUMBER,
PAL_AMT_NSF NUMBER,
PAL_AMT_OVERAGE NUMBER,
TXN_PRIMARY_IND VARCHAR2(30)
)

And this is my requirement
****Sum of RCA_NET_AMOUNT for one RCA_TXN_REFERENCE should be equal to TXN_AMT in the OMF_API_TXNS file for that
given TXN_REFERENCE...


Please give me to find this solution
Re: Using Subquery [message #277342 is a reply to message #277332] Mon, 29 October 2007 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has to nothing to do in Suggestion & Feedback forum.
You can't follow guidelines, you can't read the documentation, and now you can't post in the correct forum.
You gonna better and better at each post.

Regards
Michel
Re: Using Subquery [message #277441 is a reply to message #277332] Tue, 30 October 2007 02:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't understand the requirement I'm afraid.

Are you looking to retrieve only sets of rows where, for each txn_reference, the sum of RCA_NET_AMOUNT = TXN_AMT, or are you looking to retrieve a set of RCA_NET_AMOUNTS such that they are equal to the relevant TXN_AMT.

Assuming it's the former, then something like:
SELECT o.txn_reference, o.txn_amt
FROM  (SELECT txn_reference,sum(rca_net_amount) sum_rca_net_amount
       FROM   OMF_API_NEDFLEET_TXN
       GROUP BY txn_reference) n
     ,OMF_API_TXNS         o
WHERE n.txn_reference = o.txn_reference
AND   o.txn_amt = n.sum_rca_net_amount
should work.
Re: Queries (merged 4) [message #277448 is a reply to message #277243] Tue, 30 October 2007 02:59 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Thank u Jrow

Yes i am looking this requirement to retrieve only sets of rows where, for each txn_reference, the sum of RCA_NET_AMOUNT = TXN_AMT...

Re: Queries (merged 4) [message #277449 is a reply to message #277243] Tue, 30 October 2007 03:03 Go to previous message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
One more problem as the same table

RCA_TXN_REFERENCE should link the TXN_REFERENCE in the OMF_API_TXNS file for the txn_dt = RCA_TXN_DATE

[mod-edit] made it more readable.

[Updated on: Tue, 30 October 2007 08:11] by Moderator

Report message to a moderator

Previous Topic: Updating records of a collection
Next Topic: caseexpression
Goto Forum:
  


Current Time: Sun Dec 11 08:20:07 CST 2016

Total time taken to generate the page: 0.08391 seconds