Author |
Message |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16652 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Friday, September 16, 2011 - 12:20 pm: |
|
Pokiri1:hemu report pani chestunda
super ga annai...vere additional requirements icharu..so vaati meeda working..konchem complex ga undi..but ee thread lo vesindi matuku work avuthundi naku theledu ala paiki laagali vere group chesthaniki.. |
   
Pokiri1
Bewarse Legend Username: Pokiri1
Post Number: 17801 Registered: 03-2006 Posted From: 192.35.35.35
Rating: N/A Votes: 0 (Vote!) | Posted on Friday, September 16, 2011 - 12:01 pm: |
|
hemu report pani chestunda ara nimashamulo sachevadivi nee address naku endhuku raa
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16603 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:14 pm: |
|
Stewie:naku custom tables levu kabatti avi teesi explain plan cheste bane undi cost ... ekkuva ledu 20min run avvadaniki nuvvu explain plan run chesi choodu org_ID edo okati petti.. full table access unte nee custom tables meeda oka index dev lo create chesi choodu..
ya..cost ekkuva ledu.fast gane run avuthundi..kani nenu 2 data models create chesthe slow ayipoyindi..pokiri1 mama cheppindi chesa..naku kavalsinattu vachindi and runs fast too.. thank you all malla emanna question unte adugutha.. |
   
Stewie
Kurra Bewarse Username: Stewie
Post Number: 604 Registered: 03-2011 Posted From: 63.138.168.7
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:12 pm: |
|
naku custom tables levu kabatti avi teesi explain plan cheste bane undi cost ... ekkuva ledu 20min run avvadaniki nuvvu explain plan run chesi choodu org_ID edo okati petti.. full table access unte nee custom tables meeda oka index dev lo create chesi choodu.. kothga petta signature ela undi
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16602 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:09 pm: |
|
Pokiri1:ala raasina taravatha data model lay out loki velli ORG_ID piki teesuku raa. appudu 2 groups vuntai. aa renditiki link pettu org_id ni
got it bhayya..kummesindi kiki..anukunnatte vachindi..ippudu multiple org vachela output chosi run chesi chostha..nuvvu keka bhayya.. |
   
Dobbey
Mudiripoyina Bewarse Username: Dobbey
Post Number: 2830 Registered: 03-2011 Posted From: 65.204.218.243
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:03 pm: |
|
okate group by pettochemo antunna rendunnay kadaaa Health is Happiness
|
   
Pokiri1
Bewarse Legend Username: Pokiri1
Post Number: 17782 Registered: 03-2006 Posted From: 192.91.173.42
Rating:  Votes: 1 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:03 pm: |
|
Ravanabrahma: Pokiri1: tappu raasanu emo ani delete chesa. org_id meeda petti chudu. data correct gaa vaste appudu org id group loki vere columns add chei. single query rasthe single group ga vasthundi mama data..
ala raasina taravatha data model lay out loki velli ORG_ID piki teesuku raa. appudu 2 groups vuntai. aa renditiki link pettu org_id ni ara nimashamulo sachevadivi nee address naku endhuku raa
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16601 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:03 pm: |
|
vere reports emanna unnayemo chostha ila chesinavi.. |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16600 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:01 pm: |
|
Dobbey:oka group by ettochu ha annai froper aliases pettiii
already group by undi mama chala columns tho.. |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16599 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 1:00 pm: |
|
requirement vachi data will be diaplayed in xls output mamalu.. so xls output lo header vachi org_id petti dani kinda aa org lo unna anni project details pettali. suppose for org_id 1 we have 5 rows of data.. then one report header for all the 5 rows if information with Org_id 1 and for org_id 2 new report header with that org data.. to acheive this I need to loop through by orgs..I can achive this if I get the data by org blocks. Q_org org_id 1 data1 data2 data3 data4 data5 org_id 2 data 6 data7 |
   
Dobbey
Mudiripoyina Bewarse Username: Dobbey
Post Number: 2829 Registered: 03-2011 Posted From: 65.204.218.243
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:56 pm: |
|
oka group by ettochu ha annai froper aliases pettiii Health is Happiness
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16598 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:55 pm: |
|
Pokiri1:tappu raasanu emo ani delete chesa. org_id meeda petti chudu. data correct gaa vaste appudu org id group loki vere columns add chei.
single query rasthe single group ga vasthundi mama data.. |
   
