Showing posts with label hai. Show all posts
Showing posts with label hai. Show all posts

Friday, March 9, 2012

Help needed in sp_pkeys

Hai ,

I am in the situation to find the p.key field from a table.So i use

sp_pkeys 'table' .It works nice.

But i want to select the pkey field alone instead of the rest of the
informations supplied by the sp.

How to get the P.key field name alone from a table .Or If u tell the
way to store the results of sp_pkeys 'table' into a table also ok to
me.

With Regards
Raghu"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0402030621.2bb732b@.posting.google.co m...
> Hai ,
> I am in the situation to find the p.key field from a table.So i use
> sp_pkeys 'table' .It works nice.
> But i want to select the pkey field alone instead of the rest of the
> informations supplied by the sp.
> How to get the P.key field name alone from a table .Or If u tell the
> way to store the results of sp_pkeys 'table' into a table also ok to
> me.
>
> With Regards
> Raghu

You didn't mention which version of SQL Server you have, but in 7/2000, you
can use the INFORMATION_SCHEMA views:

select
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
kcu.COLUMN_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and
tc.TABLE_SCHEMA = 'dbo' and
tc.TABLE_NAME = 'MyTable'

Alternatively, you can put the output of sp_pkeys into a table:

create table #keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
go
insert into #keys
exec sp_pkeys 'MyTable'
go

Simon|||
Hai Simon

Thanks for your code.

I am in Sql server 7.0 and i make use of it

Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help needed in an Updation Query

Hai ,
Can any one of, out there help me in the following query.
I have two tables, one table has to get updated with sum of another tables
value. (Explanation below:)
Is it possible to use a single update statement, (i have done it with using
cursors)
Explanation:
Table A:
Col1 Col2 col3 col4
1 100 341 1.0
2 100 342 2.0
Table B:
Col1 Col2 col3 col4
1 100 341 0.1
2 100 341 0.2
3 100 341 0.3
4 100 342 0.4
5 100 342 0.5
6 100 342 0.6
Col2 of Table A PK, and Col2 of Table B id FK similarily
Col3 of Table A PK, and Col3 of Table B id FK
I need to updated Table A's Col4 column with the Sum of Col4(table B)
grouped by Col3 (table b) at one stretch.
If i am not clear in the above explanation , the query will be
Q1)
update A set col4 = col4 + (select sum(col4) from B group by col3 where
b.col2 = 100 and b.col3 = 341) where A.col2 = 100 and A.col3 = 341
Q2)
update A set col4 = col4 + (select sum(col4) from B group by col3 where
b.col2 = 100 and b.col3 = 342) where A.col2 = 100 and A.col3 = 342
when executed separately. I need these two queries in one single shot using
inner join(if possible) . I tried to do it, but unable to do it.
Thanks in advance,
V.Boomesshupdate A
set col4=col4+(select sum(B.col4) from B where B.col2=A.col2 and
B.col3=A.col3)
-oj
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:405E39EE-43BC-4373-B82A-B62B7C5502E3@.microsoft.com...
> Hai ,
> Can any one of, out there help me in the following query.
> I have two tables, one table has to get updated with sum of another tables
> value. (Explanation below:)
> Is it possible to use a single update statement, (i have done it with
> using
> cursors)
> Explanation:
> Table A:
> Col1 Col2 col3 col4
> 1 100 341 1.0
> 2 100 342 2.0
>
> Table B:
> Col1 Col2 col3 col4
> 1 100 341 0.1
> 2 100 341 0.2
> 3 100 341 0.3
> 4 100 342 0.4
> 5 100 342 0.5
> 6 100 342 0.6
>
> Col2 of Table A PK, and Col2 of Table B id FK similarily
> Col3 of Table A PK, and Col3 of Table B id FK
> I need to updated Table A's Col4 column with the Sum of Col4(table B)
> grouped by Col3 (table b) at one stretch.
> If i am not clear in the above explanation , the query will be
> Q1)
> update A set col4 = col4 + (select sum(col4) from B group by col3 where
> b.col2 = 100 and b.col3 = 341) where A.col2 = 100 and A.col3 = 341
> Q2)
> update A set col4 = col4 + (select sum(col4) from B group by col3 where
> b.col2 = 100 and b.col3 = 342) where A.col2 = 100 and A.col3 = 342
> when executed separately. I need these two queries in one single shot
> using
> inner join(if possible) . I tried to do it, but unable to do it.
>
> Thanks in advance,
> V.Boomessh
>
>|||Hai,
Thanks that worked.
V.Boomessh
"oj" wrote:

