Basic Oracle Scripting SQL*Plus

2 New Member

I would like to collect usage data for various items in our stores bin and the script I'm trying to get to run looks something like this:
1 select min(doc_date),sum(qty_change)
2 from docl
3 where doc_date > '01-JAN-07'
4 and doc_type='STOCKISS'
5 and product_code = (select product_code from product_base
6 where product_category_code = 'BUILDING'
7* and doc_type='CONTROL')

The script is just reporting the following:

SQL> /

MIN(DOC_D SUM(QTY_CHANGE)
--------- ---------------

Can you please assist me?

Oct 4 '07 #1

Subscribe Reply

2287

debasisdas

8,127

Recognized Expert Expert

Question moved to Oracle forum.

Oct 4 '07 #2

amitpatel66

2,367

Recognized Expert Top Contributor

I would like to collect usage data for various items in our stores bin and the script I'm trying to get to run looks something like this:
1 select min(doc_date),sum(qty_change)
2 from docl
3 where doc_date > '01-JAN-07'
4 and doc_type='STOCKISS'
5 and product_code = (select product_code from product_base
6 where product_category_code = 'BUILDING'
7* and doc_type='CONTROL')

The script is just reporting the following:

SQL> /

MIN(DOC_D SUM(QTY_CHANGE)
--------- ---------------

Can you please assist me?

Could you please post your table data here? It seems you dont have the relevant data as per your WHERE conditions to show any output.

Oct 4 '07 #3

themajic

New Member

Could you please post your table data here? It seems you dont have the relevant data as per your WHERE conditions to show any output.

SQL> desc docl
Name Null? Type
------------------------------- -------- ----
C_ID NOT NULL NUMBER(12)
DOC_NO NOT NULL VARCHAR2(25)
DOC_DATE DATE
LINE_ID NOT NULL NUMBER(12)
PRODUCT_CODE VARCHAR2(25)
ACT_DUTY_COST NUMBER(19,4)
ACT_FOB_COST NUMBER(19,4)
ACT_SHIP_COST NUMBER(19,4)
ADJ_UNIT_COST NUMBER(19,4)
ADJ_DATE DATE
ANALYSIS_CODE_1 VARCHAR2(25)
ANALYSIS_CATEGORY_1 VARCHAR2(10)
ANALYSIS_CODE_2 VARCHAR2(25)
ANALYSIS_CATEGORY_2 VARCHAR2(10)
ANALYSIS_CODE_3 VARCHAR2(25)
ANALYSIS_CATEGORY_3 VARCHAR2(10)
BIN_LOCATION VARCHAR2(25)
DIM_UOM_CODE VARCHAR2(10)
DISC_1 NUMBER(4,2)
DISC_2 NUMBER(4,2)
DOC_TYPE VARCHAR2(10)
EXP_DUTY_COST NUMBER(19,4)
EXP_FOB_COST NUMBER(19,4)
EXP_SHIP_COST NUMBER(19,4)
ITEM_DIM_1 NUMBER(19,3)
ITEM_DIM_2 NUMBER(19,3)
ITEM_DIM_3 NUMBER(19,3)
LINE_NO NUMBER(12)
QTY_BACKORDERED NUMBER(14,2)
QTY_CHANGE NUMBER(14,2)
QTY_ORDERED NUMBER(14,2)
QTY_OUTSTANDING NUMBER(14,2)
QTY_REJECTED NUMBER(14,2)
QTY_SHIPPED NUMBER(14,2)
REC_DATE_ACTUAL DATE
REC_DATE_PROMISED DATE
REC_DATE_REQUESTED DATE
SERIAL_NO_1 VARCHAR2(25)
SERIAL_NO_2 VARCHAR2(25)
SERIAL_NO_3 VARCHAR2(25)
SHIP_DATE_ACTUAL DATE
SHIP_DATE_PROMISED DATE
SHIP_DATE_REQUESTED DATE
UNIT_COST NUMBER(19,4)
UNIT_PRICE NUMBER(19,3)
DOC_STATUS VARCHAR2(1)
DOC_ID NUMBER(12)
PRODUCT_DESCRIPTION VARCHAR2(80)
QTY_ALLOCATED NUMBER(14,2)
CROSS_REF_LINE_ID NUMBER(12)
TAX_CODE VARCHAR2(10)
CROSS_REF_DOC_ID NUMBER(12)
CONV_FACTOR NUMBER(19,4)
LINE_STATUS VARCHAR2(1)
NEW_AV_COST NUMBER(19,4)
LIST_PRICE NUMBER(19,3)
LIST_DISCOUNT NUMBER(4,2)
REC_LINE_ID NUMBER(12)
QTY_INVOICE NUMBER(14,2)
EXP_SURC_COST NUMBER(19,4)
ACT_SURC_COST NUMBER(19,4)
SUP_ACK_REF VARCHAR2(25)
SUP_ACK_DATE DATE
TOTAL_COST NUMBER(19,4)
TOTAL_PRICE NUMBER(19,3)
PURC_CONV_FACTOR NUMBER(12,6)
UOM_CODE VARCHAR2(10)
PICK_UOM VARCHAR2(10)
INVOICE_PRICE NUMBER(19,3)
QTY_PICK NUMBER(14,2)
ALT_UOM VARCHAR2(10)
QTY_ALT NUMBER(14,2)
INSURANCE_AMOUNT NUMBER(19,4)
TAX_RATE NUMBER(4,2)
UNIT_TAX NUMBER(19,3)
STOCKED_INDICATOR VARCHAR2(1)
INSP_REQ VARCHAR2(1)
INSP_STATUS VARCHAR2(1)
INSP_DATE DATE
REJ_REASON VARCHAR2(10)
RECLAIM_TAX VARCHAR2(1)
CRITICAL VARCHAR2(1)
SOURCE_CODE VARCHAR2(10)
UNIT_DISC NUMBER(19,4)
TYPE_CODE VARCHAR2(10)
DISCIPLINE VARCHAR2(10)
EQUIPMENT VARCHAR2(25)
STANDARD_COST NUMBER(19,4)

SQL> spool off

SQL> desc product_base
Name Null? Type
------------------------------- -------- ----
SHORT_NAME NOT NULL VARCHAR2(25)
ABC_ANALYSIS_CODE VARCHAR2(10)
ABC_ANALYSIS_CODE_DATE DATE
ACTIVITY_INDICATOR VARCHAR2(1)
AGENCY_CODE VARCHAR2(25)
ANALYSIS_CATEGORY_1 VARCHAR2(10)
ANALYSIS_CODE_1 VARCHAR2(25)
ANALYSIS_CATEGORY_2 VARCHAR2(10)
ANALYSIS_CODE_2 VARCHAR2(25)
ANALYSIS_CATEGORY_3 VARCHAR2(10)
ANALYSIS_CODE_3 VARCHAR2(25)
AVERAGE_COST NUMBER(19,4)
BALANCE_FWD_DATE DATE
BALANCE_FWD_QTY NUMBER(14,2)
CONTROLLED_INDICATOR VARCHAR2(1)
C_ID NOT NULL NUMBER(12)
CYCLE_COUNT_CODE VARCHAR2(10)
DATE_CREATED DATE
DATE_LAST_COUNTED DATE
DATE_LAST_ISSUE DATE
DATE_LAST_RECEIPT DATE
DATE_LAST_SALE DATE
DATE_LAST_TRANSFER DATE
DEFAULT_BIN_LOCATION VARCHAR2(25)
DIMENSION_UOM_CODE VARCHAR2(25)
LAST_COST NUMBER(19,4)
MINIMUM_MARGIN NUMBER(5,2)
PRODUCT_CATEGORY_CODE NOT NULL VARCHAR2(25)
PRODUCT_CODE NOT NULL VARCHAR2(25)
PRODUCT_DESCRIPTION NOT NULL VARCHAR2(80)
QTY_ALLOCATED NUMBER(14,2)
QTY_ON_BACKORDER_SUPPLIER NUMBER(14,2)
QTY_ON_HAND NUMBER(14,2)
QTY_ON_ORDER NUMBER(14,2)
STANDARD_COST NUMBER(19,4)
STOCKED_INDICATOR VARCHAR2(1)
STOCKED_UOM_CODE VARCHAR2(10)
SUPERSEDED_BY_PRODUCT VARCHAR2(30)
SUPERSESSION_DATE DATE
SUPERSEDED_PRODUCT VARCHAR2(30)
TARIFF_CODE VARCHAR2(25)
TAX_CODE VARCHAR2(10)
UNIT_DIM_1 NUMBER(19,3)
UNIT_DIM_2 NUMBER(19,3)
UNIT_DIM_3 NUMBER(19,3)
UNIT_WEIGHT NUMBER(19,3)
WEIGHT_UOM_CODE VARCHAR2(10)
WRITE_DOWN_INDICATOR VARCHAR2(1)
PRICE NUMBER(19,4)
PRODUCT_IND VARCHAR2(1)
DIVISION VARCHAR2(25)
SALES_CONV_FACTOR NUMBER(12,6)
SALES_UOM_CODE VARCHAR2(25)
FUTURE_PRICE NUMBER(19,3)
MTD_USAGE NUMBER(14,2)
MTD_VALUE NUMBER(19,4)
MARKUP_PCT NUMBER(5,2)
GROSS_WEIGHT NUMBER(19,3)
ENT_AV_IND VARCHAR2(1)

SQL> spool off

These are the descriptions of the tables I am trying to extract information from,
does it help?

Oct 4 '07 #4

amitpatel66

2,367

Recognized Expert Top Contributor

I would like to collect usage data for various items in our stores bin and the script I'm trying to get to run looks something like this:
1 select min(doc_date),sum(qty_change)
2 from docl
3 where doc_date > '01-JAN-07'
4 and doc_type='STOCKISS'
5 and product_code = (select product_code from product_base
6 where product_category_code = 'BUILDING'
7* and doc_type='CONTROL')

The script is just reporting the following:

SQL> /

MIN(DOC_D SUM(QTY_CHANGE)
--------- ---------------

Can you please assist me?

I requested you to post the data.Anyways please do the following:

1. Check if you have product code for product_category_code = 'BUILDING'
and doc_type='CONTROL'
2. If point 1 is Yes, then for those product code, check if you have data for doc_date > '01-JAN-07' and doc_type='STOCKISS'

If point 1 and 2 satisfies then the query will return you the MIN ans SUM values.

Oct 4 '07 #5

Similar topics

2263

Oracle->not same result from php and from sql*plus

by: Thierry B. | last post by:

Hi, Here is my query, ran from oracle sql*plus: SELECT mrbs_room.id, start_time, end_time, name, mrbs_entry.id, type FROM mrbs_entry, mrbs_room WHERE mrbs_entry.room_id = mrbs_room.id AND area_id = 101 AND start_time <= 1076068800 AND end_time > 1076065200

PHP

3804

Oracle, PHP5, Apache2, oci_connect fails but sql*plus works

by: Ruben Schoenefeld | last post by:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi - after I got sql*plus to work on my Linux box and I recompiled PHP 5 to include the oracle instant client, I run into a weird problem: I get 'ORA-01017: invalid username/password; logon denied' when using the same combination of username/password and Oracle instance that works in...

PHP

4938

Strange behavious - inconsistencies between SQL in SQL*PLUS & SQL embedded in PL/SQL

by: Ethel Aardvark | last post by:

I have a query which runs fine in SQL*Plus but which will not compile into a packaged procedure (claiming that the table can not be found): SELECT DISTINCT Folder_ID INTO l_RootID -- remove this line in SQL*Plus context FROM ifssys.ifs_folder_items WHERE Folder_Name = 'Root Folder' AND TYPE = 'FOLDER'; -> PL/SQL: ORA-00942:...

Oracle Database

1995

Passing a space as an argument to SQL*Plus

by: GP | last post by:

Consider the following: (extracted from a .bat) sqlplus toto/titi ^ @%pdl%\islqz033 ^ '1 ' ^ 'aaa' ^ ' ' ^ 'zzz' ^ ....

Oracle Database

20045

SQL*Plus Parameters

by: Peter | last post by:

Has anyone seen this before? I start SQL*Plus, and login by typing sqlplus Quantum/Password@BPrd I type: select '&1' from dual; it responds

Oracle Database

4302

Openning/Shutting down Oracle database from the Client's SQL*Plus

by: Miori | last post by:

Dear all, Server machine running Oracle Database Server on Linux and a Client machine running Oracle Client on WIndows XP. HOw it is possible to shut down/start up the Oracle database on the server from SQL*Plus of the client. I know it can be done from SQL*Plus of the server but the point is that I want to do it from SQL*Plus of the...

Oracle Database

1582

Sql* Plus

by: nkechifesie | last post by:

Why cant I log on to Sql* plus using Sys and the password. It allows me to log on with System , then i tghen have to connect using Sys why?

Oracle Database

4206

I am unable to login SQL plus 8.0 in Developer 2000

by: coolsub82 | last post by:

Hi Everybody! I have installed developer 2000 on my system. During installation, i choose complete as an option. During installation, there was no step to configure the user id and password. Now I am unable to login to SQL plus 8.0 as it is not accepting any user id or password including the default one (the one I knew), i.e., id : scott ;; pwd...

Oracle Database

2329

How To See The Procedure Script In Sql Plus

by: vivek bansal | last post by:

I ve a created a procedure in my database. The script is on my pc. suppose you are also connected to the same database. you know the name of the procedure. how wil you see the script of that procedure in sql+? how wil you see the whole code of the procedure in sql plus?

Oracle Database

2500

DB2 equivalent for ORACLE SQL*Plus BREAK???

by: DTV12345 | last post by:

Greetings! This is an excerpt from the Oracle documentation:"...ORACLE SQL*Plus BREAK command creates a subset of records and add space and/or summary lines after each subset. The column you specify in a BREAK command is called a break column which suppresses duplicate values. For example SQL> BREAK ON DEPTNO SKIP 1 // To insert...

DB2 Database

7178

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7397

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7563

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

7125

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...

Windows Server

7543

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

4757

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3239

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1612

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

813

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP