Inline code SQL 2005 - Database Discussion Boards

Re: my SQl

Andrei Straut16-Sep-12 23:54

Andrei Straut

16-Sep-12 23:54

It's very simple.
You try a Google search[^] first, then after you've done that and got some sort of idea of what you need to do you start doing it, and then when and if you get stuck, you come back, you explain your problem clearly and with detail (while providing the smallest possible amount of relevant code), and someone will probably give you an idea. This is how it works.

Until then, I am downvoting your question

Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.

Re: my SQl

Mycroft Holmes17-Sep-12 2:15

Mycroft Holmes

17-Sep-12 2:15

Snarky, bitchy and oh so polite - I love have another 5

Never underestimate the power of human stupidity
RAH

Re: my SQl

Andrei Straut17-Sep-12 2:27

Andrei Straut

17-Sep-12 2:27

10x. I hope I'm making my point across while repeating myself over and over again. If only they'd listen...

Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.

Re: my SQl

Richard MacCutchan17-Sep-12 4:44

Richard MacCutchan

17-Sep-12 4:44

Andrei Straut wrote:
If only they'd listen.

You can try shouting a bit louder, but that just gets you a sore throat. Sigh | :sigh:

One of these days I'm going to think of a really clever signature.

Re: my SQl

Andrei Straut17-Sep-12 5:26

Andrei Straut

17-Sep-12 5:26

Chris doesn't like shouting in the Q&A / programming forums Laugh | :laugh:

I do feel a really strong urge to do it however

Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.

Re: my SQl

Jörgen Andersson17-Sep-12 8:50

Jörgen Andersson

17-Sep-12 8:50

You should actually have it in your sig.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: my SQl

Andrei Straut17-Sep-12 9:45

Andrei Straut

17-Sep-12 9:45

If only you knew how many times a day I think about it... Laugh | :laugh:

Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.

Inline code SQL 2005

Member 916254415-Sep-12 19:52

Member 9162544

15-Sep-12 19:52

I have table A with col1, col2 & table B with col3, col4.

I would like to write a query like

select a.* from A join B on A.col1=B.col3 and A.col2 like col4 + '%'

Is it possible in SQL 2005?

is there a concat function in SQL 2008 or 2005. I have seen it in 2012.

Pls help me.

Thanks in advance

Re: Inline code SQL 2005

Jörgen Andersson16-Sep-12 1:15

Jörgen Andersson

16-Sep-12 1:15

Anything wrong with google today?

Have a look here[^].

String concatenation has been around as long as SQL has. But different dialects have different syntax.
Oracle uses '||', Sql Server uses '+', most implementations can use 'concat(string1, string2)' but not SQL Server AFAIK. Could be wrong about that though.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Inline code SQL 2005

Andrei Straut16-Sep-12 23:55

Andrei Straut

16-Sep-12 23:55

Jörgen Andersson wrote:
Anything wrong with google today?

Same impression here, I'm starting to believe I'm the only person with Google access today Smile | :)

Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.

format integer number in MySQL

Jassim Rahma15-Sep-12 8:35

Jassim Rahma

15-Sep-12 8:35

Hi,

In MySQL, how can I format a number like this

1000000001

where the first 1 is the weekday and the last one is my LAST_INSERT_ID and I am adding just zeroos to make it always 10 digits?

Re: format integer number in MySQL

Graham Breach15-Sep-12 9:28

Graham Breach

15-Sep-12 9:28

How about this:

SQL

SELECT (DAYOFWEEK(NOW()) * 1E9) + @@LAST_INSERT_ID

Database updates

spankyleo12313-Sep-12 23:55

spankyleo123

13-Sep-12 23:55

Hi,

I want to update the column TID with the top most value of TID as long as MID is the same number.

I want to do this (table after update)

PID SID TID MID RID
3 0 0 4 1
3 NULL 0 4 2
3 NULL 0 4 3
3 NULL 0 4 4
3 NULL 0 4 5
3 NULL 0 4 6
3 NULL 0 4 7
3 4 4 8 1
3 NULL 4 8 2
3 NULL 4 8 3
3 NULL 4 8 4

Below is the actual table before update.

PID SID TID MID RID
3 0 0 4 1
3 NULL NULL 4 2
3 NULL NULL 4 3
3 NULL NULL 4 4
3 NULL NULL 4 5
3 NULL NULL 4 6
3 NULL NULL 4 7
3 4 4 8 1
3 NULL NULL 8 2
3 NULL NULL 8 3
3 NULL NULL 8 4

Any suggestion/solutions deeply appreciated.

Thanks

Re: Database updates

Shameel14-Sep-12 1:31

Shameel

14-Sep-12 1:31

Assuming your DB is SQL Server, you can try this (just out of mind, not tried though)

SQL

UPDATE MyTable A SET A.TID = (SELECT MAX(B.TID) FROM MyTable B WHERE A.MID = B.MID)

Re: Database updates

spankyleo12314-Sep-12 2:12

spankyleo123

14-Sep-12 2:12

Thanks for your help.

I have solved it by creating a view of all 'Not NULL' (top most) rows . Then created a stored proc to loop through the view and update the TID. A long procedure though but it did work.

Cheers !

Update with correlated sub query

David Mujica14-Sep-12 3:16

David Mujica

14-Sep-12 3:16

I think you want to do something like this ...

update table A
set tid = 
(select max(tid) 
from TableA
Group By mid)

Check these for references:
http://msdn.microsoft.com/en-us/library/ms177523.aspx

http://data.bangtech.com/sql/sql_update_with_correlated_subquery.htm

Re: Update with correlated sub query

spankyleo12314-Sep-12 3:43

spankyleo123

14-Sep-12 3:43

Hi thanks for your solution and links,

But your query returned an error.

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Re: Update with correlated sub query

Jörgen Andersson14-Sep-12 8:55

Jörgen Andersson

14-Sep-12 8:55

That's because he forgot a small but important condition.
Try this:

SQL

update table A as a1
set tid = 
(select max(tid) 
from TableA as A2
Where A1.mid = A2.mid
Group By mid)

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Update with correlated sub query

David Mujica15-Sep-12 15:11

David Mujica

15-Sep-12 15:11

Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.

Re: Update with correlated sub query

Jörgen Andersson16-Sep-12 1:05

Jörgen Andersson

16-Sep-12 1:05

David Mujica wrote:
Sometimes when you don't have the actual data to test with, you forget some
stuff.

Tell me about it. Roll eyes | :rolleyes:

That's almost something I can put in my CV.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

SUM Problem in MSSQL

Basak Oner11-Sep-12 23:41

Basak Oner

11-Sep-12 23:41

Hi everybody,
I have some numbers. Numbers are
(
0
234,9
-27
0
-55,9
0
0
-59
0
100,72
-26
0
-25,72
0
0
0
-82
0
0
0
0
0
0
-134)

I want to this.
SELECT SUM(FIELDX) FROM TBLX

but result is returning (-2,8421709430404007E-14)
actually resutl is 0 (Zero)
How can i solve this problem?

Thanks.

Başak Öner

Re: SUM Problem in MSSQL

Eddy Vluggen12-Sep-12 0:16

Eddy Vluggen

12-Sep-12 0:16

Basak Oner wrote:
actually resutl is 0 (Zero)

Not if you're a computer, then it's "almost 0". Try rounding the result.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SUM Problem in MSSQL

Basak Oner12-Sep-12 0:27

Basak Oner

12-Sep-12 0:27

sorry I don't understand your message.
I need your detailed explanation.

Thanks.

Re: SUM Problem in MSSQL

Eddy Vluggen12-Sep-12 0:31

Eddy Vluggen

12-Sep-12 0:31

Basak Oner wrote:
sorry I don't understand your message.

Try the ROUND function. This is due to how computers work and how they calculate. You know what a fractional number is, but they do not exist within a computer.

Basak Oner wrote:
I need your detailed explanation.

You're not in school anymore. There's an explanation here[^], good luck.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SUM Problem in MSSQL

Basak Oner12-Sep-12 3:58

Basak Oner

12-Sep-12 3:58

Thanks for your free time.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.