Select
A.field1,B.field2
from DB1.user.tablename A inner join DB2.user.tablename B
on A.ID = B.ID
C|||Actually I do not want to hard code the database name in the query. Can I use the datasources somehow?|||I have not been able to query across datasources.
If you just want to query one db, then you could create a view in one db that queries both dbs.
Nevermind. I didn't realize that the report model definition had to be refreshed.
Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.
You got to go dynamic SQL and use some fancy report datasets and expressions to display or not the columns.
Then you would have to create a report were you define as many columns as possible and control the visibility of these columns based on some expression stored in the Column Visibity hidden property.
Lot of work. May be you would be better off sourcing your report out of a cube.
Philippe
Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.
You got to go dynamic SQL and use some fancy report datasets and expressions to display or not the columns.
Then you would have to create a report were you define as many columns as possible and control the visibility of these columns based on some expression stored in the Column Visibity hidden property.
Lot of work. May be you would be better off sourcing your report out of a cube.
Philippe
quote:|||N indicates that the string after it is a Unicode string. Use N prefix, only
> Hi,
> I have new in SQL server. I am using Access for quite a bit. When I
> put the value under Criteria column, in query, It puts N' before the
> value, what's that mean ' Why they have to do it ?
> Thanks
>
> --
> Ginola
quote:
>N indicates that the string after it is a Unicode string. Use N prefix, onl
y
>when comparing a value against a Unicode column, otherwise you will see
>performance degradation, as an index may not be used.
1.) select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe
2.)
update lta
set lta.LL_RCVD = 'N'
where exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!
update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe|||On 10 Jan 2005 07:25:14 -0800, Roy wrote:
>Hey all,
>strange problem here... query #1 displays 357 records correctly and all
>is well. However, when placed within query #2 as a subquery, it updates
>every single record in the lta table, what's going on here? any
>thoughts?
>
>1.) select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe
>
>2.)
>update lta
>set lta.LL_RCVD = 'N'
>where exists (select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe)
Hi Roy,
The subquery on #2 doesn't reference the outer query. Hence, it returns
the same 357 rows for each row in LTA (from the outer query), so the
EXISTS predicate is always true.
You probably need
UPDATE lta
SET LL_RCVD = 'N'
WHERE EXISTS (SELECT *
FROM new_list
WHERE new_list.voy = lta.voy
AND new_list.poe = lta.poe)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 10 Jan 2005 07:42:14 -0800, Roy wrote:
>Gah! What am I doing wrong? The query below is a modification of query
>#2 above, yet updates 32 records (all of which are NOT located in table
>new_list)??!
>Please help!
>update lta
>set LL_RCVD = 'j'
>from lta a, new_list b
>where a.voy = b.voy AND
>a.poe = b.poe
Hi Roy,
This should affect the same rows as the query I suggested in my reply to
your other message (but this one sets LL_RVCD to 'j'; the other one sets
it to 'N').
An important difference occurs if one row in lta matches more than one row
in new_list (and, since the select returns more rows than are affected by
the update statement, this appears to be the case with your data). The
above query will repeatedly change the values for any lta row that matches
more than one new_list row (with the results being undefined, though in
you case, where the new value is a constant, the results will be as
expected); my version will simply update the rows exactly once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I love you forever, thanks Hugo!
quote:
select cust_name,cust_email from customer,cust_departments
where customer.cust_id=cust_departments.cust_id and
cust_departments.dept_id='1' and cust.notify_ticket='1' group by
cust_departments.cust_id
quote:
> select cust_name,cust_email from customer,cust_departments
> where customer.cust_id=cust_departments.cust_id and
> cust_departments.dept_id='1' and cust.notify_ticket='1' group by
> cust_departments.cust_id
>
quote:
> select cust_name,cust_email from customer,cust_departments
> where customer.cust_id=cust_departments.cust_id and
> cust_departments.dept_id='1' and cust.notify_ticket='1' group by
> cust_departments.cust_id
>
SELECT
a.col1,
max(a.col2), --or other functions...
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
OR
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2
Hope it helps|||Adding all the select columns to the group by can't be the solution as the result will differ when compared to the MySQL query. Am I right ? Please advice.
Also I can't remove the select list item and replace it with aggregate function, as I require that too in my result set.|||imho, MSSQL is here logically correct. Which raw should sql give out if you use group by clause?
id | name
1 a
2 b
2 c
2 d
3 e
If you execute SELECT name, max(id) GROUP BY id, what should come out?
1 a
3 e
2 (?) <-- b, c or d?
So you have to be specific.|||MySQL always gives the first row '2 b'. I need the equivalent for this logic.|||Yes, and that's not definitely. MySQL gives out just the first row, and that's the first row saved in the table. The next time (say, after import-export) it may be an another row.|||SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2
Your above solution gives all the rows, is there any equivalent SQL Server query which fetches the first row from the table
|||SELECT DISTINCT ?|||SELECT distinct MAX(id), name FROM test3 GROUP BY id, name
Above query is not helping me ! It gives all the rows|||SELECT MAX(id), min(name) FROM test3 GROUP BY id
here you'll get the right grouping. If you take min(name) you'll get the first (alphabetically) grouped name row|||Can you show some sample data and the output for that query in MySQL? I'm not sure how it should behave, given that Col1 is both grouped and in an aggregate function, and Col2 is not even grouped... Very odd... If you can show the input you're using and the output you need, I can help you write a query for it. Also, please note whether you're using SQL Server 2000 or 2005. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <smith_kbase@.discussions.microsoft.com> wrote in message news:edc1b9b6-7367-4041-8e24-a17af5660785_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi,I would liked to know the SQL Server equivalent for the below MySQL query :SELECTa.col1,a.col2,max(a.col1)FROM test a,test1 b WHERE a.col2 = b.col2GROUP BY a.col1Thanks,Smitha