> update A
> set col4=col4+(select sum(B.col4) from B where B.col2=A.col2 and
> B.col3=A.col3)
>
> --
> -oj
>
> "Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
> news:405E39EE-43BC-4373-B82A-B62B7C5502E3@.microsoft.com...
>
>

Wednesday, March 7, 2012

Help need to write a Query in VB - MsSql !

Hai ,

My database is Ms Sql and I am devolping in VB
The below is my query, it seems to be working but at the last there is some problem. If possible kindly correct the query. Actually the problem is the SalesIn Quantity value is not shown correctly, it seems to be working in SP which I created in Ms Sql, so I tried the same here, I hope with some modifications it could be correctly executed.

sql = "select Item as Itemid,Itemid as SoldItemId,Date as SoldDate,"
sql = sql & "(Qty * Unitcost) as Cost,Qty as SoldQty,ItemId as StockId,"
sql = sql & "Qty as StockQty,ItemId as SalesinId,Qty as SalesInQty from"
sql = sql & " Stock,Sales,StockDetail,SalesIn where Stock.Itemid *= Sales.Itemid AND "
sql = sql & "StockDetail.Itemid = Stock.Itemid AND SalesIn.ItemId = Sales.ItemId AND "
sql = sql & "StockDetail.WareHouse ='" & Text3.Text & "' AND SalesIn.Type ='" & Text4.Text & "' AND "
sql = sql & "Date Between '" & Text1.Text & "' AND '" & Text2.Text & "' Group By"
sql = sql & " Stock.ItemId,StockDetail.ItemId,Sales.ItemId,Sales In.ItemId,Sales.Date,Sales.Qty,Sales.UnitCost,Stoc kDetail.Qty,"
sql = sql & "SalesIn.Qty order by Stock.ItemId"
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

Everything is coming correct except the SalesIn Quantity. If I remove and try means then the other things are showing correctly. I mean the ItemId and the Soldqty and the stockqty and everything is showing correctly here just for my reference I am showing all the ItemId.

Kindly view and reply me.

SalesTable, StockTable, StockDetail, SalesIn are the Four table I am taking here. All the four tables are Linked by the ItemId.

Thank you very much,
Chockyou need to qualify the columns in the SELECT the same way you have qualified them in the GROUP BY

suggestion: switch immediately to JOIN syntax rather than the "old style" joins using that darned asterisk beside the equal sign
select Stock.Item as Itemid
, StockDetail.Itemid as SoldItemId
, Sales.Date as SoldDate
, Sales.Qty * Sales.Unitcost as Cost
, Sales.Qty as SoldQty
, Stock.ItemId as StockId
, StockDetail.Qty as StockQty
, SalesIn.ItemId as SalesinId
, SalesIn.Qty as SalesInQty
from Stock
inner
join StockDetail
on Stock.Itemid = StockDetail.Itemid
left outer
join Sales
on Stock.Itemid = Sales.Itemid
left outer
join SalesIn
on Sales.ItemId = SalesIn.ItemId
where Sales.Date Between 'Text1.Text'
and 'Text2.Text'
and StockDetail.WareHouse = 'Text3.Text'
and SalesIn.Type ='Text4.Text'
group
by Stock.ItemId
, StockDetail.ItemId
, Sales.ItemId
, SalesIn.ItemId
, Sales.Date
, Sales.Qty
, Sales.UnitCost
, StockDetail.Qty
, SalesIn.Qty
order
by Stock.ItemIdfinal tip: never use a reserved word like Date as a column name

