| Author |
Message |
    
Nine
Pilla Bewarse
Username: Nine
Post Number: 170
Registered: 04-2005
Posted From: 144.42.9.173
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, July 31, 2007 - 8:54 am: |    |
ippudu work avutundi chudu.. nenu use chesina syntax oracle compatible kaadu. this is compatible.
SELECT LOAN.LOAN_NUM, LOAN.LOAN_SOURCE_CDE, LOAN.AMORT_TERM_MONTHS_QTY,
LOAN.ARM_MARGIN_PCT, LOAN.ARM_NEXT_CHANGE_DTE, LOAN.BACK_RATIO_PCT,
LOAN.BALLOON_FLG, LOAN.BASE_LOAN_AMT, LOAN.COMB_LOAN_TO_VAL_PCT,
LOAN.FIRST_PAYMT_DTE, LOAN.GL_STAT_CDE, LOAN.GL_STAT_DTE, LOAN.LIEN_POS_TXT,
LOAN.LOAN_INT_ONLY_FLG, LOAN.LOAN_MATURITY_DTE, LOAN.LOAN_ORIG_AMT,
LOAN.LOAN_PURPOSE_TXT, LOAN.LOAN_TO_VALUE_PCT, LOAN.NOTE_RATE_PCT, LOAN.PRODUCT_NUM,
LOAN.SERVICING_NUM, LOAN.PURCHASE_DTE, LOAN.ORIGINATOR_NME, LOAN.BS_CLASS,
LOAN.MORTGAGE_TYPE, PRIC.HE_TEASER_EXPIRE_DATE, PROP.PROP_STATE_ADR
FROM ADVPNT.GL_LOAN_MAIN LOAN,
ADVPNT.GL_LOAN_PRICING PRIC,
ADVPNT.GL_LOAN_PROPERTY PROP,
(SELECT LOAN_NUM, MAX(LOAN2.GL_STAT_DTE) MAX_LOAN_STAT_DTE
FROM ADVPNT.GL_LOAN_MAIN LOAN2
group by 1) DT
WHERE UPPER(LOAN.GL_STAT_CDE) IN ('FUNDED','CERTIFIED',
'CUSTODIAN','DELIVERY',
'SCHEDULED','BROKER LOAN FUNDED','SOLD' )
AND LOAN.PURCHASE_DTE BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND TRUNC (SYSDATE, 'MM') -1
AND LOAN.GL_STATE_DTE = DT.MAX_LOAN_STAT_DTE
AND LOAN.LOAN_NUM = DT.LOAN_NUM
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE |
Ramu
Yavvanam Kaatesina Bewarse
Username: Ramu
Post Number: 4899
Registered: 03-2004
Posted From: 199.244.214.30
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, July 31, 2007 - 8:51 am: | |
nEnu thread title cUsi hEmant EsADEmO anukunnA  .
Minneapolis sOdarA,
intaki idi E database? manaki migatA databases gurinci idea lEdu kAnee, Oracle lO aitE, explain plan utility ni use ceyyi. it will give you the way it queries the database and based on the results you can fine tune it.
intakee nee query lO indexed columns unnAyA?
rAmu(Du) manci bAluDu  cinnappaTinuncI intE |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 979
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:40 pm: | |
derived tables anedhi SQL kindha vastundhaa leka PL/SQL aa..nuvvu icchina intial query run avvadam ledhu actually. nenu run chesthunna original query that u gave idhenaa.. highlight chesthunna chudu error ekkada chupistundho in bold..
00928: missing select keyword..
SELECT LOAN.LOAN_NUM, LOAN.LOAN_SOURCE_CDE, LOAN.AMORT_TERM_MONTHS_QTY,
LOAN.ARM_MARGIN_PCT, LOAN.ARM_NEXT_CHANGE_DTE, LOAN.BACK_RATIO_PCT,
LOAN.BALLOON_FLG, LOAN.BASE_LOAN_AMT, LOAN.COMB_LOAN_TO_VAL_PCT,
LOAN.FIRST_PAYMT_DTE, LOAN.GL_STAT_CDE, LOAN.GL_STAT_DTE, LOAN.LIEN_POS_TXT,
LOAN.LOAN_INT_ONLY_FLG, LOAN.LOAN_MATURITY_DTE, LOAN.LOAN_ORIG_AMT,
LOAN.LOAN_PURPOSE_TXT, LOAN.LOAN_TO_VALUE_PCT, LOAN.NOTE_RATE_PCT, LOAN.PRODUCT_NUM,
LOAN.SERVICING_NUM, LOAN.PURCHASE_DTE, LOAN.ORIGINATOR_NME, LOAN.BS_CLASS,
LOAN.MORTGAGE_TYPE, PRIC.HE_TEASER_EXPIRE_DATE, PROP.PROP_STATE_ADR
FROM ADVPNT.GL_LOAN_MAIN LOAN,
ADVPNT.GL_LOAN_PRICING PRIC,
ADVPNT.GL_LOAN_PROPERTY PROP,
(SELECT LOAN_NUM, MAX(LOAN2.GL_STAT_DTE)
FROM ADVPNT.GL_LOAN_MAIN LOAN2}
group by 1) DT (LOAN_NUM, MAX_LOAN_STAT_DTE)
WHERE UPPER(LOAN.GL_STAT_CDE) IN ('FUNDED','CERTIFIED',
'CUSTODIAN','DELIVERY',
'SCHEDULED','BROKER LOAN FUNDED','SOLD' )
AND LOAN.PURCHASE_DTE BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND TRUNC (SYSDATE, 'MM') -1
AND LOAN.GL_STATE_DTE = DT.MAX_LOAN_STAT_DTE
AND LOAN.LOAN_NUM = DT.LOAN_NUM
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 977
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:23 pm: | |
Oops..i think initial ga cheppindhi result display cheyyadam kaadhu. case avasram ledhu anukuntaa..
actually i want a filter condition on the loan_source_code based on the count of that particular loan_num.
If the count is 1 then i want the records with loan_source_code = 'originated' only and it the loan_num count is 2 i want the the records with loan_source_code = 'repurchase' only.. |
Nine
Pilla Bewarse
Username: Nine
Post Number: 159
Registered: 04-2005
Posted From: 144.42.9.173
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:12 pm: | |
i donno oracle syntax.. it was long time back i worked. write syntax in the below lines..it will work. aa LOAN_NUM ki count extra field ga add chesi.. adi main SELECT list lo validate chesi.. use chesukontavu. |
Nine
Pilla Bewarse
Username: Nine
Post Number: 158
Registered: 04-2005
Posted From: 144.42.9.173
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:11 pm: | |
sub query lo COUNT(*) add chesi.. aa column ni LOAN_COUNT ga derived table lo declare cheyyi. SELECT list lo aa LOAN_COUNT ni case expression tho validate chesi.. neeku kavalsina vidham ga result display chesuko.
SELECT
CASE WHEN DT.LOAN_COUNT = 1 THEN loan_source_code = 'originated'
WHEN DT.LOAN_COUNT = 2 THEN loan_source code = 'repurchase'
END
FROM GL_LOAN_MAIN LOAN,
GL_LOAN_PRICING PRIC,
GL_LOAN_PROPERTY PROP,
/* added Derived table */
(SELECT LOAN_NUM, COUNT(*), MAX(LOAN2.GL_STAT_DTE)
FROM ADVPNT.GL_LOAN_MAIN
group by 1) DT (LOAN_NUM, LOAN_COUNT MAX_LOAN_STAT_DTE)
/* added Derived table */
WHERE LOAN.GL_STAT_CDE IN (UPPER('FUNDED'),UPPER('CERTIFIED'),
UPPER('CUSTODI AN'),UPPER('DELIVERY'),
UPPER('SCHEDULED'),UPPER('BROKER LOAN FUNDED'),UPPER('SOLD')
)
AND LOAN.PURCHASE_DTE BETWEEN <1st calendar day of the month> AND
<last calendar day of the month>
/* comment added the new condition */
AND LOAN.GL_STATE_DTE = DT.MAX_LOAN_STAT_DTE
AND LOAN.LOAN_NUM = DT.LOAN_NUM
/* comment added the new condition */
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 976
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:07 pm: | |
Oracle |
Nine
Pilla Bewarse
Username: Nine
Post Number: 157
Registered: 04-2005
Posted From: 144.42.9.173
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:06 pm: | |
cheyyochu mama. nee database enti oracle/MS SQL server?
i will post SQL in next post. |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 974
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 4:03 pm: | |
Nine mama - inko doubt.
Idhe query lo some kind of sub query add cheyyali..any thoughts..
for each loan_num that has only 1 record we want the loan_source_code = 'originated' and if the count of loan_num =2 we want the loan_source code = repurchase.
any thoughts on this logic.. |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 973
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Monday, July 30, 2007 - 3:59 pm: | |
Bump for nine mama..nuvvu monna icchina DT query run avvadam ledhu mama.. SQL query not ended ani edho message istundhi..so for now aa paatha corelated query ne vaduthunna.. |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 971
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:10 pm: | |
Nine mama,
Inka changes chesthunna meeru chebuthunnavi. thanks for the help. kakapothe inka run cheyyadam avvadam ledhu..will let u know if i need more help..
my27email at yahoo.com |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9010
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:10 pm: | |
SOK mayya correct already upper lo vundi anduke itu vaipu strings upper lo vunnayi.. asala akkada just column name in (strings isthey chalu)
problem adi kadule mayya yevo indexes use avvatam ledu.. query bagane edichindi |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9009
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:09 pm: | |
yemi wrong vundi mayya... ayana ichhina query ne kada sub query position marchanu |
Symbol_of_king
Pilla Bewarse
Username: Symbol_of_king
Post Number: 541
Registered: 12-2005
Posted From: 198.182.163.125
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:07 pm: | |
<<<UPPER(LOAN.GL_STAT_CDE) IN ('FUND'....) cheyyi.>>>
edi avasaram ledu emo chudu...LOAN.GL_STAT_CDE already upper lo vundi kabatte kada original sql loo UPPER('FUND') ani use chesindi... |
Nine
Pilla Bewarse
Username: Nine
Post Number: 111
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:07 pm: | |
nayak... ekkada copy chesi paste chesav. wrong vundi chudu. |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9008
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:06 pm: | |
SELECT ......
FROM GL_LOAN_MAIN LOAN,
GL_LOAN_PRICING PRIC,
GL_LOAN_PROPERTY PROP
WHERE LOAN.GL_STAT_CDE IN (UPPER('FUNDED'),UPPER('CERTIFIED'),
UPPER('CUSTODI AN'),UPPER('DELIVERY'),
UPPER('SCHEDULED'),UPPER('BROKER LOAN FUNDED'),UPPER('SOLD')
)
AND LOAN.PURCHASE_DTE BETWEEN <1st calendar day of the month> AND
<last calendar day of the month>
/* comment added the new condition */
AND LOAN.GL_STATE_DTE =
(SELECT MAX(LOAN2.GL_STAT_DTE)
FROM ADVPNT.GL_LOAN_MAIN WHERE LOAN_NUM = LOAN.LOAN_NUM
group by 1))
/* comment added the new condition */
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE |
Nine
Pilla Bewarse
Username: Nine
Post Number: 110
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:06 pm: | |
MN naa first post lo SQL artham ayyinda? are u able to change it let me know. nenu going.. nee mail ID post neeku help kaavalante email lo response ista. inka emanna doubts vunte SQL gurinchi adugu cheptanu.
bye. |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9007
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:04 pm: | |
SELECT ......
FROM GL_LOAN_MAIN LOAN,
GL_LOAN_PRICING PRIC,
GL_LOAN_PROPERTY PROP,
WHERE LOAN.GL_STAT_CDE IN (UPPER('FUNDED'),UPPER('CERTIFIED'),
UPPER('CUSTODI AN'),UPPER('DELIVERY'),
UPPER('SCHEDULED'),UPPER('BROKER LOAN FUNDED'),UPPER('SOLD')
)
AND LOAN.PURCHASE_DTE BETWEEN <1st calendar day of the month> AND
<last calendar day of the month>
/* comment added the new condition */
AND LOAN.GL_STATE_DTE =
(SELECT MAX(LOAN2.GL_STAT_DTE)
FROM ADVPNT.GL_LOAN_MAIN WHERE LOAN_NUM = LOAN.LOAN_NUM
group by 1))
/* comment added the new condition */
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE
try thisss |
Symbol_of_king
Pilla Bewarse
Username: Symbol_of_king
Post Number: 540
Registered: 12-2005
Posted From: 198.182.163.125
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:04 pm: | |
neeku thread valla nenu nerchukundi enti antee....
corelated subquery vuntee kudirithee complex query loki maaristhee effeciency vuntundi  |
Nine
Pilla Bewarse
Username: Nine
Post Number: 109
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:02 pm: | |
LOAN.GL_STAT_CDE IN (UPPER('FUNDED'),UPPER('CERTIFIED'),UPPER('CUSTODI AN'),UPPER('DELIVERY'), UPPER('SCHEDULED'),UPPER('BROKER LOAN FUNDED'),UPPER('SOLD') )
deenni simple ga ila transform cheyyi.
UPPER(LOAN.GL_STAT_CDE) IN ('FUND'....) cheyyi. |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9006
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:01 pm: | |
adi sub query nele.. nenu aa join chudaledu |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9005
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:00 pm: | |
mayya aa UPPER enduku lopala strings upper case lone vunnayi gada confusing ga vunnadi |
Nine
Pilla Bewarse
Username: Nine
Post Number: 108
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:00 pm: | |
MN ichhindi correlated sub query kadu nenu sure kikiki danni correlated sub query kindamarchandi.. index vunnayemo check chesukondi explain plan yeto chusi analyze cheyyandi
==
aa sub query is just like any other table... that is not corelated sub query...
====
AND LOAN.GL_STATE_DTE =
(SELECT MAX(LOAN2.GL_STAT_DTE) FROM ADVPNT.GL_LOAN_MAIN LOAN2 WHERE LOAN2.LOAN_NUM = LOAN.LOAN_NUM)
Sub query lo OUTER LEVEL query column ni refer cheste adi correlated sub query avutundi. endukante prathi LOAN.LOAN_NUM ki.. sub query result ni join cheyyali.
idi database standard Nayaka. |
Symbol_of_king
Pilla Bewarse
Username: Symbol_of_king
Post Number: 539
Registered: 12-2005
Posted From: 198.182.163.125
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 4:00 pm: | |
corelated ee anukunta kada nayakudu mama...enduku antaa sub query lo malli Loan table use chestunnavu kada |
Gochi
Bewarse Legend
Username: Gochi
Post Number: 43528
Registered: 07-2004
Posted From: 162.136.192.1
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:58 pm: | |
UPPER anedhi teesesthe emayina performance improve avutundhaa..
suregaa avuthundi 
school kelli sadavaledu 123....ninnu choosi nerchukunna 143  |
Nine
Pilla Bewarse
Username: Nine
Post Number: 107
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:58 pm: | |
UPPER anedhi teesesthe emayina performance improve avutundhaa..
effect vuntundi.. better teeseyyi if you are sure.. no records are lost. |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9004
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:58 pm: | |
aa sub query is just like any other table... that is not corelated sub query... |
Nine
Pilla Bewarse
Username: Nine
Post Number: 106
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:57 pm: | |
Nine mama can u let me know how can it be written using derived tables?
nenu rasi paste chesanu chudu mama.. C language comments pettanu chudu.. aa basis meeda nee orig query modify chesi run cheyyi. let me know if u need help. |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 970
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:57 pm: | |
UPPER anedhi teesesthe emayina performance improve avutundhaa..
Nine mama still trying to run the query given by u using derived tables.. |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9003
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:56 pm: | |
mayya adi correlated kadu mayya correlated ga marchi stha vundu |
Symbol_of_king
Pilla Bewarse
Username: Symbol_of_king
Post Number: 538
Registered: 12-2005
Posted From: 198.182.163.125
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:55 pm: | |
NINE mama ichindi baga efficient then previous anukuntaa...enduku antee...corelated subquery valla for every row corelated subquery execute avuthundi(adi malli inko join statement)....
kani NINe mama ichinda dani valla a subquery lo vundee join okkasare jaruguthundi for overall query execution |
Nayakudu
Mudiripoyina Bewarse
Username: Nayakudu
Post Number: 9002
Registered: 05-2005
Posted From: 148.107.10.20
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:55 pm: | |
MN ichhindi correlated sub query kadu nenu sure kikiki danni correlated sub query kindamarchandi.. index vunnayemo check chesukondi explain plan yeto chusi analyze cheyyandi |
Gochi
Bewarse Legend
Username: Gochi
Post Number: 43527
Registered: 07-2004
Posted From: 162.136.192.1
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:54 pm: | |
 Nine maama raasindhi bettergaa perform chesthundi gwaranteega compared to the previous one 
