Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. RyanEK

    Query fails with error of "An expression of non-boolean type specified in a context

    Have you tried running the script with out the comments? I'm not sure how you execute the query but if for some reason it is sent without carriage returns, it is executed as a long one line query. The comments would therefore comment out a lot of the code.
  2. RyanEK

    Should I Be Using ExecuteNonQuery Like This

    Wrong forum, but to answer your question you can indeed use ExecuteNonQuery to return values from the store procedure but you need to set the direction of your return parameters to returnParameter.Direction = ParameterDirection.ReturnValue. Alternatively you can use ExecuteReader which is used...
  3. RyanEK

    Defining General Purpose User-Defined Table Types; Good or Bad Idea?

    You're describing table valued parameters. It's a tried technique and a good solution for your requirements.
  4. RyanEK

    Looping for value in a function

    I think I understand you now. Does this work for you? create function [dbo].[fn_MTD_V1] ( @report_date datetime ) returns int as begin declare @days int declare @endmonth datetime set @endmonth = dateadd(d,-1,dateadd(mm,datediff(m,0,@report_Date)+1,0)) set @days = day(@endmonth) if...
  5. RyanEK

    Looping for value in a function

    Hi, How about the following? create function fn_MTD_V1 ( @report_date datetime, @calendar_id smallint ) returns int as begin declare @days int if (dbo.ABfn_IsNonWorkingDay(dateadd(d,-1,dateadd(mm, datediff(m,0,@report_date)+1,0)),@calendar_id) = 0) begin set @days = datediff(day...
  6. RyanEK

    Using data from tables to dynamically create report column headers

    Hi, any chance you can give sample of your data?
  7. RyanEK

    SQL SERVER 200 and GROUP BY and HAVING with COUNT

    ...2, 1, 1 insert into @temp select 3, 1, 2 insert into @temp select 5, 1, 3 insert into @temp select 6, 1, 3 insert into @temp select 7, 1, 4 select * from @temp t1 join ( select testid, testtypeid from @temp group by testid, testtypeid having count(0) > 1 ) t2 on t1.testid = t2.testid...
  8. RyanEK

    Update records based on Count

    ...insert into @temp select '001','0210901',null insert into @temp select '002','0049703',null insert into @temp select '003','0049806',null update t1 set t1.count = (select count(0) from @temp t2 where t2.ERP_Line_Num = t1.ERP_Line_Num) from @temp t1 select * from @temp
  9. RyanEK

    Adding a New Table/Field and Linking

    What is your question?
  10. RyanEK

    Splitting a string into parts - not as rows.

    ...'efg-2-b-3-02') ;with cte as ( select t2.id, t3.split, ROW_NUMBER() over (partition by t2.id order by t2.id) as num from ( select *, CAST('<i>'+replace(t.col,'-','</i><i>')+'</i>' as XML) as myfilter from @temp t ) t2 cross apply ( select...
  11. RyanEK

    select depart and arrival cities

    Hi, Will this work for you? ;with a as ( SELECT row_number() over (partition by tktnum order by tktnum) as idx, * FROM #XY123 ), b as ( select *, case when exists (select idx from a as a2 where a.tktnum = a2.tktnum and a.arrivalcty = a2.departcty and a2.idx < a.idx) then 1 else 0 end as...
  12. RyanEK

    CASE statement in WHERE clause considering a fiscal year

    This query is only giving me the 09/30/2013, I need the 09/30/2013 and 09/30/2014 paid thru." Your query looks correct. Why are you expecting 09/30/2014 when by your definition, it should only return 09/30/2013 when the current month is 11?
  13. RyanEK

    Design question - adding soft links to tables

    Great responses guys, thank you. I will take on board the use of a view, and look into applying an ordering on the addresses.
  14. RyanEK

    Design question - adding soft links to tables

    Hi, I have a database design question that I was hoping a DB professional could answer. Lets say I have the following one-to-many table relationship between an Account and its Addresses. create table account ( acctkey int, acctname varchar(100), active bit ) create table acctaddress...
  15. RyanEK

    Soft links in Entity Framwork and Code First

    Hi, I have a basic understanding of how fluent api's are used EF and how they define properties and relationships of the tables. My question is how do I go about defining a collection of entities that are soft linked. eg. Say I have a Contact table and a generic [MapTable]. [MapTable] MapID...
  16. RyanEK

    Adding CodeRegionDirective around CodeNamespaceImport using CodeDom

    Hi, I can't seem to add region directives around CodeNamespaceImport. Is is possible to generate this code using CodeDom? namespace MyNamespace { #if POCKETPC using System.Data.SqlServerCe; #else using System.Data.SqlClient; #endif } Thanks Ryan
  17. RyanEK

    Query - Count By Day from Table with Effective Date Field

    ...into @temp values ('B',2010,1,2,7) insert into @temp values ('C',2010,1,2,7) insert into @temp values ('A',2010,1,3,9) ;with mycte as ( select *, CAST(CAST(year AS VARCHAR(4))+ RIGHT('0'+CAST(month AS VARCHAR(2)),2)+ RIGHT('0'+CAST(day AS VARCHAR(2)), 2) AS DATETIME) as tdate from @temp)...
  18. RyanEK

    Sequence numbering

    ...(order by t.seq)), 5) as seq, (select top 1 seq from @temp t1 where t1.data like '6701%' and t1.id < t.id order by t1.id desc) as lastseq from @temp t where data like '3102%') update u set u.seq = c.seq + '_' + c.lastseq from @temp u join cte3 c on u.data = c.data select * from @temp Ryan
  19. RyanEK

    Sequence numbering

    How deep can the level records go? If only 3 as per your example then you could write a query for each level. Otherwise this is one of the few examples where a cursor could be utilised.
  20. RyanEK

    Update Parent/Child Relationship Query

    Hi, Try adding a rowid to the source table... with cteData (rowid, cnfrm_prod_grp_sub_prod_trans_type_assn_id, cnfrm_prod_grp_code, cnfrm_sub_prod_type_code, cnfrm_trans_type_code, parent_assn_id) as ( select ROW_NUMBER() over(order by (select 0)) as rowid...

Part and Inventory Search

Back
Top