Selecting different records from a same column
-
Hi Gurus - I need your expertise:
ACTUALLY, wanting to allow Visitors of my (future) site to recalculate the price of Contracts, or any other value, due to a escalation of prices, measured by 01 among 04 different official indexes,
There will be a page where
he will INPUT1. the VALUE to be recalculated ("value"),
2. the MONTH/YEAR when that value was established ("month0", "year0"),
3. the name of the INDEX settled in the Contract, or which
he will want to be used for this purpose ("index"), and also4. the MONTH/YEAR to which
he will want that value, through that index, to be updated ("month1", "year1").The table "indecontb", that saves those monthly indexes, has 04 columns: each row saves (1st column) the "month", (2nd) the "year" and (3rd) the name of the "index" and (4th) another one for the "number" of the index.
So, to calculate the increase of value occured between the "month0"-"year0" - and - the "month1"-"year1", I need to SELECT -TWO- different "number"s of indexes:
- the 1st, WHERE it relates to the (name of the)"index"-"month0"-"year0": renaming the record to a different name (I prefer "indexnumber0"); and
- the 2nd, WHERE it relates to the (name of the)"index"-"month1"-"year1": renaming the record to "indexnumber1".
...THE PROBLEM IS that:
a) when I put the 02 SELECTs TOGETHER, I didn't succeed in including those WHERE conditions in the same statement... (? can I?, HOW?), and more:
b) when I put the two SELECTs IN TWO DIFFERENT STATEMENTS (the first amended by a "&" to the second), ...only the SECOND SELECT is displayed, afterwards, through a "Response.write"...
I've already tried a #TEMPorary table, but the problem remains...
Even trying to transfer just ONE of these 02 "number"s to the TEMP table, even so I failed (because I didn't get how to relate the 02 IDs)...
Would You Gurus be kind in helping me?
Thanks in advance!
DHIGilson
-
Got lost with the logic b/c of all the wordings,
Give an example of your table structure and a sample query
MW
MW -
The shorthand is kind of difficult to read!
-
If you want both records in one resultset, use UNION between the selects.
SELECT zzz FROM ...
UNION
SELECT yyy FROM ... -
mworku, 5409045121009
You are right! By the way, I was already returning to ask You to forgive me, because I forgot some important things:
1. the pages are .ASP, using a SQL Server 2000 database
2. after INPUTing the informations, the form is POSTed to another page, where there is this code:
"Dim ....., month0, year0, month1, year1, index, indexnumber0, indexnumberl, k, ...
m0=request.form("month0")
m1=request.form("month1")
y0=request.form("year0")
y1=request.form("year1")
v = request.form("value")
k = request.form("index")
if k = "igpm" then
k = "igpm"
elseif k = "igpdi" then
k = "igpdi"
elseif k = "ipca" then
k = "ipca"
elseif k = "poup01" then
k = "poup01"
end if
.....
strQ = "SELECT number AS 'number0' FROM indecontb WHERE index LIKE '%" & k & "%' AND month LIKE '%" & month0 & "%' AND year LIKE '%" & year0 & "%' "
strQ = strQ & "SELECT number AS 'number1' FROM indecontb WHERE index LIKE '%" & k & "%' AND month LIKE '%" & month1 & "%' AND year LIKE '%" & year1 & "%' "As I said, after those codes, the "response.write" just outputs the SECOND data!
Thanks again, since now!
...but, NPeeters, thanks for your help: I'll test it, and will give You a feedback!
Thanks again, and again!
DHIGilson
-
Still not sure what you want to accomplish.
If the idea is to fetch the two indexvalues from the database in a single statement, consider writing a stored procedure.
You would pass in the necessary variables and get the two indexes back.
CREATE PROCEDURE up_fetchindexvalues
@month0 int,
@year0 int,
@month1 int,
@year1 int,
@index varchar(10),
@indexnumber0 double OUTPUT,
@indexnumber1 double OUTPUT
AS
--Put your selects here...
SELECT @indexnumber0 = number
FROM indecontb
WHERE year = @year0
AND month = @month0
AND index = @index
--Put your second select hereThen, in your ASP you would execute this statement as a stored procedure.
Check following article for more info on this.
For ASP.NET:http://www.sqlservercentral.com/columnists/jwiner/anintroductiontousingtheadonetsqlcommandobject.asp
For ASP:http://www.sqlservercentral.com/columnists/awarren/introductiontoadothecommandobject.asp
-
NPeeters
Before I try the "stored procedure" option, please let me make You a summary:
1. - I want to offer my Visitors to update the value (for instance) of a Contract through one of the major economic indexes monthly available.
So, in a FORM to be POSTed to the next page, they must INPUT
(a)the value to be updated and
(b)(c)the month/year when it was settled ("month0", "year0"), and
(d)(e)the month/year they want that value to be updated to ("month1", "year1") - and also, of course,
(f) which (from a list) economic index the program should use to make this updating ("index").
2. - these 06 informations ("a" to "f"), after DIMed and after a "request.form", come to view, normally, through the command
---> Response.write "date...." & month1 & "<BR>"
3. - but not the only TWO LAST informations that must come from recordsets, oriented from those informations above.
4. - well, in a SQL Server table "indecontb", each row brings these informations - please consider some EXAMPLES:
COLUMNs month year index number
--------------------------------------------------
row nr x 04 2003 igpm 123,4567
- - - - - - - - - - - - - - - - - -
row nr y 09 2002 igpm 111,2233
- - - - - - - - - - - - - - - - - -
row nr z 07 2002 igpm 101,4774
5. - so, if the Visitor wants to update the value $ XXX,XX of a Contract (for instance) from 07/2002 to 04/2003 using the index "igpm", the program must pick the values "101,4774" (related to the "month0/year0" of 07/2002) and "123,4567" (related to "month1/year1" of 04/2003), in order to divide one from the other.
6. - well, THE PROBLEM IS that BOTH come from the same column ("number"). So, I tried to SELECT each one attaching to each one an "alias" (".....AS 'indexnumber0'", ".....AS 'indexnumber1'") to make possible to the system to understand them differently.
7. - then, the program only recognizes the second...
Plase consider the rest of the information: page in .ASP, SQL Server database, etc...
Would You be kind in follow on helping me? Many thanks in advance!
DHIGilson
-
Not being harsh but still cryptic
please show us your table structure and your sample query
Also let us know what the criterias are for selecting, updating or ...
MW
MW -
mworku
Here You have!
When a first brought some script, I "translated" into English the statements -- but now I did prefer to keep it AS IT IS really!
So, please give a look of the 03 scripts (the first, "page1", doesn't matter, it's just the presentation page): in .txt:
- http://www.contratosonline.com.br/biblioteca/indseconomicos/calcatualizacao1.txt
- http://www.contratosonline.com.br/biblioteca/indseconomicos/calcatualizacao2.txt
- http://www.contratosonline.com.br/biblioteca/indseconomicos/calcatualizacao3.txt
And also -- the structure of the table "indecontb": in EXCEL (all of the SQL DB and tables of the whole program are not yet in the site, because my poor provider doesn't support SQL any more..., so I'm looking for a GOOD provider, not change just for changing's sake...):
- http://www.contratosonline.com.br/biblioteca/indseconomicos/indecontb.xls.
The query, is the proper "page2".
Thank You very much, indeed, and again!
DHIGilson
-
is this what you are looking for
SELECT number0 , number1
FROM
(
SELECT number AS number0
FROM indecontb
WHERE index LIKE @idx AND
month LIKE @month0 AND
year LIKE @year0
) As number0
CROSS JOIN
(
SELECT number AS number1
FROM indecontb
WHERE index LIKE @idx AND
month LIKE @month1 AND
year LIKE @year1
) As number1
should give you both numbers in a single row
-
GRN
I still do not understand WHAT is going on, with my @%&*~{<# script!
Please take a look at my last post, where I copied the script I was using.
Due to your post, I altered it: and the result is as follows (as I have already said, I first used to "translate" some words, the name of some records, into English - but now I copied them as they really are displayed:
----------------------------
strQ = "SELECT mesind, anoind, tipoind, numeroind0, numeroind1 FROM (SELECT numeroind AS numeroind0 FROM indecontb WHERE tipoind LIKE @k AND mesind LIKE @mesind0 AND anoind LIKE @anoind0) AS numeroind0 CROSS JOIN (SELECT numeroind AS numeroind1 FROM indecontb WHERE tipoind LIKE @k AND mesind LIKE @mesind1 AND anoind LIKE @anoind1) AS numeroind1"
set objRS = conn.execute(strQ)
objRS.Open strQ
----------------------------
Those keywords are already DIMed, as You can see in the script of page 2.
And more: please observe that I altered the expression "@idx" to "@k" (as stated in the line --> k = request.form("tipoind") <--
Thanks again for your help!
DHIGilson
-
GRN and all my Gurus
Wow! Eureka! It worked! See the code:
----------
strQ = "SELECT numeroind0, numeroind1 FROM (SELECT mesind, anoind, tipoind, numeroind AS numeroind0 FROM indecontb WHERE tipoind LIKE '%" & k & "%' AND mesind LIKE '%" & mesind0 & "%' AND anoind LIKE '%" & anoind0 & "%') AS numeroind0 CROSS JOIN (SELECT mesind, anoind, tipoind, numeroind AS numeroind1 FROM indecontb WHERE tipoind LIKE '%" & k & "%' AND mesind LIKE '%" & mesind1 & "%' AND anoind LIKE '%" & anoind1 & "%') AS numeroind1"
set objRS = conn.execute(strQ)
objRS.Open strQ
----------
I must have done something wrong, any tiny detail, because it's GRN idea!
Even so, I want to thank all my Gurus for this kind -- and huge! -- attention, and patience with me! God thank You all!
DHIGilson
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply