|
Looking at your example it is not possible to identify the col1/2 content. Assuming the fist number is not in col1 I would break it into 2 queries
The first query should group by the col1 and count the instances of the different specifications.
The second query should build the string based on that information.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, Thank you so much for your reply.
Could you please find the table information clearly with each row and data separated with pipe delimeter (|).
As you suggested, Group the column by Col1.. As the Col1 Information is unique.. i think it will give the same data even after grouping.
SELECT COL1, COL2 FROM TABLE1
COL1|COL2
1| Tier 1 (QL) (ST) (PA)
2| Tier 2 (QL) (ST)
3| Tier 1 (ST)
4| Tier 1 (ST) (PA)
5| Tier 1
-----------------------------------------------------
INSERT INTO TABLE2 (COL1,COL2,QL_FLG,ST_FLG,PA_FLG)
SELECT COL1,COL2, , , FROM TABLE1
------------------------------------------------------
SELECT * FROM TABLE2
COL1| COL2| QL_FLG| ST_FLG| PA_FLG
1| Tier 1 (QL) (ST) (PA)| 1 |1| 1
2| Tier 2 (QL) (ST)| 1 |1| 0
3| Tier 1 (ST)| 0 |1 |0
4| Tier 1 (ST) (PA)| 0| 1| 1
5| Tier 1 |0 |0 |0
|
|
|
|
|
Ok so this is not structured data it is rubbish, you are going to have to parse col2 of table1 into proper data. Then create a normalised table where you have 3 records for 1|Tier1.
After that it is a positional or possibly a pivot.
OR
You can create a cursor spit to process each record into the desired table2 format by parsing the col2 of table1.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI, Thank you for the reply.
Actaully the data is pretty much structered... since i cannot able to show the data here with proper horizantal and vertical bars,i kept Pipe delimeter so that data of each column will be separate from each other.
My source table is having rows in arround 500 - 600K using a cursor will be costly in performence point of view.
Could u please suggest if there is any other alternative.
|
|
|
|
|
HI, this is how it worked for me
INSERT INTO TABLE2 (COL1,
COL2,
QL_FLG,
ST_FLG,
PA_FLG)
(SELECT COL1,
COL2,
DECODE(INSTR(COL2,'QL'),0,0,1) QL_FLG,
DECODE(INSTR(COL2,'ST'),0,0,1) ST_FLG,
DECODE(INSTR(COL2,'PA'),0,0,1) PA_FLG
FROM TABLE1);
Thank you so much for your help.
|
|
|
|
|
any one can give me example about comlpex data binding with access database and its tables (bind combo box to access database tables)
|
|
|
|
|
This question begs a LMGTFY, have you done even the minimum of research before asking a question that has been answered 1000s of times. Read the guidelines, follow them!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
 We were all beginners once, if you typed this into google
Achilles84 wrote: bind combo box to access database tables
and added ADO.net you would have found numerous examples and articles explaining how to achieve your connection. You should at least read a book on your subject and work through some examples. You cannot learn your basics from forum questions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
please i already know how to bind combo box to access database but my problem is :
i have 2 combo box the combobox1 binding to table1 which has 3 records as follow
college name
____________
college of engineering
college of science
table 2 has 2 column
college of engineering department College of Science
_________________________________ ___________________
civil eng department Department of botany
electronic eng.department Department of biology
computer eng.department Department of physics
table 2 binding to combo box 1 so if combo box 1 display member is college of science
the combobox2 automatically diplay only college of science column
|
|
|
|
|
I have a case statement. I want to save the case value in a variable so that I can use the variable later in the stored procedure.
Ex:
case when Flag = 'Y' then CONVERT(varchar(10), (LastDate), 120) end as @var1,
case when @var1 = ... then ..... end
Please help
|
|
|
|
|
SET @VariableName = CASE Flag When 'Y' then etc...
or
select @VariableName = CASE Flag When 'Y' then etc...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
the Stored proc is as follows:
select empno, datejoined,
case when Flag = 'Y' then CONVERT(varchar(10), (LastDate), 120) end as @var1,
case when @var1 = ... then ..... end
from tableemp
|
|
|
|
|
You are combining a select query and an assignment query, not allowed!
You need to nest your case statements as you need to test the case for each record, variable assignment is a once only operation (using a cursor spit would allow you to do the record by record processing).
This example test the value of flag but note that it must know what to expect in the nested case, your ... cannot be a random date, it must be a testable value!
SELECT Records,
CASE Flag WHEN 'FX' THEN CASE Records WHEN 0 THEN 'Failed' ELSE 'Ok' END
WHEN 'MM' THEN CASE Records WHEN 0 THEN 'Failed' ELSE 'Ok' END
WHEN 'TZ' THEN CASE Records WHEN 0 THEN 'Failed' ELSE 'Ok' END END
FROM ProcessLog PL
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm trying to find the answer to this question.
Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not?
When does it generate the new ID? At transaction start or finish?
Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully?
thanks
|
|
|
|
|
Not important.
The point with autoincrementation is that your key field has unique values, and gaps in the sequence does not matter.
And if it matters, you should probably rethink your design.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Ok so you don't know. Anyone else?
|
|
|
|
|
That's pretty rude: he gave you an answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Rude? If I didn't need the answer I wouldn't have asked the question.
|
|
|
|
|
Maybe we can chalk this down to a lost in translation but what Jorgen said is correct.
Identity columns allow you to assign a unqiue value to each row. It doesn't get reset to the previous value if you rollback the transaction.
If you require no gaps in your keys then you will need to think of a different approach.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Now you're just being a twat. He gave you an answer. You should have just moved on. What's the incentive for anyone else to help you in the future if they think you'll just be rude if you don't like the answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I did move on. Until someone gave an answer to my question which was all i needed to know.
Why do some people think that a question must always be answered with with an assumption that everything you do is wrong and you should change your thinking according to theirs? Did any of you even bothered to ask why I needed the answer to that question? (except the 2 straight to the point answers - thanks again). No you didn't. You just assumed it is something vital to what I do and you advised me to change my logic.
So to sum it up and end this hopefully:
- i got the answer I needed which is great
- i was looking for an answer and not a solution
- the mayans were wrong about the world end (along with whoever misunderstood what they were trying to say)
- check above fact and see if you find any analogy with this now turning into a flame thread
Thanks
|
|
|
|
|
piticcotoc wrote: Why do some people think that a question must always be answered with with an assumption that everything you do is wrong
..because we never use the value of an identity field; it does not (EVER) hold information. Asking what the number is indicates that you might indeed have a design-problem, and that might lead to problems in the long run.
It's called free advice, and sometimes comes unsollicited.
|
|
|
|
|
Quote: It's called free advice, and sometimes comes unsollicited.
So do STDs but that doesn't mean I need them
|
|
|
|
|
Same goes for taxes, but the fact they have a single thing in common doesn't make them comparable.
This is "normal behaviour" when asking an expert for advice. Ask a builder how to build on quicksand. You can expect an answer, and a warning. You can ask the doctor to remove your lungs - and get an answer and a warning. Same goes here.
Even when being paid for the job, I will not shut up whn someone is making a mistake that could cost them dearly. I´m not that much of an ass yet 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
and so i suppose that my question is so hard and not answered 1000 tiemes
