Jump to content

Search the Community

Showing results for tags 'sql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 199 results

  1. my focus is across a whole 3 years (156 weeks) of data, beginning January 1st 2011 (or nearest date) and ending December 31st 2013 (or nearest date) – so while we are looking at 3 years total, we will only end up with 52 weeks in our clean version of the data, labelled Week 1 to Week 52 (and not dates).there will be items in this list that launched before 1st Jan 2011, for which part of the launch year will show up in the period we’re focused on. In order that data percentiles (will be calculated at later stage) are not clouded by this, we also need to move any part year data from launches preceding 1st Jan 2011 to the relevant column. To illustrate by example, an item launched in July 2010 will have 26 weeks in 2010 (that fall outside of our 3 yr window) and 26 weeks that fall inside our 3yr window, but the first week we see is actually week 27 of that item’s launch, and not week 1. So the 26 weeks of data that fall into our window, should be shifted out to align with week 27-52 of the ‘clean’ data columns – does this make sense? So i want to have another column next to the WeekNumber column, which will give me the correct number (from 1 to 52) to place the sales value for each product. If then you do a pivot as shown in http://i.imgur.com/VZVIlPb.jpg by arranging the weeknumber column in the Pivots Column area and Sales Value in Pivots Value area, you will see the sales values are then shifted properly to fit the 52 columns. So for each Product, 1] if the sales data starts from 1st week (W 2011 01) and its total weeks count is < 52, then it means some weeks sales data lying in previous year. So this data needs to be numbered to correct week (between 1 to 52), by finding its column: (52 - count of weeks containing data for that product + 1).2] if the sales data does not start from 1st week (W 2011 01) and its total week count is < 52 then move it to 1st week (W 2011 01) column in 1 to 52 weeks i.e. number it as 1, 2, .....<=52.3] if the data starts from 1st week and is 52 weeks data then keep it as it is showing from 1 to 52 weeks. i.e. number it as 1,2,....52. Hope this makes sense. see the linkback for further clarity. http://stackoverflow.com/questions/23825905/shifting-156-weeks-sales-figures-to-52-weeks
  2. I am trying to process a string which contains various data relating to an elearning record; Moodle SCORM suspend_data if you're familiar with it Since I do not have access to server side SQL/CLR functions through the Moodle interface, I am attempting to reference, and extract, the relevant information from the string by explicitly referencing the character locations of the information I need in the string and then converting them as required so they can be summed e.g. SELECT prefix_user.username,........ ((CASE(SUBSTRING(value_rep,237,1)) WHEN 'c' THEN 1 ELSE 0 END) + ........(CASE(SUBSTRING(value_rep,3659,1)) WHEN 'c' THEN 1 ELSE 0 END)) AS "# Videos watched"........FROM ((prefix_user LEFT JOIN (SELECT prefix_scorm_scoes_track.userid, Replace(value,"''","'XX'") AS value_repFROM prefix_scorm_scoes_track WHERE (((prefix_scorm_scoes_track.element)='cmi.suspend_data'))) AS vid ON prefix_user.id = vid.userid The string contains multiple occurrences of the string patterns '','n' and 'nn' where n is any digit 0-9 which I need to replace with the same string pattern e.g. 'XX', so that the total length of the string as a whole remains the same. I Initially thought that the pattern would always exist as '' or 'nn' so it was relatively simple to use a REPLACE(value,"''","XX") I understand it's not possible to use regex in the REPLACE function and have been investigating the use of PATINDEX with STUFF, but understand this is inly suitable for operating on 1 occurrence of a pattern. The report can be output to a CSV for viewing in Excel, so my conclusion is currently to carry out post-processing with Excel VBA where I will have access to loops and other functions. Is what I am trying to achieve possible without the use of a function on the SQL server?
  3. From the tables that I attached, a. Assume Job relation is created. Write the SQL statement that will create the EMP relation (use the structure shown in Table 2) Following is my answer: Create Table EMP (empnum NUMBER(3), lname VARCHAR2(15) CONSTRAINT emp_lname_nn Not Null, fname VARCHAR2(25), mi CHAR(1), hiredate DATE CONSTRAINT emp_hiredate_nn Not Null, enddate DATE, Jobcode CHAR(3), Salary Number(10,2) CONSTRAINT emp_empnum_pk Primary Key (empnum), CONSTRAINT emp_jobcode_fk Foreign Key (jobcode) REFERENCES Job(job_code); b. Write the SQL statement that will enter the first data row into the EMP relation My answer: Insert into EMP values (101, 'O''Connel', 'John', 'mi', '11-Aug-2000', NULL, '502', 3600) For both of this questions, can someone checks with me whether am I doing it correctly? Please point out my mistakes if any, I appreciate your guides.Thank you!
  4. Hello there, some help in this? Is it possible to calculate numeric data in table with data coming from forms? I mean something like this: mysql_query("UPDATE ov SET dt_ob=(dt_ob+'$suma1') WHERE smetka='$debit' ",$con) dt_ob and smetka are coloumns in the table, $suma1 and $debit are data coming from form. The code above do not work to me.
  5. Hello Ladies and Gents, I am new to sql and this forum so please forgive if I don't word my question very well. I'm having diffuculty retrieving data when trying to run more than 2 queries in w3schools course. I was trying to pull from customers, Orders, Products and OrderDetails as a training example to myself. What i'm asking is, if someone is willing, can they please write out a granular query that I can follow. I get different errors when trying to tweek my query. I want to be able to pull 3 or more tables at a time but know I'm missing a fundimental step or steps during my process. SELECT Orders.OrderID, Customers.CustomerName, Orders.EmployeeID, Products.ProductNameFROM OrdersINNER JOIN CustomersON Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID; I know I'm not doing my joins right for sure. I want to be able to pull a customers product, get the employee ID that took the order and the product that was associated with the order and go a bit further. Getting a few examples and explanations would really help me. I'm just not getting it and google isn't giving the answer I need as there are several sql applications that give different nuances of retrieving query information from tables. Thank you community for taking the time to help me out. I just want to get to a point where I can answer a question instead of asking the question. Or maybe someone can point me to a great site that shows different and more detailed examples of running queries such as above. Thank you, -Edgar
  6. I'm trying to organize this SQL query from a packet capture and I'm more of a network/application guy, not so much of a DBA. Can anyone if I got this right and if this makes sense? I think the SQL query is this: SELECT [t0].[SECTIONNAME], [t0].[PARAMETERNAME], [t0].[INTVALUE], [t0].[STRINGVALUE], [t0].[DATEVALUE], [t0].[INFRASTRUCTURAL], [t0].[SiteParametersId] FROM [dbo].[SITE_PARAMETERS] AS [t0] WHERE ([t0.[SECTIONNAME] = @p0) AND ([t0].[PARAMETERNAME] = @p1) After that, is it defining the parameters used in the query like this? @UserId = @p1, @IsRoot = @p2 Here's the whole thing: S E L E C T [ t 0 ] . [ S E C T I O N N A M E ] , [ t 0 ] . [ P A R A M E T E R N A M E ] , [ t 0 ] . [ I N T V A L U E ] , [ t 0 ] . [ S T R I N G V A L U E ] , [ t 0 ] . [ D A T E V A L U E ] , [ t 0 ] . [ I N F R A S T R U C T U R A L ] , [ t 0 ] . [ S i t e P a r a m e t e r s I d ] F R O M [ d b o ] . [ S I T E _ P A R A M E T E R S ] A S [ t 0 ] W H E R E ( [ t 0 ] . [ S E C T I O N N A M E ] = @ p 0 ) A N D ( [ t 0 ] . [ P A R A M E T E R N A M E ] = @ p 1 ) @ 4@ @ p 0 n v a r c h a r ( 3 ) , @ p 1 n v a r c h a r ( 1 0 ) @ p 0 4 W e b @ p 1 4 M a x Q u e r i e s [ < d 4 S E C T I O N N A M E d 4 P A R A M E T E R N A M E & I N T V A L U E 4 S T R I N G V A L U E o D A T E V A L U E 2 I N F R A S T R U C T U R A L S i t e P a r a m e t e r s I d W e b M a x Q u e r i e s 0 y @ 4 E X E C @ R E T U R N _ V A L U E = [ d b o ] . [ p _ S a l e s O r d e r s _ S e a r c h ] @ M a x R e s u l t s = @ p 0 , @ U s e r I d = @ p 1 , @ I s R o o t = @ p 2 , @ L i s t C u s t o m e r = @ p 3 , @ P r o j e c t N u m b e r = @ p 4 , @ L a s t M o d i f i e d B y N a m e = @ p 5 , @ C o n t r a c t M a n a g e r M a i l I D = @ p 6 , @ P r o j e c t M a n a g e r M a i l I D = @ p 7 , @ K A M M a i l I D = @ p 8 , @ R e s p o n s i b l e S a l e s A d m i n M a i l I D = @ p 9 , @ O r d e r M a n a g e r M a i l I D = @ p 1 0 , @ S h i p m e n t R e s p M a i l I D = @ p 1 1 , @ C a r a t C o d e = @ p 1 2 , @ I s A r c h i v e d = @ p 1 3 , @ L i s t S o u r c e A p p l i = @ p 1 4 , @ L i s t P r o c e s s S t a t u s = @ p 1 5 , @ L i s t E d i t i o n S t a t u s = @ p 1 6 , @ L i s t B u s i n e s s U n i t = @ p 1 7 , @ L i s t L e g a l U n i t = @ p 1 8 , @ L i s t C u s t o m e r C o u n t r y = @ p 1 9 , @ A l l i a n c e C r e a t i o n F r o m = @ p 2 0 , @ A l l i a n c e C r e a t i o n T o = @ p 2 1 , @ E R P S e n t F r o m = @ p 2 2 , @ E R P S e n t T o = @ p 2 3 , @ C u s t o m e r P O R e c e i p t D a t e F r o m = @ p 2 4 , @ C u s t o m e r P O R e c e i p t D a t e T o = @ p 2 5 , @ L a s t M o d i f i c a t i o n D a t e F r o m = @ p 2 6 , @ L a s t M o d i f i c a t i o n D a t e T o = @ p 2 7 , @ I s I n v o i c e d = @ p 2 8 , @ I s C a n c e l l e d = @ p 2 9 , @ C u s t o m e r P O I D = @ p 3 0 , @ A l l i a n c e O r d e r I d = @ p 3 1 , @ E r p S a l e s O r d e r I d = @ p 3 2 , @ C u s t o m e r C a t a l o g N u m b e r = @ p 3 3 , @ C a t a l o g N u m b e r = @ p 3 4 , @ E R P R e f e r e n c e N u m b e r = @ p 3 5 , @ L i s t C a t e g o r y = @ p 3 6 , @ O p p o r t u n i t y R e f e r e n c e = @ p 3 7 , @ C u s t o m e r P r o g r a m C o d e = @ p 3 8 , @ A g g r e g a t i o n R e f e r e n c e I d = @ p 3 9 , @ B a c k O f f i c e O A M a i l I d = @ p 4 0 , @ A n t i c i p a t i o n O r d e r I d = @ p 4 1 , @ C r e a t i o n D a t e O f L a s t E d i t i o n F r o m = @ p 4 2 , @ C r e a t i o n D a t e O f L a s t E d i t i o n T o = @ p 4 3 , @ O f f e r R e f e r e n c e = @ p 4 4 , @ A l l i a n c e F A C D R I d = @ p 4 5 , @ L i s t B i l l i n g P l a n C o n d i t i o n s S t a t u s e s = @ p 4 6 , @ O r d e r D e s c r i p t i o n = @ p 4 7 , @ N o t V i s i b l e F o r C D R = @ p 4 8 , @ L i s t A t t a c h m e n t T y p e s = @ p 4 9 4 @ p 0 i n t , @ p 1 i n t , @ p 2 b i t , @ p 3 n v a r c h a r ( 4 0 0 0 ) , @ p 4 n v a r c h a r ( 4 0 0 0 ) , @ p 5 n v a r c h a r ( 4 0 0 0 ) , @ p 6 n v a r c h a r ( 4 0 0 0 ) , @ p 7 n v a r c h a r ( 4 0 0 0 ) , @ p 8 n v a r c h a r ( 4 0 0 0 ) , @ p 9 n v a r c h a r ( 4 0 0 0 ) , @ p 1 0 n v a r c h a r ( 4 0 0 0 ) , @ p 1 1 n v a r c h a r ( 4 0 0 0 ) , @ p 1 2 n v a r c h a r ( 4 0 0 0 ) , @ p 1 3 b i t , @ p 1 4 n v a r c h a r ( 7 ) , @ p 1 5 n v a r c h a r ( 5 ) , @ p 1 6 n v a r c h a r ( 9 5 ) , @ p 1 7 n v a r c h a r ( 6 6 6 ) , @ p 1 8 n v a r c h a r ( 7 6 3 ) , @ p 1 9 n v a r c h a r ( 8 7 2 ) , @ p 2 0 d a t e t i m e , @ p 2 1 d a t e t i m e , @ p 2 2 d a t e t i m e , @ p 2 3 d a t e t i m e , @ p 2 4 d a t e t i m e , @ p 2 5 d a t e t i m e , @ p 2 6 d a t e t i m e , @ p 2 7 d a t e t i m e , @ p 2 8 b i t , @ p 2 9 b i t , @ p 3 0 n v a r c h a r ( 4 0 0 0 ) , @ p 3 1 n v a r c h a r ( 4 0 0 0 ) , @ p 3 2 n v a r c h a r ( 4 0 0 0 ) , @ p 3 3 n v a r c h a r ( 4 0 0 0 ) , @ p 3 4 n v a r c h a r ( 4 0 0 0 ) , @ p 3 5 n v a r c h a r ( 4 0 0 0 ) , @ p 3 6 n v a r c h a r ( 1 7 ) , @ p 3 7 n v a r c h a r ( 4 0 0 0 ) , @ p 3 8 n v a r c h a r ( 4 0 0 0 ) , @ p 3 9 n v a r c h a r ( 4 0 0 0 ) , @ p 4 0 n v a r c h a r ( 4 0 0 0 ) , @ p 4 1 n v a r c h a r ( 4 0 0 0 ) , @ p 4 2 d a t e t i m e , @ p 4 3 d a t e t i m e , @ p 4 4 n v a r c h a r ( 4 0 0 0 ) , @ p 4 5 n v a r c h a r ( 4 0 0 0 ) , @ p 4 6 n v a r c h a r ( 1 5 ) , @ p 4 7 n v a r c h a r ( 4 0 0 0 ) , @ p 4 8 b i t , @ p 4 9 n v a r c h a r ( 2 4 ) , @ R E T U R N _ V A L U E i n t o u t p u t @ p 0 & @ p 1 & f) @ p 2 h @ p 3 4 @ p 4 4 @ p 5 4 @ p 6 4 @ p 7 4 @ p 8 4 @ p 9 4 @ p 1 0 4 @ p 1 1 4 @ p 1 2 4 @ p 1 3 h @ p 1 4 4 1 , 2 , 3 , 4 @ p 1 5 4 1 , 2 , 3 @ p 1 6 4 1 , 4 , 1 4 , 1 5 , 3 6 , 5 , 3 5 , 1 0 , 2 0 , 2 5 , 2 6 , 2 7 , 2 , 8 , 1 2 , 1 8 , 2 8 , 6 , 1 1 , 1 6 , 2 2 , 1 3 , 3 , 7 , 9 , 1 7 , 1 9 , 2 9 , 2 1 , 3 7 , 3 1 , 3 3 , 3 2 , 3 4 , 2 3 @ p 1 7 4 44 1 , 2 6 2 , 2 6 0 , 2 6 1 , 2 3 6 , 2 5 8 , 2 5 9 , 2 3 7 , 2 3 8 , 2 3 9 , 2 4 0 , 2 4 1 , 2 4 2 , 2 4 3 , 2 4 4 , 2 4 5 , 2 4 6 , 2 4 7 , 2 4 8 , 2 4 9 , 2 5 0 , 2 5 1 , 2 5 2 , 2 5 3 , 2 5 4 , 2 6 3 , 2 6 4 , 2 6 5 , 2 6 6 , 1 5 1 , 5 , 1 9 2 , 6 , 7 , 1 6 0 , 1 6 7 , 1 8 5 , 1 7 6 , 1 5 4 , 1 4 5 , 1 5 2 , 8 , 9 , 2 2 4 , 1 0 , 1 1 , 2 0 3 , 2 3 5 , 1 2 , 1 4 , 2 2 5 , 1 6 , 1 8 , 2 0 , 2 2 , 2 4 , 1 4 1 , 1 9 3 , 2 6 , 2 8 , 1 4 6 , 1 4 7 , 2 0 0 , 1 9 4 , 2 9 , 3 0 , 3 1 , 3 2 , 1 6 1 , 1 5 7 , 1 5 8 , 1 6 2 , 3 3 , 3 4 , 3 5 , 2 0 4 , 3 6 , 3 7 , 1 9 5 , 3 9 , 4 0 , 4 1 , 1 0 3 , 1 6 3 , 2 2 6 , 1 7 8 , 1 9 6 , 2 0 5 , 2 0 6 , 2 0 7 , 1 5 5 , 1 6 9 , 1 9 7 , 2 0 8 , 2 0 9 , 2 1 0 , 1 7 7 , 2 3 4 , 1 9 8 , 1 8 2 , 1 7 4 , 2 1 1 , 1 5 9 , 2 5 5 , 2 5 6 , 1 7 3 , 1 8 0 , 2 5 7 , 2 1 2 , 2 1 3 , 2 1 4 , 2 1 5 , 1 8 7 , 2 1 6 , 2 1 7 , 2 1 8 , 2 2 2 , 2 1 9 , 2 2 0 , 1 9 9 , 4 2 , 1 4 8 , 1 4 9 , 4 3 , 4 4 , 4 5 , 1 0 9 , 1 7 2 , 1 7 1 , 1 8 3 , 2 2 7 , 2 2 8 , 1 8 1 , 4 6 , 2 2 9 , 1 1 2 , 4 8 , 1 5 0 , 2 3 0 , 2 3 1 , 2 3 2 , 4 9 , 5 0 , 1 7 9 , 1 1 8 , 1 1 9 , 1 2 0 , 1 2 1 , 1 2 3 , 1 5 6 , 5 1 , 5 3 , 1 7 5 , 5 5 , 5 7 , 5 9 , 6 1 , 1 6 4 , 1 6 8 , 2 2 1 , 6 3 , 6 4 , 6 5 , 6 6 , 1 8 4 , 1 4 2 , 2 3 3 , 1 7 0 , 1 8 6 , 1 9 1 , 1 8 8 , 1 8 9 , 1 9 0 , 1 4 3 , 1 4 4 , 1 6 5 , 1 6 6 , 2 0 1 , 2 2 3 , 2 0 2 @ p 1 8 4 4 3 2 , 3 4 9 , 1 5 0 , 4 3 1 , 9 6 , 1 5 2 , 9 7 , 1 5 4 , 1 5 5 , 7 , 1 5 9 , 1 6 2 , 4 1 0 , 1 6 3 , 3 7 2 , 3 6 0 , 1 6 4 , 9 9 , 1 6 6 , 3 9 7 , 4 , 1 7 0 , 1 7 2 , 1 0 1 , 1 7 4 , 1 7 6 , 1 7 7 , 1 7 8 , 1 8 0 , 1 8 1 , 3 5 5 , 3 4 8 , 3 7 3 , 3 5 1 , 3 5 2 , 3 4 7 , 3 4 2 , 1 8 2 , 1 8 3 , 3 7 4 , 1 8 4 , 1 8 5 , 1 8 7 , 1 8 9 , 1 0 2 , 1 9 2 , 1 0 3 , 1 9 4 , 1 0 4 , 1 9 7 , 1 5 , 1 0 5 , 2 0 0 , 4 2 8 , 2 0 2 , 2 0 3 , 2 0 6 , 2 0 7 , 3 , 1 0 7 , 2 1 4 , 2 1 5 , 3 5 7 , 2 1 6 , 4 0 6 , 2 1 7 , 1 0 8 , 2 , 2 2 1 , 4 2 9 , 2 2 2 , 2 2 4 , 2 2 5 , 3 9 4 , 3 6 9 , 2 2 6 , 5 , 2 2 9 , 2 3 0 , 2 3 3 , 2 3 6 , 2 3 7 , 2 4 0 , 2 4 4 , 2 4 6 , 2 4 7 , 2 4 8 , 1 1 1 , 2 5 0 , 4 3 0 , 2 5 2 , 2 5 3 , 3 6 3 , 2 5 4 , 4 1 5 , 2 5 5 , 3 7 5 , 3 6 4 , 1 1 2 , 2 5 8 , 2 5 9 , 2 6 1 , 2 6 2 , 2 6 4 , 3 9 5 , 1 1 3 , 2 6 7 , 3 5 8 , 4 2 7 , 1 1 4 , 2 6 9 , 3 6 5 , 3 4 1 , 2 7 5 , 3 6 8 , 3 3 9 , 2 7 7 , 2 7 8 , 2 7 9 , 2 8 0 , 2 8 1 , 2 8 2 , 3 7 0 , 1 1 5 , 4 0 0 , 2 8 4 , 2 8 5 , 2 8 6 , 3 6 6 , 2 9 0 , 1 4 , 2 9 2 , 1 1 7 , 2 9 5 , 3 6 2 , 2 9 7 , 2 9 8 , 2 9 9 , 3 7 1 , 1 2 , 3 5 6 , 3 0 1 , 1 1 9 , 3 0 5 , 3 0 6 , 3 0 7 , 1 2 0 , 4 2 5 , 3 0 9 , 1 2 1 , 4 0 2 , 3 7 7 , 3 6 1 , 3 1 1 , 3 1 2 , 3 1 4 , 3 1 5 , 1 2 2 , 4 2 6 , 3 6 7 , 3 1 7 , 3 1 9 , 4 3 4 , 3 2 1 , 3 7 9 , 3 2 2 , 4 2 4 , 3 2 3 , 4 2 2 , 3 2 4 , 3 2 5 , 3 2 6 , 4 0 4 , 3 8 0 , 3 8 1 , 3 8 2 , 3 8 3 , 3 8 4 , 3 8 5 , 3 8 6 , 3 4 3 , 3 8 8 , 3 4 4 , 3 8 9 , 3 9 0 , 3 9 1 , 3 4 6 , 3 2 9 , 3 3 0 , 3 3 2 , 3 4 5 , 3 3 4 , 3 3 5 , 3 4 0 , 3 3 7 @ p 1 9 4 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 F , 9 , 1 0 , 1 1 , 1 2 , 1 3 , 1 4 , 1 5 , 1 6 , 1 7 , 1 8 , 1 9 , 2 0 , 2 1 , 2 2 , 2 3 , 2 4 , 2 5 , 2 6 , 2 7 , 2 8 , 2 9 , 3 0 , 3 1 , 3 2 , 3 3 , 3 4 , 3 5 , 3 6 , 3 7 , 3 8 , 3 9 , 4 0 , 4 1 , 4 2 , 4 3 , 4 4 , 4 5 , 4 6 , 4 7 , 4 8 , 5 0 , 5 1 , 5 2 , 5 3 , 5 4 , 5 5 , 5 6 , 5 7 , 5 8 , 5 9 , 6 0 , 6 1 , 6 2 , 6 3 , 6 4 , 6 5 , 6 6 , 6 7 , 6 8 , 6 9 , 7 0 , 7 1 , 7 2 , 7 3 , 7 4 , 7 5 , 7 6 , 7 7 , 7 8 , 7 9 , 8 0 , 8 1 , 8 2 , 8 3 , 8 4 , 8 5 , 8 6 , 8 7 , 8 8 , 8 9 , 9 0 , 9 1 , 9 2 , 9 3 , 9 4 , 9 5 , 9 6 , 9 7 , 9 8 , 9 9 , 1 0 0 , 1 0 1 , 1 0 2 , 1 0 3 , 1 0 4 , 1 0 5 , 1 0 6 , 1 0 7 , 1 0 8 , 1 0 9 , 1 1 0 , 1 1 1 , 1 1 2 , 1 1 3 , 1 1 4 , 1 1 5 , 1 1 6 , 1 1 7 , 1 1 8 , 1 1 9 , 1 2 0 , 1 2 1 , 1 2 2 , 1 2 3 , 1 2 4 , 1 2 5 , 1 2 6 , 1 2 7 , 1 2 8 , 1 2 9 , 1 3 0 , 1 3 1 , 1 3 2 , 1 3 3 , 1 3 4 , 1 3 5 , 1 3 6 , 1 3 7 , 2 4 4 , 1 3 8 , 1 3 9 , 1 4 0 , 1 4 1 , 1 4 2 , 1 4 3 , 1 4 4 , 1 4 5 , 1 4 6 , 1 4 7 , 1 4 8 , 1 4 9 , 1 5 0 , 1 5 1 , 1 5 2 , 1 5 3 , 1 5 4 , 1 5 5 , 1 5 6 , 1 5 7 , 1 5 8 , 1 5 9 , 1 6 0 , 1 6 1 , 1 6 2 , 1 6 3 , 1 6 4 , 1 6 5 , 1 6 6 , 1 6 7 , 1 6 8 , 1 6 9 , 1 7 0 , 1 7 1 , 1 7 2 , 1 7 3 , 1 7 4 , 1 7 5 , 1 7 6 , 1 7 7 , 1 7 8 , 1 7 9 , 1 8 0 , 1 8 1 , 1 8 2 , 1 8 3 , 1 8 4 , 1 8 5 , 2 4 3 , 1 8 6 , 1 8 7 , 1 8 8 , 1 8 9 , 1 9 0 , 1 9 1 , 1 9 2 , 1 9 3 , 1 9 4 , 1 9 5 , 1 9 6 , 1 9 7 , 1 9 8 , 1 9 9 , 2 0 0 , 2 0 1 , 2 0 2 , 2 0 3 , 2 0 4 , 2 0 5 , 2 0 6 , 2 0 7 , 2 0 8 , 2 0 9 , 2 1 0 , 2 1 1 , 2 1 2 , 2 1 3 , 2 1 4 , 2 1 5 , 2 1 6 , 2 1 7 , 2 4 5 , 2 1 8 , 2 1 9 , 2 2 0 , 2 2 1 , 2 2 2 , 2 2 3 , 2 2 4 , 2 2 5 , 2 2 6 , 2 2 7 , 2 2 8 , 2 2 9 , 2 3 0 , 2 3 1 , 2 3 2 , 2 3 3 , 2 3 4 , 2 3 5 , 2 3 6 , 2 3 7 , 2 3 8 , 2 3 9 , 2 4 0 , 2 4 1 , 2 4 2 , 2 4 8 @ p 2 0 o @ p 2 1 o @ p 2 2 o @ p 2 3 o @ p 2 4 o @ p 2 5 o @ p 2 6 o @ p 2 7 o @ p 2 8 h @ p 2 9 h @ p 3 0 4 @ p 3 1 4 @ p 3 2 4 @ p 3 3 4 @ p 3 4 4 @ p 3 5 4 @ p 3 6 " 4" 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 @ p 3 7 4 @ p 3 8 4 @ p 3 9 4 @ p 4 0 4 @ p 4 1 4 @ p 4 2 o @ p 4 3 o @ p 4 4 4 @ p 4 5 4 @ p 4 6 4 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 @ p 4 7 4 @ p 4 8 h @ p 4 9 0 40 0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 1 8 , 2 1 , 2 6 @ R E T U R N _ V A L U E &
  7. Am designing a simple php forum platform using PHP and MYSQL to insert the data into myql database. But i use to get this kind of --[You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id='4'' at line 1] PLEAS WHAT SHOULD I DO
  8. birbal

    opinion about ORM

    Do you use ORM in your production or commercial application? I think ORMs are good for maintaining OO relation with datbase, But it has its limitation. For DML ORM is great for faster development and minimize duplication of code. But for DQL I think it is hard to do complex SQLs with ORM. Raw SQL would go along well. I have read somewhere it is not good to fit something like RDBMS to OO which it is not. Anyway i dont know how many open source and commercial application uses it. It looks like OSCommerce prefer to not use ORM. I have own written active record like ORM which uses Reflection to map the classess. It works well with DML and DQL (one to one,one to many relations). But have some issues with many to many relations (It could be resolved though i think). Though it serving well , I am not much happy with it as i expected. I can even see if i use Raw SQL i could even mnimize (for DQL) one or two query than that ORM. Though that helps to reduce writing code manually a lot. Conclussion, I have decided to use it in DML part. and change the DQL part to be raw SQL specially in many to many relations table and complex queries. From this disappointment i look into other ORM like doctrine or propel. But they have learning curve and fairly complex. They I think do lot of things than i need. Currently I am not in position to invest time to read and grasp whole doctrine/propel and at the end , find the same conclussion as above. I know benfits and drawbacks of ORM, still hearing any experience regarding this would helpful. Another query does Doctrine supports temporary tables or not ? Even I tried to install doctrine. But composer showing some error. Before i resolved it and invest time. i would like to hear your story with ORM.
  9. i need help with useing one table to count another tables enteries
  10. Hi, I would like to use <input type=file> to allow user to upload file (pictures, pdf...) and save them as varinary on SQL server. I can do it using asp.net but want to use html5 and javascipt. I imagine it will take some sort of ajax call to a web service written in vb.net. Does someone have an example?or a resoource? My specific questions are how to get the file from the <imput> and pass that file to web service? Thanks John
  11. I am new to sql and want to do some practice stuff hence requires list of two tables which having relation with each other for example product and order table. these are the two tables having relation with each other through productid column. producttable ordertable productid productname price color weight orderid productid customer
  12. Hi guys, i need to finish an assignment but keep getting the error: ERROR 1111 (HY000): Invalid use of group function The assignment goes as follows: Give a list of cruises (c_code, description) for all cruises and their number of passengers that have more passengers then average. There are 3 tables in the database: Booking, cruises and passengers. My code is :SELECT C_CODE, C_DESCRIPT, COUNT(DISTINCT(B_PASS)) FROM CRUISE, BOOKING WHERE (CRUISE.C_CODE =BOOKING.B_CRUISE) GROUP BY C_CODE HAVING COUNT(DISTINCT(B_PASS)) > AVG(COUNT(DISTINCT(B_PASS))); Obviously I'm doing something wrong.. Does anyone know what I'm doing wrong ? thanks in advance, Omoruyi
  13. I have a database of 5 tables. 1st table is my customer table. 2nd is my crust table. 3rd is my toppings table. 4th is my sides table. 5th is my order table. My crust, toppings and sides table all have a price column and I am trying to get them to add together on my order table total cost column. What function would be the best to use to accomplish getting the crust price toppings price and sides price to add up on the order table Total cost column? Here is my table schema: Crust TableCrustID Primary KeyCrustnameCrustsizeCrustprice moneyToppings TableToppingID Primary KeyToppingNameToppingPrice moneySides TableSideID Primary KeySideNameSidePortionSidePrice moneyCustomer TableCustomerID Primary KeyFnameLnameStreetCityStateZipPhonenumberemailaddress Order TableOrderId Primary Key INTCustomer Foreign Key INTCrust Foreign Key INTToppings Foreign Key INT Sides foreign key int TotalCost money
  14. My goal is to have users fill out three fields and click on a button that will check an sql table to see if there is a record with those three fields already in the database. I'm not 100% sure, but I think my issue is in my C# code calling the procedure. I have tested my stored procedure from sql and it works. I've tried a few things, but nothing is working. The code below is one of my attempts that isn't working. I'm not sure, but when I step through the program it doesn't appear to be calling the procedure (though I could be wrong and the problem is elsewhere). SqlConnection conn = new SqlConnection(GetConnectionString()); try { conn.Open(); SqlCommand cmd = new SqlCommand("SubmitCheck", conn); SqlParameter sqlCheck = new SqlParameter("@result", DbType.Int16); cmd.Parameters.AddWithValue("@pfName", fName.Text); cmd.Parameters.AddWithValue("@plName", lName.Text); cmd.Parameters.AddWithValue("@pssn", ssn.Text); cmd.CommandType = CommandType.StoredProcedure; bool check = Convert.ToBoolean(cmd.ExecuteScalar()); if (check) { checkEntrylbl.Text = "You have not yet completed this form. Please complete the form."; checkEntrylbl.Visible = true; lName.Enabled = false; fName.Enabled = false; ssn.Enabled = false; submitButton.Enabled = true; } else { checkEntrylbl.Text = "You have completed this form. You do not have to fill it out again."; checkEntrylbl.Visible = true; } } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { conn.Close(); } Here is the stored procedure code. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE SubmitCheck -- Add the parameters for the stored procedure here @pfName varchar(50), @plName varchar(50), @pssn int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Checks if there is an entry with the same first name, last name, and last four of ssn. IF (EXISTS(SELECT fName, lName, ssn FROM dbo.compassSurvey WHERE @pfName = fName AND @plName = lName AND @pssn = ssn)) BEGIN RETURN (0); END BEGIN RETURN (1); END END Thanks for any suggestions.
  15. Anyone have any suggestions.?? I have Table <customers> I need to query the data points in Column ID ( data type_ Int ) and Column Lot (data type_Int) FROM customers but what my query is doing is adding this two data points up. and I don't want a SUM result. ex. if ID = 100 and Lot = 543 my query result = 643 what I am looking for is IF ID=100 and Lot = 543 my query result should be= 100543 SELECT customers.ID + ' ' +customesr.Lot AS combined FROM customers ANY SUGGESTIONS PLEASE!!!
  16. Anyone have any suggestions.?? I have Table <customers> I need to query the data points in Column ID ( data type_ Int ) and Column Lot (data type_Int) FROM customers but what my query is doing is adding this two data points up. and I don't want a SUM result. ex. if ID = 100 and Lot = 543 my query result = 643 what I am looking for is IF ID=100 and Lot = 543 my query result should be= 100543 SELECT customers.ID + ' ' +customesr.Lot AS combined FROM customers ANY SUGGESTIONS PLEASE!!!
  17. Soooo I am taking a class called Electronic Commerce. My professor is is requiring us to make a website "selling" a product (not for real) and the website must use html 5, php and sql. He is not going to waste class time teaching us these because this is a "capstone" class and we should already be familiar. Well Html 5 I am ok with, but SQL and PHP, I am so lost. As far as I can figure out, the SQL is for my database. I have already gathered the jpgs, made a database of the spices with prices I will sell. I am not really sure how to attack this project and any help will by greatly appreciated. There is no mention of this project in his syllabus and folks that have taken his class before said that he doesn't even grade this project, I really just want to have something to turn in just in case. Thanks
  18. Hello.I have this code $get_cart_info = mysql_query("select c.*,g.*,m.* from cart as c left join games as g on c.tid = g.id left join movies as m on c.tid=m.id where c.user = {$CURUSER['id']} GROUP BY c.tid") or die(mysql_error()); But what I need is when c.type is movie its only takes info from movies tabel, but still have to take everything out, because I both have movies and games in cart..Can it be done, or do I have to trying something else?
  19. Sir; I have developed a website for my Department of Science, which recently is hosted in USA. The website is programmed not to accept any form submissions after 8 pm. Also it gets redirected to "Site is down" page on Saturdays and Sundays. It was OK while the site was over in-house server. Now that its in USA, because the time zone now is different, it does not accept the forms after 8 pm in USA, which is 8 am here in India. Site does not accept forms from 8 am to 12 noon here in India. Also weekend redirect to "Site is down" is of no use due to date being different in USA and in India. Is there any code in ASP or procedure in SQL 2008 R2, whereby I can convert the USA time on the server in USA to Indian Standard Time ( IST ) so that the required functioning of the site happens as per the Indian Time and not as per the time in USA? We are on shared server so the server time can not be changed. Thanks DAMODAR G AGNI
  20. Hi,I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required. Really appreciate your expertise. Sorry if my explaination below is quite lengthy. SCENARIO : I am required to query from 2 tables for those unique record that meets both conditions below:-1. Status is 1 @ max (trans_id), paychnl = CC2. Status is 2 @ max (trans_id), paychnl = A or B FYR, 2 tables and respective columns to query are as below:-table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODEtable CHFF --> col STATUS,PAYCHNL FYI, status refers to the paychnl method status:-==> 1 means the current paychnl method==> 2 means the previous paychnl method paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE. Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID. Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODEtable CHFF --> col STATUS,PAYCHNL SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B'))) However, the script above returns :-1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID Samples of the result as below. ID STATUS TRANS_ID PAYCHNL TRANSDATE EFFDATE TRANSCODE 5188 1 712 CC 130829 20130920 T522 9361 1 64 CC 130816 20140813 T522 7853 1 153 CC 130820 20130814 T522 8949 1 51 CC 130812 20130801 T522 8949 2 51 B 130812 20130801 T522 1908 1 455 CC 130516 20131129 T522 1908 2 455 A 130516 20131129 T522 1908 1 409 CC 111019 20111129 T522 1908 2 409 A 111019 20111129 T522 1908 1 404 CC 110929 20111129 T522 1908 2 404 B 110929 20111129 T522
  21. LAs

    Sort using CASE and LEN

    Hello guys, i need some experienced info here.. i need to sort selected data, thats why i used case like: SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1WHEN CustomerName LIKE '%b%' THEN 2ELSE 3END Everythings fine here, but I need to sort each sector (1, 2 and 3) seperately by CustomerName`s lenght. How to do that? any ideas ?
  22. I have a nifty query that pulls data by orders.orderid > '85000' (and I update that number every week based on orders.) But I want it to automatically pull the most recent 3,000 orders instead of changing that number all the time, so I was hoping to set a variable which is Max(order.orderid)-3000, thus pulling the last 3000 order records. Here is my static select: SELECT Orders.OrderID, Orders.OrderStatus, Orders.CustomerID, Orders.ShippingMethodID, Orders.BillingFirstName, Orders.BillingLastName, Orders.BillingAddress1, Orders.BillingAddress2, Orders.BillingCity, Orders.BillingState, Orders.BillingPostalCode, Orders.PaymentAmount, Orders.OrderDate, Orders.ShipDate, Orders.LastModified, fraud.score, CAST(Orders.OrderID AS VARCHAR) AS 'za_orderid_full', CAST(Orders.CustomerID AS VARCHAR) AS 'za_custid_full'FROM Orders JOIN Fraud ON Fraud.OrderID = Orders.OrderIDWHERE Orders.ShipDate IS NULL AND Orders.OrderID > '85000' AND Orders.OrderStatus <> 'Cancelled' Can someone tell me how to declare and set that variable? Or suggest another way to accomplish this? Your help is greatly appreciated.
  23. I have the following sql query in vb.net and ms access , how do I create a table from the query result ? SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1 I have tried but it does not work create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1 any help would be appreciated
  24. SqlBeginner

    Stored Procedure

    CREATE PROCEDURE
  25. I created an asp.net webform that submits to an sql database on an sql server. The web server is Windows server 2012. The page successfully submits when I run the page using Visual Studio 2012, but when I run it on the web server (not using Visual Studios) I get the error message below when I click submit. It says login failed for user domainservername$, but the username in the connection string is user we created for the sql database. It uses Windows Authentication. I setup the web server for asp.net. I haven't done it before, so I assume the issue is there or in the connection string, though I'm not sure. The connection string is below. I'm new to asp.net, c#, and sql, so I'm learning as I go in this project. Let me know if you need anymore information. Thanks for any suggestions. <connectionStrings> <add name="MyConsString" connectionString="Server=sqlserver; Database=database; User Id=username; password=password; Trusted_Connection=Yes;" providerName="System.Data.SqlClient" /> </connectionStrings> Server Error in '/' Application. -------------------------------------------------------------------------------- Insert Error:Login failed for user 'domainservername$'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Exception: Insert Error:Login failed for user 'domainservername$'. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [Exception: Insert Error:Login failed for user 'domainservername$'.] Survey.WebForm1.ExecuteInsert(EventArgs e) in servernamec$inetpubSurvey2Surveydefault.aspx.cs:489 Survey.WebForm1.Button1_Click(Object sender, EventArgs e) in servernamec$inetpubSurvey2Surveydefault.aspx.cs:39 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804
×
×
  • Create New...