school kelli sadavaledu 123....ninnu choosi nerchukunna 143 |
Symbol_of_king
Pilla Bewarse
Username: Symbol_of_king
Post Number: 537
Registered: 12-2005
Posted From: 198.182.163.125
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:53 pm: | |
corelated sub query ni overall gaa complex query gaa marchavu anthee gaa NINE mama |
Gochi
Bewarse Legend
Username: Gochi
Post Number: 43526
Registered: 07-2004
Posted From: 162.136.192.1
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:53 pm: | |
minneapolis maama...inkonchem details kaavali
what is the purpose of subquery in here...is it that all other records are based on this latest date (or) is it to just pull the max(date) in ur output
school kelli sadavaledu 123....ninnu choosi nerchukunna 143 |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 969
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:52 pm: | |
yep co related query ne mama adhi.
Derived tables ante knowlege ledhu assalu. kaani cheyyochu anukunta..\
Nine mama can u let me know how can it be written using derived tables? |
Nine
Pilla Bewarse
Username: Nine
Post Number: 104
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:51 pm: | |
intha kantee easy gaa raadu anukunta query...sub query kuda corelated sub query kada inka emi marchalemu emoooo
nenu ichina SQL .. equalent result produce chestundi. i am sure about it. |
Atidi
Pilla Bewarse
Username: Atidi
Post Number: 16
Registered: 06-2007
Posted From: 148.87.1.172
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:50 pm: | |
hemu gaa rara
vacchi edho allu rasindhe malla paste sesi try this and buildup evvara |
Nine
Pilla Bewarse
Username: Nine
Post Number: 103
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:50 pm: | |
ante oka query reult ni inko table create sesi etti daanitho Joining seyyadama?
yeah. atlantide.
ilanti correlated condiations (Loan2.loan_num = loan.loan_num) vunte.. joining logic different ga work avutundi. like for every outer table loan_num.. the subquery is evaluated. |
Symbol_of_king
Pilla Bewarse
Username: Symbol_of_king
Post Number: 536
Registered: 12-2005
Posted From: 198.182.163.125
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:49 pm: | |
intha kantee easy gaa raadu anukunta query...sub query kuda corelated sub query kada inka emi marchalemu emoooo |
Nine
Pilla Bewarse
Username: Nine
Post Number: 102
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:48 pm: | |
SELECT ......
FROM GL_LOAN_MAIN LOAN,
GL_LOAN_PRICING PRIC,
GL_LOAN_PROPERTY PROP,
/* added Derived table */
(SELECT LOAN_NUM, MAX(LOAN2.GL_STAT_DTE)
FROM ADVPNT.GL_LOAN_MAIN
group by 1) DT (LOAN_NUM, MAX_LOAN_STAT_DTE)
/* added Derived table */
WHERE LOAN.GL_STAT_CDE IN (UPPER('FUNDED'),UPPER('CERTIFIED'),
UPPER('CUSTODI AN'),UPPER('DELIVERY'),
UPPER('SCHEDULED'),UPPER('BROKER LOAN FUNDED'),UPPER('SOLD')
)
AND LOAN.PURCHASE_DTE BETWEEN <1st calendar day of the month> AND
<last calendar day of the month>
/* comment added the new condition */
AND LOAN.GL_STATE_DTE = DT.MAX_LOAN_STAT_DTE
AND LOAN.LOAN_NUM = DT.LOAN_NUM
/* comment added the new condition */
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE
try this and let me know. |
Bignole
Mudiripoyina Bewarse
Username: Bignole
Post Number: 6152
Registered: 03-2004
Posted From: 161.225.1.12
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:45 pm: | |
ante oka query reult ni inko table create sesi etti daanitho Joining seyyadama?
Kathulatho Kaadhu raa Kanti choopu tho champestha |
Nine
Pilla Bewarse
Username: Nine
Post Number: 101
Registered: 04-2005
Posted From: 144.42.9.186
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:43 pm: | |
adi sub query kaadu.. correlated sub query. it is not that simple to spilit it.
derived table use chesi vrayochu. derived tables OK na? |
Bignole
Mudiripoyina Bewarse
Username: Bignole
Post Number: 6150
Registered: 03-2004
Posted From: 161.225.1.12
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:42 pm: | |
Kathulatho Kaadhu raa Kanti choopu tho champestha |
Atidi
Pilla Bewarse
Username: Atidi
Post Number: 14
Registered: 06-2007
Posted From: 148.87.1.172
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:41 pm: | |
hemu gaadu ROM vuntaadu gaani neeku help seyyadu soodu
endukantee aadiki ________ |
Bignole
Mudiripoyina Bewarse
Username: Bignole
Post Number: 6148
Registered: 03-2004
Posted From: 161.225.1.12
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:40 pm: | |
conds sana vunnayee anduke performance eppect avuthandemo. lekapothe oka view sesi daanitho join seyyakapoyava attage alias lu vadu tables ki sadavataki veasy ga vuntadhi
Kathulatho Kaadhu raa Kanti choopu tho champestha |
Bignole
Mudiripoyina Bewarse
Username: Bignole
Post Number: 6147
Registered: 03-2004
Posted From: 161.225.1.12
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:38 pm: | |
adhee naa vuddesam subqueries teeseyyataniki kuduruddemo ani
Kathulatho Kaadhu raa Kanti choopu tho champestha |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 968
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:36 pm: | |
joins already vunnayi kadha mama.
worried more about the sub query in this query.. |
Bignole
Mudiripoyina Bewarse
Username: Bignole
Post Number: 6140
Registered: 03-2004
Posted From: 161.225.1.12
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:33 pm: | |
manam antha sql pisthalam kaadhu kani Join lu vadochemo soodu
Kathulatho Kaadhu raa Kanti choopu tho champestha |
Minneapolis
Pilla Bewarse
Username: Minneapolis
Post Number: 967
Registered: 04-2005
Posted From: 209.180.28.6
Rating:N/A
Votes: 0(Vote!) | | Posted on Friday, July 27, 2007 - 3:31 pm: | |
SELECT ...... FROM GL_LOAN_MAIN LOAN, GL_LOAN_PRICING PRIC, GL_LOAN_PROPERTY PROP
WHERE LOAN.GL_STAT_CDE IN (UPPER('FUNDED'),UPPER('CERTIFIED'),UPPER('CUSTODI AN'),UPPER('DELIVERY'), UPPER('SCHEDULED'),UPPER('BROKER LOAN FUNDED'),UPPER('SOLD') )
AND LOAN.PURCHASE_DTE BETWEEN <1st calendar day of the month> AND <last calendar day of the month>
AND LOAN.GL_STATE_DTE =
(SELECT MAX(LOAN2.GL_STAT_DTE) FROM ADVPNT.GL_LOAN_MAIN LOAN2 WHERE LOAN2.LOAN_NUM = LOAN.LOAN_NUM)
AND LOAN.LOAN_NUM = PRIC.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PRIC.LOAN_SOURCE_CDE
AND LOAN.LOAN_NUM = PROP.LOAN_NUM
AND LOAN.LOAN_SOURCE_CDE = PROP.LOAN_SOURCE_CDE
can someone tell me if this query can be written in a better way to run quicker and efficiently.. |