Pokiri1
Bewarse Legend Username: Pokiri1
Post Number: 17780 Registered: 03-2006 Posted From: 192.91.173.42
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:55 pm: |
|
stewie tammi cheppinattu union takes time.number columns kuda vunnai kada. use union all. ara nimashamulo sachevadivi nee address naku endhuku raa
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16597 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:54 pm: |
|
Stewie:avasarama ? union all try cheyyi .. koncham fast ga untadi
union all ee undi mama.. |
   
Pokiri1
Bewarse Legend Username: Pokiri1
Post Number: 17779 Registered: 03-2006 Posted From: 192.91.173.42
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:54 pm: |
|
tappu raasanu emo ani delete chesa. org_id meeda petti chudu. data correct gaa vaste appudu org id group loki vere columns add chei. ara nimashamulo sachevadivi nee address naku endhuku raa
|
   
Stewie
Kurra Bewarse Username: Stewie
Post Number: 603 Registered: 03-2011 Posted From: 63.138.168.7
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:54 pm: |
|
union avasarama ? union all try cheyyi .. koncham fast ga untadi kothga petta signature ela undi
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16596 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:52 pm: |
|
delete chesaventi mama post? |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16595 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:52 pm: |
|
Pokiri1:
singel data model..kani org_id paina undetattu..aagu mama chostha.. |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16594 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:48 pm: |
|
I linked using Inventory_Org for both the data models.. |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16593 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:48 pm: |
|
in my case.. first query SELECT ood.organization_code Inventory_Org FROM org_organization_definitions ood 2nd query SELECT ood.organization_code Inventory_Org, ood.organization_name Organization_Name, cii.serial_number Asset_Number, ppa.segment1 Project_Number, ppa.project_id project_id, ppa.description Project_Description, pt.task_number Task_Number, pt.description Task_Description, peia.expenditure_type Expenditure_Type, paa.date_placed_in_service In_Service_Date, a.PO_Requisition_Number PO_Requisition_Number, a.PO_Number PO_Number, a.authorization_status PO_Status, xxpo.child_wo Work_Order, SUM (pcdla.burdened_cost) Burdened_Cost, ppa.start_date Project_Start_Date, ppa.completion_date Project_Completion_Date, pt.start_date Task_Start_Date, pt.completion_date Task_Completion_Date, TO_CHAR (SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') Run_Time FROM csi_item_instances cii, pa_project_asset_assignments ppaa, pa_project_assets_all paa, pa_projects_all ppa, pa_tasks pt, mtl_system_items_b msib, org_organization_definitions ood, pa_expenditure_items_all peia, pa_expenditures_all pea, pa_cost_distribution_lines_all pcdla, XXPO_WO_AFE_TASK_CROSS_REF xxpo, (SELECT DISTINCT pha.segment1 PO_Number, pda.project_id, pda.task_id, prha.segment1 PO_Requisition_Number, pha.authorization_status FROM po_headers_all pha, po_distributions_all pda, po_req_distributions_all prda, po_requisition_lines_all prla, po_requisition_headers_all prha WHERE pha.po_header_id(+) = pda.po_header_id AND pda.req_distribution_id = prda.distribution_id(+) AND prda.requisition_line_id = prla.requisition_line_id(+) AND prla.requisition_header_id = prha.requisition_header_id(+) AND pha.segment1 = NVL (:PO_Num, pha.segment1) AND NVL (prha.segment1, 1) = NVL (:PO_Req_Num, NVL (prha.segment1, 1))) a WHERE ppaa.attribute7 = cii.serial_number AND ppaa.project_asset_id = paa.project_asset_id AND paa.project_id = ppa.project_id AND ppa.project_id = pt.project_id AND ppa.project_id = a.project_id(+) AND pt.task_id(+) = a.task_id AND ppaa.task_id = pt.task_id AND pt.task_id = peia.task_id(+) AND peia.expenditure_id = pea.expenditure_id(+) AND ppa.project_id = pcdla.project_id AND pt.task_id = pcdla.task_id AND pcdla.expenditure_item_id = peia.expenditure_item_id AND xxpo.project_number = ppa.segment1 AND xxpo.task_number = pt.task_number AND msib.organization_id = ood.organization_id AND cii.inventory_item_id = msib.inventory_item_id AND cii.last_vld_organization_id = msib.organization_id AND cii.inv_organization_id = msib.organization_id AND ppa.project_id = NVL (:From_Proj, ppa.project_id) AND pt.task_id = NVL (:From_Task, pt.task_id) AND cii.serial_number = NVL (:From_Asset, cii.serial_number) AND ood.organization_id = NVL ( rg, ood.organization_id) AND xxpo.child_wo = NVL (:Child_wo, xxpo.child_wo) GROUP BY ood.organization_code, ood.organization_name, cii.serial_number, ppa.segment1, ppa.project_id, ppa.description, pt.task_number, pt.description, peia.expenditure_type, paa.date_placed_in_service, a.PO_Number, a.PO_Requisition_Number, a.authorization_status, xxpo.parent_wo, xxpo.child_wo, ppa.start_date, ppa.completion_date, pt.start_date, pt.completion_date UNION ALL SELECT ood.organization_code Inventory_Org, ood.organization_name Organization_Name, cii.serial_number Asset_Number, ppa.segment1 Project_Number, ppa.project_id project_id, ppa.description Project_Description, pt.task_number Task_Number, pt.description Task_Description, peia.expenditure_type Expenditure_Type, paa.date_placed_in_service In_Service_Date, NULL, NULL, NULL, xxpo.child_wo Work_Order, SUM (pcdla.burdened_cost) Burdened_Cost, ppa.start_date Project_Start_Date, ppa.completion_date Project_Completion_Date, pt.start_date Task_Start_Date, pt.completion_date Task_Completion_Date, TO_CHAR (SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') Run_Time FROM csi_item_instances cii, pa_project_asset_assignments ppaa, pa_project_assets_all paa, pa_projects_all ppa, pa_tasks pt, mtl_system_items_b msib, org_organization_definitions ood, pa_expenditure_items_all peia, pa_expenditures_all pea, pa_cost_distribution_lines_all pcdla, XXPO_WO_AFE_TASK_CROSS_REF xxpo, (SELECT DISTINCT pha.segment1 PO_Number, pda.project_id, pda.task_id, prha.segment1 PO_Requisition_Number, pha.authorization_status FROM po_headers_all pha, po_distributions_all pda, po_req_distributions_all prda, po_requisition_lines_all prla, po_requisition_headers_all prha WHERE pha.po_header_id(+) = pda.po_header_id AND pda.req_distribution_id = prda.distribution_id(+) AND prda.requisition_line_id = prla.requisition_line_id(+) AND prla.requisition_header_id = prha.requisition_header_id(+)) a -- AND pha.segment1 = NVL (:PO_Num, pha.segment1) -- AND NVL (prha.segment1, 1) = -- NVL (:PO_Req_Num, NVL (prha.segment1, 1))) a WHERE ppaa.attribute7 = cii.serial_number AND ppaa.project_asset_id = paa.project_asset_id AND paa.project_id = ppa.project_id AND ppa.project_id = pt.project_id AND ppa.project_id = a.project_id(+) -- AND pt.task_id(+) = a.task_id AND a.PO_Number = NVL (:PO_Num, a.PO_Number) AND NVL (a.PO_Requisition_Number, 1) = NVL (:PO_Req_Num, NVL (a.PO_Requisition_Number, 1)) AND ppaa.task_id = pt.task_id AND pt.task_id = peia.task_id(+) AND peia.expenditure_id = pea.expenditure_id(+) AND ppa.project_id = pcdla.project_id AND pt.task_id = pcdla.task_id AND pcdla.expenditure_item_id = peia.expenditure_item_id AND xxpo.project_number = ppa.segment1 AND xxpo.task_number = pt.task_number AND msib.organization_id = ood.organization_id AND cii.inventory_item_id = msib.inventory_item_id AND cii.last_vld_organization_id = msib.organization_id AND cii.inv_organization_id = msib.organization_id AND ppa.project_id = NVL (:From_Proj, ppa.project_id) AND pt.task_id = NVL (:From_Task, pt.task_id) AND cii.serial_number = NVL (:From_Asset, cii.serial_number) AND ood.organization_id = NVL ( rg, ood.organization_id) AND xxpo.child_wo = NVL (:Child_wo, xxpo.child_wo) AND NOT EXISTS (SELECT 1 FROM po_distributions_all WHERE project_id = pt.project_id AND task_id = pt.task_id) GROUP BY ood.organization_code, ood.organization_name, cii.serial_number, ppa.segment1, ppa.project_id, ppa.description, pt.task_number, pt.description, peia.expenditure_type, paa.date_placed_in_service, NULL, NULL, NULL, xxpo.parent_wo, xxpo.child_wo, ppa.start_date, ppa.completion_date, pt.start_date, pt.completion_date |
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16592 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:46 pm: |
|
example lo ichindi entante.. 1st query SELECT ENAME, EMPNO FROM EMP WHERE JOB = 'SALESMAN' 2nd query SELECT REPID, PRODNAME, AMOUNT, CUSTNAME FROM SALES ORDER BY REPID, CUSTNAME now they linked two data models with the link using empid from 1st and repid in 2nd..so the final query will be.. SELECT ENAME, EMPNO, REPID, PRODNAME, AMOUNT, CUSTNAME FROM EMP, SALES WHERE JOB = 'SALESMAN' AND EMPNO = REPID (+) ORDER BY REPID, CUSTNAME |
   
