Re: A Question of Tables

From: Astra (info_at_NoEmail.com)
Date: 05/04/04 

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
>
>

Relevant Pages

  • Re: SQL Results Problem
    ... My 1st table is called STOCKPRICES and a typical example of a row in the ... Taking the above as an example, if the user selects their currency to be US ... Dollar even though the web site default is Sterling I wanted my query to ... but as STP and KGC don't have price entered in the STOCKPRICES table ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Results Problem
    ... I have no idea how to write a query ... that aren't entered in your default currency. ... > query to know that IVP has a US Dollar price in the STOCKPRICES table ...
    (microsoft.public.inetserver.asp.db)
  • Re: A question of tables
    ... the currencyid specified then I can change my query to take the exchange ... price (which should be in the STOCKPRICES table) with this exchange rate. ... selected lang and currency, ie cSelCurrency and cSelLang. ...
    (microsoft.public.inetserver.asp.db)
  • A Question of Tables
    ... the currencyid specified then I can change my query to take the exchange ... price (which should be in the STOCKPRICES table) with this exchange rate. ... selected lang and currency, ie cSelCurrency and cSelLang. ...
    (microsoft.public.sqlserver.programming)
  • Re: A Question of Tables
    ... Without getting into your specific query, ... > price (which should be in the STOCKPRICES table) with this exchange rate. ... > selected lang and currency, ...
    (microsoft.public.sqlserver.programming)
Loading