Merging two SQL''s

Question

Merging two SQL''s

Stuart Barber

SSC Enthusiast

Points: 152
More actions
March 2, 2007 at 10:37 am

#176203

I currently have two indivdual sqls that i need to merge to put into one record but dont know how - any help?
INSERT INTO TWSST
                      (_value,_lastdate)
SELECT     SUM(AMTCUR_0*SNS_0),Max(RELBANK.OPEDAT_0)
FROM Adonix.THIERRY.RELBANK RELBANK
WHERE (RELBANK.BAN_0='TWSST') AND (RELBANK.RECTYP_0=2)
                          UPDATE    TWSST
                           SET              date_ = { fn NOW() }
                           WHERE     date_ = '01/01/1900'
and
INSERT INTO TWSST
                      (_newvalue)
SELECT     SUM(AMTLOC_0*SNS_0)
FROM Adonix.THIERRY.OTHBANK OTHBANK
WHERE (OTHBANK.BAN_0='XXXST') AND (OTHBANK.RECTYP_0=3)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

because your WHERE statements affect different records, you can't merge them together into the SAME SQL

there is no guarantee that the data found would be the same rows, and would most likely affect zero rows. Obviously,you would never have any records where a specific field (RECTYPE_0) is equal to 2 AND Equal to 3, so you need to explain in more detail what you were trying to accomplish.

INSERT INTO TWSST

(_value,_lastdate)

SELECT SUM(AMTCUR_0*SNS_0),Max(RELBANK.OPEDAT_0)

FROM Adonix.THIERRY.RELBANK RELBANK

WHERE (RELBANK.BAN_0='TWSST') AND (RELBANK.RECTYP_0=2)

UPDATE TWSST

SET date_ = { fn NOW() }

WHERE date_ = '01/01/1900'

and

INSERT INTO TWSST

(_newvalue)

SELECT SUM(AMTLOC_0*SNS_0)

FROM Adonix.THIERRY.OTHBANK OTHBANK

WHERE (OTHBANK.BAN_0='XXXST') AND (OTHBANK.RECTYP_0=3)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!