Dobbey
Mudiripoyina Bewarse Username: Dobbey
Post Number: 2828 Registered: 03-2011 Posted From: 65.204.218.243
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:46 pm: |
|
annai aaa tables cheppu oka saaari Health is Happiness
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16591 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:43 pm: |
|
Dobbey:sql entha sepu run avuthundi annai
oka 20 mins run ayyindi..mamuluga ayithe 2 mins..so close chesesa wrong undi ani. |
   
Dobbey
Mudiripoyina Bewarse Username: Dobbey
Post Number: 2827 Registered: 03-2011 Posted From: 65.204.218.243
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:43 pm: |
|
sql time meede depend ayi untadi run time inka formula columns emanna unnayya unte aatilo emanna undaa code Health is Happiness
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16590 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:43 pm: |
|
Pokiri1:
thanks mama.. 1st query select org_id from organizations o 2nd query select org_id, data1, data2 from table1, table2, organizations o union select org_id, data1, data2 from table1, table3, organizations 0 ippudu data link tho link chesthe ela behave chesthundo ardam kavatam ledu..I mean how these 2 queries will be linked anedi.. |
   
Dobbey
Mudiripoyina Bewarse Username: Dobbey
Post Number: 2826 Registered: 03-2011 Posted From: 65.204.218.243
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:42 pm: |
|
sql entha sepu run avuthundi annai Health is Happiness
|
   
