473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Trying to write a view using union statement

I am trying to extract data from two tables. I am trying to use a union to join the two tables so I can retrieve the information I need. Here is what I have so far but I get an error saying missing select statement. I am not sure if the rest of my code is correct. Any help is appreciated as I am completely new to Oracle. We have Oracle 9i.

CREATE VIEW "LABOR"."LT_VIEW_RPT_VENDOR_CHRGBK" AS
SELECT
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_MECH_PAY_HRS as"MECH_HRS",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_OP_PAY_HRS as"OPER_HRS",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT as "SHIFT",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT_DT as "SHIFT_DATE",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT_ID as "SHIFT_ID",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_UP as "UP"
UNION
SELECT
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_HRS as "UNPROD_HRS",
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_SHIFT_ID as "SHIFT_ID",
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_UNPROD_CD as "UNPROD_HRS_CODE",
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_UP as "UP"
WHERE
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT_DT as "SHIFT_DATE" > '30-Jun-2006' and
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_UNPROD_CD = '14'
Aug 3 '06 #1
3 20709
Let me add some things to my previous post. My two tables have two fields that are unique. They are the LT_SH_CFG_UP and LT_SH_CFG_SHIFT_ID from the table LT_SHIFT_CONFIG and then LT_SH_NCHG_UP and LT_SH_NCHG_SHIFT_ID from the LT_SHIFT_UNPR_NOT_CHRG table. What I want to return is the following:

List of OP and MECH hours along with the total hours for NCHG_HRS, whose UNPROD_CD is = 14. I need to join the two tables together to get this information. I keep getting errors when I try this. Everything from missing select statement, to SQL command not properly ended.
Aug 3 '06 #2
masdi2t
37
you have different number of columns.
you have 6 columns on your first select 'n only 4 columns on your second select.
Aug 4 '06 #3
js549
3
The syntax of a SELECT statement is roughly SELECT Field1, Field2 .... FROM TABLENAME WHERE FIELD3 = SOMETHING - NEITHER of the SELECT statements you have posted have a 'FROM TABLENAME'

ALSO there must be the same number of Fields in each query that is being UNION'd and they should be of a compatible datatype. I would suggest you get some king of reference on basic SQL. Please do not take this as an offense of any sort - we all had to start somewhere. Please note my description above is quite sloppy but you can get exact specification of requirements out of the Online Oracle Documentation. - eg -

http://download-west.oracle.com/docs/cd/B10501_01/index.htm

If I assume your Table is called LABOUR then it should look something like :

CREATE VIEW "LABOR"."LT_VIEW_RPT_VENDOR_CHRGBK" AS
SELECT
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_MECH_PAY_HRS as"MECH_HRS",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_OP_PAY_HRS as"OPER_HRS",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT as "SHIFT",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT_DT as "SHIFT_DATE",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT_ID as "SHIFT_ID",
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_UP as "UP"
FROM LABOUR
UNION
SELECT
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_HRS as "UNPROD_HRS",
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_SHIFT_ID as "SHIFT_ID",
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_UNPROD_CD as "UNPROD_HRS_CODE",
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_UP as "UP"
FROM LABOUR
WHERE
LABOR.LT_SHIFT_CONFIG.LT_SH_CFG_SHIFT_DT as "SHIFT_DATE" > '30-Jun-2006' and
LABOR.LT_SHIFT_UNPR_NOT_CHRG.LT_SH_NCHG_UNPROD_CD = '14'
Aug 15 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: jonnychang | last post by:
What is the purpose of using union in this class? Class A { public: union { int age; char * name; double amount; } };
3
by: Rich Protzel | last post by:
Hello, So my table contains say 100,000 records, and I need to group the categories in fld1 by the highest count of subcategories. Say fld1 contains categories A, B, C, D, E. All of these...
1
by: loRdK | last post by:
Hi, I have a database stored in MS SQL 2000 and an application written in VB5, which connects the database via JET/ODBC. I have a problem with the UNION statement.
5
by: Thomas Baxter | last post by:
Is it possible to have a conditional union statement in a stored proc? Here's an example on the northwind database. If says there's a syntax error near the UNION statement. Looks like it...
7
by: Bob | last post by:
Currently I am using this statement to translate 3 fields in my db thru Visual Basic. I import the data from one table to another then call the IFF statements and the NewDate to translate the...
12
by: Susan Bricker | last post by:
For those of you who have been following my posts - they all pertain to a Dog Competition Organization's Database. There are three classes that the dogs can participate: NOVICE, OPEN, and...
6
by: steve.kim | last post by:
Hello, I'm trying to make a class like below... class myClass { public: // ctor / dtor .... // methods
9
by: John | last post by:
Hello all. I am a PHP newbie and am having an issue using the && in an if statement. here is the code: if ($_REQUEST == "1" && date("Y-m-d") < $rowWork) { die("<h1>The earlybird special has...
2
by: waqasahmed996 | last post by:
hi to all i am using union in select statement like this mysql> SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3; please tell me that is it possible that i can check value...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.