Re: A Question of Tables
From: Astra (info_at_NoEmail.com)
Date: 05/04/04
- Next message: Tibor Karaszi: "Re: Table Deleted Accidently"
- Previous message: Vlad: "Re: Test"
- In reply to: Wayne Snyder: "Re: A Question of Tables"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 4 May 2004 14:44:16 +0100
Thanks for coming back to me Wayne.
Because I'm completely crap at these sort of things, could you please post
me a quick example of what you mean. I can always read these better than
Pseudos.
Thanks
Robbie
"Wayne Snyder" <wsnyder@computeredservices.com> wrote in message
news:uO$aO%23cMEHA.1392@TK2MSFTNGP09.phx.gbl...
Without getting into your specific query, try substituting a case statement
for the column in question ie.
case when whatever is null then expressionforcolumnvalue Else
Otherexpressionforvolumnvalue END as columnname
-- Wayne Snyder, MCDBA, SQL Server MVP Computer Education Services Corporation (CESC), Charlotte, NC www.computeredservices.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "Astra" <info@NoEmail.com> wrote in message news:40975d45_4@127.0.0.1... > Just to elaborate on this theme, is there anyway that I could adapt the > query so that if there isn't a price available in the STOCKPRICES table for > the currencyid specified then I can change my query to take the exchange > rate from a 4th table (or session var) and convert the default currency > price (which should be in the STOCKPRICES table) with this exchange rate. > > At present, I using the following method to cater for the above and > aforementioned problems (see below): > > IF cSelCurrency <> cDefCurrency THEN > > strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC, > ((sp.RRPNETAMOUNT * " & cSelCurrencyRate & ") + (sp.RRPTAXAMOUNT * " & > cSelCurrencyRate & ")) AS RRP, ((sp.NETAMOUNT * " & cSelCurrencyRate & ") + > (sp.TAXAMOUNT * " & cSelCurrencyRate & ")) AS PRICE FROM STOCK s, STOCKDESCS > sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID AND > sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1" > > ELSE > > strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC, > (sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS > PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID > AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1" > > END IF > > I then execute whichever query string is relevant. > > Rgds > > Laphan > > > Laphan <news@DoNotEmailMe.co.uk> wrote in message > news:ONxroEQMEHA.1388@TK2MSFTNGP09.phx.gbl... > Hi All > > As per my previous posting, I'm having trouble getting my schema out of my > DB so you will have to bear with me on this. > > Basically I have the following tables (PLEASE NOTE: these are me > pseudo-trying to create DDL - apologies for errors): > > CREATE TABLE STOCK > (STOCKID TEXT(30) NOT NULL PK, > CATID TEXT(30) NOT NULL, > MANUID TEXT(30) NOT NULL, > TAXID TEXT(30) NOT NULL, > PIC TEXT(50) NULL, > THUMBPIC TEXT(50) NULL, > UNIT TEXT(50) NULL, > WEIGHT INTEGER NULL, > QTYINSTOCK INTEGER > NEWSTOCK INTEGER > DISCON BYTE > SPECIAL BYTE > LASTUPDATE DATE/TIME); > > CREATE TABLE STOCKDESCS > (STOCKID TEXT(30) NOT NULL, > STOCKNAME TEXT(50) NOT NULL, > SHORTDESC TEXT(255) NULL, > FULLDESC MEMO NULL, > LANGID TEXT(30) NOT NULL); > > CREATE TABLE STOCKPRICES > (STOCKID TEXT(30) NOT NULL, > CURRENCYID TEXT(30) NOT NULL, > RRPNETAMOUNT DECIMAL(18,3) NULL, > RRPTAXAMOUNT DECIMAL(18,3) NULL, > NETAMOUNT DECIMAL(18,3) NULL, > TAXAMOUNT DECIMAL(18,3) NULL); > > Not actually sure if I should have the above price amounts set to > DECIMAL(18,3), but I read that I should use the decimal option as it is more > accurate and the Access default is 18,3 > > Anyway this isn't the problem. My prob is that if I run the below query: > > strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC, > (sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS > PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID > AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND > sp.CURRENCYID='" & cSelCurrency & "' AND s.SPECIAL=1" > > I get the cartisan (sp?) result that there has to be entries in the > STOCKDESCS and STOCKPRICES tables in order to bring back the records for the > selected lang and currency, ie cSelCurrency and cSelLang. > > The reason for my query is that my ASP page has pop-up options for different > currencies and languages. The only problem is that the above query really > only works if the admin user has religiously entered descs and prices for > each currency and language. > > I need it that if the prices and/or descs aren't entered then I still bring > back the rest of the data. > > I'm putting in flags so that the user can omit the necessary langs and > currencies so missing data won't look odd. > > Is it all to do with these outer joins again? > > Your help would be appreciated. > > Rgds > > Laphan > >
- Next message: Tibor Karaszi: "Re: Table Deleted Accidently"
- Previous message: Vlad: "Re: Test"
- In reply to: Wayne Snyder: "Re: A Question of Tables"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