Pokiri1
Bewarse Legend Username: Pokiri1
Post Number: 17775 Registered: 03-2006 Posted From: 192.91.173.42
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:39 pm: |
|
hemu query raasi first sql lo run chei. ara nimashamulo sachevadivi nee address naku endhuku raa
|
   
Ravanabrahma
Bewarse Legend Username: Ravanabrahma
Post Number: 16585 Registered: 06-2004 Posted From: 98.151.191.253
Rating: N/A Votes: 0 (Vote!) | Posted on Wednesday, September 14, 2011 - 12:11 pm: |
|
2 query data model chesthunna.. report out lo oka group lo org id undi next data model aa org ki sambandinchina projects, assets data etc untadi..so <Q_ORG> 450 <Q_SUB_DATA> DATA1 DATA2 DATA3 \<Q_SUB_DATA> <Q_ORG> 451 <Q_SUB_DATA> DATA4 DATA5 DATA6 \<Q_SUB_DATA> ila annattu.. so 2 query data model rasanu 1st data model lo simple query to get the org_id from organizations table..2nd data model lo actual query that gets the data which also has the org_id now data link create chesa rendu data models ki using org_id.. kaani report chala sepu run avuthundi..edo issue unnattundi evaranna ila chesara? idi simple report anukunta. I used this link to develop the report. http://download.oracle.com/docs/html/B10602_01/orbr_grpl_multi.htm |
|