Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    India
    Posts
    12

    Exclamation Unanswered: 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,
    Chock
    Chock

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you 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
    Code:
    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.ItemId
    final tip: never use a reserved word like Date as a column name

    rudy
    http://r937.com/

  3. #3
    Join Date
    Apr 2003
    Location
    India
    Posts
    12

    Hi having problem again ! Help pls

    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.
    Chock

  4. #4
    Join Date
    Apr 2003
    Location
    India
    Posts
    12

    Exclamation Need Help - kindly suggest whether the query is correct or not !

    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.
    Chock

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •