sql query problem - how to add "if - then" clause?

[03021979]

This is the query:

SELECT MSORNSTD.CONO40, MSORNSTD.WORD40, PARTS.PGMN35, MSORNSTD.ITEM40, PARTS.PDES35, MSORNSTD.WOOQ40, MSORNSTD.WQCM40, MSORNSTD.wOOQ40-MSORNSTD.wQCM40, MSORNSTD.WCOQ40, PARTS.DSFC35, PARTS.PGMJ35
FROM EUROPEAN.TVSPF00.MSORNSTD MSORNSTD, EUROPEAN.TVSPF00.PARTS PARTS
WHERE PARTS.CONO35 = MSORNSTD.CONO40 AND PARTS.PNUM35 = MSORNSTD.ITEM40 AND ((MSORNSTD.CONO40='88') AND (MSORNSTD.WORD40>'W180000'))

The matter is that I want to modify MSORNSTD.wOOQ40-MSORNSTD.wQCM40. If this value < 0 then it needs to be 0, and if this value => 0 then it needs to be just MSORNSTD.wOOQ40-MSORNSTD.wQCM40. No records can be exluded!

I'd be grateful for a clue!

 

[SkipVought]

Hi,


It DEPENDS on the DATABASE that you are querying.

If its an Access/Excel database, use IIF.

If its Oracle or DB2 then use CASE.

iif(MSORNSTD.wOOQ40-MSORNSTD.wQCM40<0,0,MSORNSTD.wOOQ40-MSORNSTD.wQCM40)

Case When MSORNSTD.wOOQ40-MSORNSTD.wQCM40<0 Then 
   0
Else 
   MSORNSTD.wOOQ40-MSORNSTD.wQCM40 
End

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[03021979]

It's an oracle database but triggered from VB in Excel like this below:

StrSql = "SELECT Max(INVCEHED.INVN65) Maksiu" & Chr(13) & "" & Chr(10) & _
"FROM EUROPEAN.TVSPF00.INVCEHED INVCEHED" & Chr(13) & "" & Chr(10) & _
"WHERE INVCEHED.CONO65='88' and (INVCEHED.INVN65 >'0606000')" _

Sheets("JBA_MAX").Select
Worksheets("JBA_MAX").Select
Worksheets("JBA_MAX").Range("A1").Select
Selection.CurrentRegion.Select
Selection.Clear
Application.ScreenUpdating = False

With ActiveSheet.QueryTables.Add(Connection:=StrConnection, Destination:= _
Worksheets("JBA_MAX").Range("A1"))
.CommandText = StrSql
.Name = "Query from JBA"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

So where to insert if-then clause into StrSql?

 

[SkipVought]

Then use the CASE construct. I query Oracle DBs often from Excel.

StrSql = "SELECT 
  MSORNSTD.CONO40
, MSORNSTD.WORD40
, PARTS.PGMN35
, MSORNSTD.ITEM40
, PARTS.PDES35
, MSORNSTD.WOOQ40
, MSORNSTD.WQCM40
, Case When MSORNSTD.wOOQ40-MSORNSTD.wQCM40<0 Then 
   0
  Else 
   MSORNSTD.wOOQ40-MSORNSTD.wQCM40 
  End
, MSORNSTD.WCOQ40
, PARTS.DSFC35
, PARTS.PGMJ35

FROM 
  EUROPEAN.TVSPF00.MSORNSTD MSORNSTD
, EUROPEAN.TVSPF00.PARTS PARTS

WHERE PARTS.CONO35 = MSORNSTD.CONO40 
  AND PARTS.PNUM35 = MSORNSTD.ITEM40 
  AND ((MSORNSTD.CONO40='88') 
  AND (MSORNSTD.WORD40>'W180000'))"

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[03021979]

thanks a lot!