rudy
http://r937.com/|||Hi,

I modified the Left outer join as you said, the below is the query which i am currently using.

sql = "select Itemid as StockId,Itemid as StkDetailId,
ItemId as SoldItemId,ItemId as SalesinId,
SellingDate as SoldDate,"
sql = sql & "(SoldQty * UnitCost) as Cost,SoldQty as SoldQty,"
sql = sql & "StockQty as QtyInHnd,SaleinQty as SalesInQty from"
sql = sql & " Stock inner join StockDetail on
Stock.Itemid = StockDetail.Itemid"
sql = sql & " left outer join Sales on Stock.Itemid = Sales.Itemid"
sql = sql & " left outer join SalesIn on Sales.ItemId = SalesIn.Itemid"
sql = sql & " where Sales.Date Between '" & Text1.Text &
"' AND '" & Text2.Text & "' AND StockDetail.WareHouse ='" &
Text3.Text & "' AND "
sql = sql & "SalesIn.Type ='" & Text4.Text & "' Group By "
sql = sql & "Stock.Itemid,StockDetail.Itemid,Sales.Itemid,
SalesIn.Itemid,Sales.SellingDate,Sales.SoldQty,
Sales.UnitCost,StockDetail.QtyinHand,
SalesIn.SalesInQty order by Stock.ItemId

its executing, but I didn't get the output correctly, It didn't shows the record as per the Left Outer Join. Actually I need the output as below

StkId SoldCost QtyInHand SoldQty SaleInQty SDate SalesIn WareHouse
sl001 120 4 2 2 05/01/03 00 01
sl002 0 10 0 0 00 01
sl003 30 2 10 0 05/01/03 00 01
sl004 0 120 0 0 00 01

whethere the Item Sold or not all the Item Id should be listed with their details. The Stock and StockDetail is the Master for the ItemId, So i take that as Inner Join , now what I am getting the oupt put is

sl001 60 4 1 2 05/01/03 00 01
sl001 60 4 1 2 05/01/03 00 01
sl003 30 2 10 0 05/01/03 00 01

if above is not clear, I have attatched the Excel sheet.|||hai friend,

Now I am trying like this, will it work, is the way I am writing is correct or not. kindly let me know also now I will post this to UA,

here I am facing the error

Run-time error '-2147217900(80040e14)':
The Column Prefix 'subquery' doesnot match with a tablename or alias name used in the query.

sql1 = "select distinct ItemId,Description from Stock"
sql2 = "select Itemid,SoldQty,UnitCost,SoldDate from Sales where Sales.SoldDate Between ='" & Text1.Text & "' AND '" & Text2.Text & "'"

sql3 = "select distinct ItemId,WareHouse,StockQty from StockDetail where WareHouse ='" & Text3.Text & "'"

sql4 = "select distinct SalesType,ItemId,Date,ItemId,SalesInQty from Stock Left Join SalesIn on Stock.ItemId = SalesIn.ItemId where SalesInType ='" & Text4.Text & "' AND SalesIn.Date ='" & Text1.Text & "' AND '" & Text2.Text & "'

this is the Subquery

subquery = "select sql1.StockItemId,sql1.Description,sql2.SalesItemId ,"
subquery = subquery & "(sql2.SoldQty * sql2.Cost),sql2.SoldDate,sum(sql3.StockQty),"
subquery = subquery & "sum(sql4.SalesInQty) from "
subquery = subquery & "(((sql1 Left Join Sql2 on sql1.StockItemId=sql2.SaleItemId) Left Join sql3 on sql1.StockItemId = sql3.StockDetail.ItemId) Left Join sql4 on sql1.StockItemId = sql4.SalesInItemId) order by sql1.StockItemId"
this is the main query
mainquery = "select subquery.StockItemId,subquery.Decription,subquery. SoldItemId,"
mainquery = mainquery & "(subquery.SoldQty * subquery.Cost),subquery.SoldDate,subquery.StockQty ,"
mainquery = mainquery & "subquery.SalesInQty"

kindly when you have time view and reply me

Thankyou very much,
Chock.