Select problem

[AndreasAuer]

I have to do a select on a table called produktion. The primary keys are bezugnr and lfdnr where bezugnr is the number of an order and the lfdnr marks the number of the position in the order. Now I have to get max(berende) from all positions per order except the one with 'VERSAND' in column artnr. The Problem now is that I also have to get berstart from the position with 'VERSAND' in artnr. I have to get this into one select to make a view.

 

[karluk]

I think this can best be done through a union. See if the following query does what you want.

select bezugnr, lfdnr, artnr,
null as berstart,
max(berende) as berende from produktion
where artnr <> 'VERSAND'
group by bezugnr, lfdnr
union
select bezugnr, lfdnr, artnr,
berstart,
null as berende from produktion
where artnr = 'VERSAND'

 

[karluk]

I did this on an 8.0.6 install. Probably 8i is similar.

The scripts get saved to a directory. As I recall you can pick the directory of your choice. The scripts consist of several files with .sql extensions and one file with a .bat extension. The .bat file contains the commands to create the instance and execute the contents of the sql files.

You can edit the sql files and then run the batch file. Alternatively you can take the commands from the batch file and execute them one at a time, debugging and verifying as you go.

Please recall that double clicking the batch file will probably start execution. I did that once when I was intending to edit the file instead. A rather painful lesson.

 

[karluk]

Sorry, I posted to the wrong thread.