[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 939
  • Last Modified:

Merge two SQL statements into one statement

I have two SQL plus statements that work perfectly independently.  The problem is that the software I use will only allow me to pass one statement.  I am an idiot when it comes to SQL.

Here are the two statements.

SELECT cast(ts as CHAR FORMAT 'DD-MON-YY HH:MI') as "Date", value as "Water" FROM history WHERE name= 'T1EG:AI20589.PV' AND ts >= '01-JAN-07 00:00:00' and ts < current_timestamp AND period = 12:00 and request = 7 and stepped = 1

SELECT cast(ts as CHAR FORMAT 'DD-MON-YY HH:MI') as "Date", value as "DEG" FROM history WHERE name= 'T1EG:AI20588.PV' AND ts >= '01-JAN-07 00:00:00' and ts < current_timestamp AND period = 12:00 and request = 7 and stepped = 1

They look very similar I know but they are different if you look closely.  What I need my final query to be is Date in Column 1, Water in Column 2, and DEG in column 3.  It has to be one statement.

I've had people tell me that there is no way that the individual statements will work.  I assure you that they do.  Maybe the application I use is just funny that way.
0
browneye9000
Asked:
browneye9000
  • 2
  • 2
1 Solution
 
joebednarzCommented:
You didn't mention if this is a single row of output or if there is a primary key in which to relate the two queries, so I'll make some assumptions...

Try this and see if it works:

SELECT a.Date, water, b.Date, Deg
FROM
(
SELECT cast(ts as CHAR FORMAT 'DD-MON-YY HH:MI') as "Date", value as "Water" FROM history WHERE name= 'T1EG:AI20589.PV' AND ts >= '01-JAN-07 00:00:00' and ts < current_timestamp AND period = 12:00 and request = 7 and stepped = 1
) a,
(
SELECT cast(ts as CHAR FORMAT 'DD-MON-YY HH:MI') as "Date", value as "DEG" FROM history WHERE name= 'T1EG:AI20588.PV' AND ts >= '01-JAN-07 00:00:00' and ts < current_timestamp AND period = 12:00 and request = 7 and stepped = 1
) b
WHERE a.period = b.period
 AND a.request = b.request
 AND a.stepped = b.stepped;
0
 
joebednarzCommented:
Oops, just saw your spec about date being the output of column one... assuming Date column matches:

SELECT a.Date, water, Deg
FROM
(
SELECT cast(ts as CHAR FORMAT 'DD-MON-YY HH:MI') as "Date", value as "Water" FROM history WHERE name= 'T1EG:AI20589.PV' AND ts >= '01-JAN-07 00:00:00' and ts < current_timestamp AND period = 12:00 and request = 7 and stepped = 1
) a,
(
SELECT cast(ts as CHAR FORMAT 'DD-MON-YY HH:MI') as "Date", value as "DEG" FROM history WHERE name= 'T1EG:AI20588.PV' AND ts >= '01-JAN-07 00:00:00' and ts < current_timestamp AND period = 12:00 and request = 7 and stepped = 1
) b
WHERE a.ts = b.ts
 AND a.period = b.period
 AND a.request = b.request
 AND a.stepped = b.stepped;
0
 
browneye9000Author Commented:
I tried it and it didn't work.  My application tells me that Date is a reserved word.  Maybe the a.Date can't be used?  That is where the pseudo debugger highlighted the problem.
0
 
browneye9000Author Commented:
Got it to work by doing a few of things.
1) renamed a.Date to a.Dat (removed the e) and did this throughout.
2) Removed AND a.period = b.period AND a.request = b.request AND a.stepped = b.stepped
3) Changed WHERE a.ts = b.ts to a.Dat = b.Dat

Thanks so much for your help.  I could have never gotten it to work without your help!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now