1

I need to create a pivot table of all customers and all products and the price that the customer gets charged but also falling back to a standard customer when the customer has no price set up (the customer 999999 is the standard customer that has a standard price for all products).

I wrote this query;

Select KUST_ADR.KU_NAME As Customer,
  GLAS_DATEN_BASIS.GL_BEZ As Product,
  NVL(OS_PRZU.ZUM2, (Select OS_PRZU.ZUM2 From OS_PRZU
  Where OS_PRZU.IDNR = GLAS_DATEN_BASIS.IDNR And OS_PRZU.KUNR = 999999)) As
  Price
From GLAS_DATEN_BASIS
  Inner Join OS_PRZU On GLAS_DATEN_BASIS.IDNR = OS_PRZU.IDNR
  Inner Join KUST_ADR On KUST_ADR.KU_NR = OS_PRZU.KUNR
Where KUST_ADR.KU_ADR_ART = 0

but still lots of the fields are empty, so NVL() is not working? What am I doing wrong?

1

Apparently this sub-query does not always return a value:

(Select OS_PRZU.ZUM2 From OS_PRZU
Where OS_PRZU.IDNR = GLAS_DATEN_BASIS.IDNR And OS_PRZU.KUNR = 999999)

meaning that the default customer probably does not have a price for some of the products.

Replace the sub-query with a hard-coded value in order to verify that NVL itself works as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.