auto colomn names from data
auto colomn names from data
(OP)
i wonder if you can help. this may be in the wrong forum.
i want to produce an auto alias for a group of price bands. the names of what i want the aliases to be are stored in a table and these can change. is there a way to automatically create these and keep them updated?
thanks
i want to produce an auto alias for a group of price bands. the names of what i want the aliases to be are stored in a table and these can change. is there a way to automatically create these and keep them updated?
thanks
RE: auto colomn names from data
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: auto colomn names from data
"StockItem"."Code",
"PriceBand"."Name",
"StockItem"."Manufacturer",
"StockItem"."StockItemStatusID",
"StockItem"."StandardCost",
"StockItemPrice"."Price",
(SELECT "StockItemPrice"."Price" WHERE ("PriceBand"."Name"= 'standard')) AS "PriceBand Standard"
FROM ("WhatMoreLive"."dbo"."StockItemPrice" "StockItemPrice" INNER JOIN "WhatMoreLive"."dbo"."StockItem" "StockItem"
ON "StockItemPrice"."ItemID"="StockItem"."ItemID") INNER JOIN "WhatMoreLive"."dbo"."PriceBand" "PriceBand"
ON "StockItemPrice"."PriceBandID"="PriceBand"."PriceBandID"
WHERE "StockItem"."StockItemStatusID"=0
RE: auto colomn names from data
id name
1 Standard
2 price band 1
3 price band 2
ect
RE: auto colomn names from data
i assumed that a "price band" was something like
10.00 - 14.99 = cheap
15.00 - 29.99 = normal
30.00 - 49.99 = expensive
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: auto colomn names from data
RE: auto colomn names from data
SELECT "StockItem"."Code"
, ...
, ( SELECT "StockItemPrice"."Price"
WHERE "PriceBand"."Name" = 'standard'
) AS "PriceBand Standard"
FROM ...
but this obviolsy doesn't work (because the subquery is missing the FROM clause)
how did you figure that you were supposed to use "standard" as the price band and not one of the other ones?
i've seen queries like this before, and what usually happens is that the actual price of an item determines which band it's in...
SELECT ...
, pricebands.bandname -- e.g. standard
FROM items
INNER
JOIN pricebands
ON items.price BETWEEN pricebands.loprice and pricebands.hiprice
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: auto colomn names from data
the sub querey creates the comlomn name standard. nd fills in the info. what i am alooking for is somethinkg like that but creates a colomn for every "price band name" record in the priceband table.
thanks
RE: auto colomn names from data
RE: auto colomn names from data
RE: auto colomn names from data
now working like a dream