I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate OrderId Subtotal CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL") AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
AREA (area_code, area_name) MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)
I want to display the output of top 3 sales columnwise as below.
AREA_NAME Top 1st (ISBN) Top 1st (BOOK_NAME) Top 1st (QTY) Top 2nd (ISBN) Top 2nd (BOOK_NAME) Top 2nd (QTY) Top 3rd (ISBN) Top 3rd (BOOK_NAME) Top 3rd (QTY)
Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages. So another words one sale entity that has both products with messages and at lease one with no message. Something like this
Sales 1 ID 123 Product1 ID 1234 for sale ID 123 Product2 ID 4321 for sale ID 123 Sales_message ID 098 for product ID 1234 'blabla' Sales_message ID 876 for product ID 1234 'albalb' AND NO MESSAGES FOR PRODUCT 4321
Required to generate report to total sale dollar amount by combining information from T1 and T2 (matching with item_no). Rules are a)Formula is Sales_Number*Sale_price b)Choose the earliest sale record to latest by Sales_Date with the sum of Sales_Number is less or equal the Total_Sales_Number from T1. c)If the sum of Sales_Number is less than the Total_Sales_Number from T1, then the use a fixed price for all items, say $10 d)If a item is not in T2 then use $10 for the price
For example, item_no 1: Total sale dollar amount = 20*$15+30*$15+(60-20-30)*$10
item_no 2: Total sale dollar amount = 40*$16+(50-40)*$18 item_no 3: Total sale dollar amount = 5*$20 item_no 4: Total sale dollar amount = 20*$10
The current logic is done by using cursor. Can this be done without cursor using such as analytical statements? The Oracle DB is 9i.
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
I have Microsoft finger print reader and installed. I would like this that this device will capture finger prints from form application.i have installed fingerprints software and then oracle. The device is active on sdk. when i use form the device shown nothing. How i can interact with oracle and device.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; BEGIN JOBSFILE := UTL_FILE.FOPEN('FILES','JOBS.TXT','W'); UTL_FILE.PUT_LINE(JOBSFILE,'Action = insert'); FOR REC IN JOBSCUR [code]....
is there anyway to print the values of full table .i have used all column names to print .
I'm trying to create an output statement that will show that the update was successful, but everything that I'm trying doesn't seem to be working.
I have a procedure and an anonymous block that calls on the procedure.
So far after the begin in my anonymous block I have.
BEGIN procedure_name(variable1, variable2); dbms_output.put_line('Successfully Updated'); END;
Now I know that the procedure and the anonymous block without the dbms output works as the update takes place in the table, but I can't get any text string output to confirm this.
I've 2 identical tables of 300+ columns each,and each of table is having pk as combination of 2 fields.There are some fields where data is not matching. need a query which will use these two tables and return the following-
How can i print serial no of records in sql query? I know I can achieve this with selecting rownum but it gives wrong data if used with group by like below
select rownum,deptid from emp group by rownum,deptid order by rwnum
I am using oracle forms10g here i have next button. So when i click the next button it will show the total volumn of record count and each record value for example 1/10 like that.
I am creating report a using emp table .There are 14 rows in emp table , i want to print output into two pages . If i set FILTER TYPE Property to 5 ,it only shows 5 records .
I want to print data from a clob in XML format and use the following
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is xmlstr varchar2(32767); line varchar2(32767); cnt NUMBER;
[Code]...
However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?
I have my own design employee time attendance application which i design in oracle forms 6i and oracle database 9i. Now I bought one USB fingerprint reader so I can attached this device to my application. I want every employee to scan there finger in my reader and I will record there attendance. How I can configure this device in my form.