This is my problem.
I have a table which is like this
"DK5" "184:00" "168:00" "00:00" 0 0 0 1
"F02" "185:00" "152:00" "00:00" 0 2 0 2
"SX1" "184:00" "107:00" "00:00" 3 0 0 3
I want to read every row until the end of the table.
Inside this I want another loop that reads next column's value.
there are no for loops as in C language. I can't find the appropriate words to "google it".
Thank you! :)
10 4493
This is my problem.
I have a table which is like this
"DK5" "184:00" "168:00" "00:00" 0 0 0 1
"F02" "185:00" "152:00" "00:00" 0 2 0 2
"SX1" "184:00" "107:00" "00:00" 3 0 0 3
I want to read every row until the end of the table.
Inside this I want another loop that reads next column's value.
there are no for loops as in C language. I can't find the appropriate words to "google it".
Thank you! :)
if you want to loop through the records use cursor
Are you trying in C or MSSQL?
I use MS SQL.
If I use cursor how it will recognize the end of the rows?
@@fetch=0?
Thanks
Some, including me, would recommend cursor as last resort. Would you mind telling us what your desired output is? It might be accomplished by a complex query.
-- CK
OK lets see.
I want to read the first column which is an ID. After that real the content of the next column (lets say VAL1) . If it is not zero (<>0), I want to insert some values into a table. The values are VAL1, ID and some other ones which I can insert on my own.
After that read the next column etc.
Then go to the next row...
I also find it hard using cursors. Is it the only way?
OK lets see.
I want to read the first column which is an ID. After that real the content of the next column (lets say VAL1) . If it is not zero (<>0), I want to insert some values into a table. The values are VAL1, ID and some other ones which I can insert on my own.
After that read the next column etc.
Then go to the next row...
I also find it hard using cursors. Is it the only way?
What are the other conditions and what do you want to do if those conditions are true. For now, I'll based this on just what you wrote.
You can try: - INSERT INTO YourDumpTable (VAL1, ID, SomeOtherColumn)
-
select Val1, ID, SomeOtherColumn from YourMainTable where ID <> 0
The above query will INSERT all records from YourMainTable into YourDumpTable all rows with ID <> 0
If you can list all your conditions, we might need to figure out some other ways. Am just basing the code on what you wrote.
-- CK
I finally used cursor. - fetch next ... into @myvariable1 @myvariable2 etc...
It works but I have another problem right now. Some codes are not correct (should not be taken into consideration). I should make a code that searches a table. If this code exists then insert into target-table values. This is to avoid duplicate records, because if the code does not exist a get a new row with the previous code. So I have the same code in two rows.
Well I'm just interested in finding out how NOT to use cursors..
Any "tutorial"?
THANK YOU ALL FOR YOUR PATIENCE!!!
If you could post the table structures of your transaction table and your "dump" table and the conditions you want to perform before insert to "dump" table and your desired result (sample data), I think we can build a query for you.
-- CK
OK. Here is the table.
"DK5" "184:00" "168:00" "00:00" "00:00" "00:00"
-------------------------------------------------------------------------
"F02" "185:00" "152:00" "01:00" "00:00" "00:00"
-------------------------------------------------------------------------
"SC1" "184:00" "107:00" "00:00" "05:00" "30:00"
-------------------------------------------------------------------------
"D2" "168:00" "64:00" "00:00" "00:00" "18:00"
Rows are defined by double quotes.
I want to read the first row. The first column is a code. I want to search another table (lets say it codes_table). If the code exists there then we read the second column. If it does not read the next row.
If it exists we read the next column. If it is >0 then insert into another table (lets say it Final_table) some values. - INSERT INTO FINAL_TABLE VALUES (DATE, CODE, VALUE_THAT_IS>0)
Then go to the next column and do the same thing. If it is also >0 a new row will be inserted with the same code. When it finishes all the columns, go to the next row. Here it will read a new code. It will search again at codes_table. If the code exists then etc...
If it is possible I want "date" to be defined by the final user! But except that getdate() function (also not defined by user) I can't find something...
Finally I want to be printed a message that would say how many rows were inserted in the Final_table with a DISTINCT code. For example 3 rows with code "DK5" count for one.
THANK YOU SO MUCH, CK!
"Rows are defined by double quotes." I assume you mean Columns are defined by double quotes. I also convert your ":" to "." to make it a number.
One last question, what version of MSSQL are you using?
If you're using MS-SQL 2005, you're in luck. Paste this in a Query Window on your Management Studion - declare @t table
-
(code varchar(3), col1 decimal(6,2),
-
col2 decimal(6,2), col3 decimal(6,2),
-
col4 decimal(6,2), col5 decimal(6,2))
-
-
declare @codes_table table (code varchar(3))
-
-
insert @codes_table values ('DK5')
-
insert @codes_table values ('F02')
-
insert @codes_table values ('D2')
-
insert @codes_table values ('XYZ')
-
-
-
insert @t values ('DK5', 184.00, 168.00, 00.00, 00.00, 00.00)
-
insert @t values ('F02', 185.00, 152.00, 01.00, 00.00, 00.00)
-
insert @t values ('SC1', 184.00, 107.00, 00.00, 05.00, 30.00)
-
insert @t values ('D2', 168.00, 64.00, 00.00, 00.00, 18.00)
-
insert @t values ('CK', 168.00, 64.00, 00.00, 00.00, 18.00)
-
insert @t values ('XYZ', 168.00, 64.00, 00.00, 00.00, 18.00)
-
-
-
select * from @t
-
select * from @codes_table
-
-
select code, codes, cols
-
from
-
(select code, col1, col2, col3, col4, col5 from @t t where t.code in (select cd.code from @codes_table cd)) p
-
unpivot
-
(
-
cols for Codes in
-
(col1, col2, col3, col4, col5)
-
) as unp
-
where cols > 0
-
-
You can put all these in a stored proc so that you can just pass the date as parameter.
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bob Alston |
last post by:
Anyone know a way to make all access to a linked table, in another
Access MDB, read only?
I really don't want all the hassle of implementing full access security.
I can't do this at the server...
|
by: Matthew Louden |
last post by:
I want to read how many records in the table, and insert a record with id
field which increment the counter by 1. However, I had the following runtime
on Dim s As Integer = CInt(dr("t")). Since "t"...
|
by: Programmer |
last post by:
Hi all
I wan't to know if i'm able to read mail from a mail server. My mail server
is a pop3 server (UNIX) and i want to be able to get the mails from an aspx
or an asmx. with out using external...
|
by: Richard Albrecht |
last post by:
I have been trying to figure out for days on how to read values from a Bound
ListBox. The listBox gets the values from an Access Table.
I can read values fine for Non-Bound ListBoxes, But the...
|
by: whitemoss |
last post by:
Hi All,
I had written a code to read a file and insert it's contents to the database. Since I will receive 3 files every hour, so, this program should read those files and insert the contents...
|
by: troy |
last post by:
Could someone please point me in the right direction on how to read in
a large query with .net.
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query....
|
by: ncjed |
last post by:
First off, my apologies if this topic has previously been covered.
I am in the process of creating an A2K DB (Win XP) that will open other DBs (either on local drive, mapped drives, or UNC network...
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big...
|
by: Cintury |
last post by:
Hi all,
I've developed a mobile application for windows mobile 5.0 that has been in use for a while (1 year and a couple of months). It was developed in visual studios 2005 with a back-end sql...
|
by: dizzydangler |
last post by:
Hi, all! After receiving a lot of help from ADezzi, Msquared, and NeoPa getting my ms access 2007 db functioning, I've run into another hitch that I haven't been able to solve. The database itself...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |