Database

Link to Microsoft Virtual System

31-Oct-14 6:33

The C# forum may well be the correct forum. He hasn't answered my question there.

Shweta N Mishra31-Oct-14 3:41

Shweta N Mishra

31-Oct-14 3:41

Hi
Does anybody have any idea about the system/Link, Where Microsoft provides an Environment and we can do experiments , Work with database, run our query etc.

Re: Link to Microsoft Virtual System

Vijay Gill31-Oct-14 3:47

Vijay Gill

31-Oct-14 3:47

Do you mean Azure?

Re: Link to Microsoft Virtual System

Shweta N Mishra31-Oct-14 5:21

Shweta N Mishra

31-Oct-14 5:21

Yes thanks got it.

Uncertain on What to Use as Foreign Key of a Table

ASPnoob30-Oct-14 5:19

ASPnoob

30-Oct-14 5:19

Hi all, I am trying to design a database to hold data for a job portal. Basically I have a database called SiteUsers which contains five tables (UsersTable, JobSeekersTable, ResumesTable, EmployersTable, and JobsTable). The following are the table schema for UsersTable, ResumesTable, and JobsTable.

The UsersTable has a Primary Key (PK) of UserID and a Foreign Key (FK) of Username.
The ResumesTable has a PK of ResumeID and the JobsTable has a PK of JobID.
The JobSeekersTable has a PK of Username.
The EmployersTable has a PK of Username.

The question I have is should the JobSeekersTable FK also be Username, or ResumeID, or a composite of Username and ResumeID. I think that the FK should be Username because it's unique but because it's not the primary key of the JobsTable I am uncertain. Also I did not want to use ResumeID as the FK in the JobSeekersTable because although it will be unique a job seeker can have more than one resume.

Whether you agree or disagree with my assumption that Username should be the PK and FK in the JobSeekersTable, please elaborate your answer . Thanks in advance for your help.

modified 30-Oct-14 12:35pm.

Re: Uncertain on What to Use as Foreign Key of a Table

PIEBALDconsult30-Oct-14 5:59

Re: Uncertain on What to Use as Foreign Key of a Table

30-Oct-14 5:59

0) You don't actually define a foreign key on a table
1) You may want to define a unique index on UsersTable, but don't use it as a foreign key
2) The other tables should be related to UsersTable on UserID, not on Username

Eddy Vluggen30-Oct-14 6:08

Eddy Vluggen

30-Oct-14 6:08

A foreign key is a primary key from another table. The "username" in the Users table (calling it userstable is redundant, all those things are tables and not chairs with little chance of confusing those) is not a foreign key - but it might be candidate-primary key.

Also, the artificial autonumber may be unique, but it does not mean that one can assign it blindly to be the primary key without asking oneself what the user will be using to uniquely identify a record.

You should look into normalization.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Uncertain on What to Use as Foreign Key of a Table

ASPnoob30-Oct-14 6:34

ASPnoob

30-Oct-14 6:34

Hi, thanks for responding. You said "calling it userstable is redundant", that was a typo.

plsql

Member 1118871528-Oct-14 21:28

Member 11188715

28-Oct-14 21:28

What r the works u r doing in the IT company..I want to become plsql developer..please share ur works in brief..

Re: plsql

Mycroft Holmes28-Oct-14 22:03

Retrieve Data Using Comma Separated List

28-Oct-14 22:03

The first thing you should do is learn to use a keyboard!

Never underestimate the power of human stupidity
RAH

Re: plsql

PhilLenoir29-Oct-14 3:04

PhilLenoir

29-Oct-14 3:04

I foresee a return to single letter variables. Bring back the ZX80!

Life is like a s**t sandwich; the more bread you have, the less s**t you eat.

ASPnoob28-Oct-14 20:10

ASPnoob

28-Oct-14 20:10

Hi all,
Suppose I have a database table named JobsTable and among the fields in the table are JobDescriptions, and JobTitle. How would I write my Where clause if users can get all the JobTitle where the JobDescriptions field contains any word in the comma separated list they entered?

I tried the Like keyword but it is not doing what I want. Thanks in advance for your help.

Re: Retrieve Data Using Comma Separated List

Mycroft Holmes28-Oct-14 22:01

Re: Retrieve Data Using Comma Separated List

28-Oct-14 22:01

I have a function (every dev does) which splits a string and returns a table with ID and Item, you could then join the split with your data table

SQL

Select *
From Table T
inner join fn_Split(@List,',') l on L.item = T.FieldName

SQL

ALTER   FUNCTION [dbo].[fn_Split]
 (@List  varchar(8000), @Delimiter char(1))

RETURNS @Results table
 (Item varchar(8000),ID int Identity(1,1))

AS

begin
 declare @IndexStart int
 declare @IndexEnd int
 declare @Length  int
 declare @Word  varchar(8000)

 set @IndexStart = 1
 set @IndexEnd = 0

 set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
	set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)
		
	If @Delimiter = char(32) 
		set @IndexEnd = charindex(Space(1), @List, @IndexStart)

	if @IndexEnd = 0
		set @IndexEnd = @Length + 1

	set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
	set @IndexStart = @IndexEnd + 1

	INSERT INTO @Results(Item)
	SELECT @Word
end

return
end

Never underestimate the power of human stupidity
RAH

Shweta N Mishra29-Oct-14 1:23

Shweta N Mishra

29-Oct-14 1:23

You can also convert comma separated list to rows(column to rowConverting comma separated data in a column to rows for selection[^]) and then you can run the like query against it.

Re: Retrieve Data Using Comma Separated List

Mycroft Holmes29-Oct-14 3:13

Advice on Perfomance Turning of the Query

29-Oct-14 3:13

Yeah I probably snaffled that code from someone in the 90s and have not looked at it since Blush | :O

, I'm certain there is a better way of splitting the items.

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd28-Oct-14 7:27

Vimalsoft(Pty) Ltd

28-Oct-14 7:27

hi guys

I need some advice , i have a Query that runs very long because of the following lines of Code , i know you dont understand my data or table structure , you can just advice on a High Level

   ( CASE WHEN ( SELECT TOP 1
                      ATTRIB_CODE
            FROM      SDE.[NOTIFICATION] WITH ( NOLOCK )
            WHERE     ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = LP.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                        AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      )
                      AND ARCHIVE_DATE IS NULL
          ) LIKE '00090009%'
     THEN ( SELECT TOP 1
                      ATTRIB_CODE COLLATE SQL_Latin1_General_CP1_CI_AS
            FROM      SDE.[NOTIFICATION] N WITH ( NOLOCK )
            WHERE     N.ARCHIVE_DATE IS NULL
                      AND N.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      AND N.LIS_KEY  COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                      AND N.ATTRIB_CODE LIKE '00090009%'
                      AND N.ARCHIVE_DATE IS NULL
                      AND V.ARCHIVE_DATE IS NULL
          )
     ELSE ISNULL(( SELECT TOP 1
                          ATTRIBUTE_CODE
                   FROM   SDE.VALUATION WITH ( NOLOCK )
                   WHERE  ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                            AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
                          )
                          AND VAL_STATUS_ID = 2
                          AND ARCHIVE_DATE IS NULL
                 ), '')
END ) AS ATTRIBUTE_CODE ,

thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

PIEBALDconsult28-Oct-14 8:04

28-Oct-14 8:04

SQL Server? Did you check the execution plan?

Eddy Vluggen28-Oct-14 8:59

Eddy Vluggen

28-Oct-14 8:59

Vuyiswa Maseko wrote:
you can just advice on a High Level

There is a high level checklist for performance here[^].

I'd recommend against optimizer hints unless you can explain how SQL server handles locking. I'd also recommend on removing the collation-checks; it would be set once for the server, and not be repeated in each query as that takes extra time.

It also looks a lot like logic that could be run when inserting/updating the record. If that is not helping enough, then look into partitioning the table.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes28-Oct-14 12:59

28-Oct-14 12:59

As a last resort look into Parameter Sniffing[^], I know it sounds like a perversion but it can be an issue.

Basically the work around is to create a set of local variables for each parameter passed into the proc and use the local variables in the proc and not the parameters.

Never underestimate the power of human stupidity
RAH

Combine results from 2 foxpro DBF files using union

Vimalsoft(Pty) Ltd4-Nov-14 21:56

Vimalsoft(Pty) Ltd

4-Nov-14 21:56

Thank you very much for the Advice, no my Query runs for 5 Min on 800 000 Records

jkirkerx27-Oct-14 12:30

jkirkerx

27-Oct-14 12:30

On this query, I get 2 sets of results for each part number, which is probably what I asked for.
I'm trying to get 1 set of results for each part number, I just need to figure out the proper method.

I'll try some critical thinking here, it can't be the group by clause, so perhaps I'm suppose to wrap the union in a select statement like my count in the post below? But then I have the distinct, and I don't want to mess that up.

Once I have this done, I think I'm done writing for these old account mate dos programs.

Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
"   h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "

queryString +=
" UNION ALL "

queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
"   v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO "

Makes no sense how it works

jkirkerx27-Oct-14 13:01

jkirkerx

27-Oct-14 13:01

I think got it. The numbers look correct on a couple of test items.
I'm shocked that it works, doesn't really make sense to me how the inner select lines up with the main select.

queryString +=
        " SELECT " & _
        " DISTINCT FITEMNO " & _
        ", SUM(FSHIPQTY) " & _
        ", AVG(FCOST) " & _
        ", AVG(FPRICE) " & _
        ", SUM(FSHIPQTY * FPRICE) " & _
        ", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
        ", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST)" & _
        " FROM " & _
        "(" & _
        "   SELECT " & _
        "      h.FITEMNO " & _
        "    , h.FSHIPQTY " & _
        "    , h.FCOST " & _
        "    , h.FPRICE " & _
        "   FROM ARTRS01H.dbf h " & _
        "   WHERE " & _
        "   h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
        "   UNION ALL " & _
        "   SELECT " & _
        "      v.FITEMNO " & _
        "    , v.FSHIPQTY " & _
        "    , v.FCOST " & _
        "    , v.FPRICE " & _
        "   FROM ARTRS01.dbf v " & _
        "   WHERE " & _
        "   v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
        ")" & _
        " GROUP BY FITEMNO "

Select count on 2 DBF files

jkirkerx27-Oct-14 11:11

jkirkerx

27-Oct-14 11:11

Sort of a dumb question here, I have 2 DBF files, fox-pro DBF files that are identical, in which I want to union all and get the count of the FITEMNO, which there is 1 in the first file and 1 in the 2nd file, so I get a count of 2.

Is this normal behavior or did I construct the statement wrong below?

SELECT COUNT(FITEMNO) AS hCount
 FROM
   ( 
     SELECT
     FITEMNO
     FROM ARTRS01H.dbf
     WHERE
     FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
     AND
     FCUSTNO = @FCUSTNO
     AND
     FITEMNO = @FITEMNO
   	GROUP BY FITEMNO
   	UNION ALL
    SELECT
	FITEMNO
    FROM ARTRS01H.dbf
    WHERE
    FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
    AND
    FCUSTNO = @FCUSTNO
    AND
    FITEMNO = @FITEMNO
    GROUP BY FITEMNO
 )

Re: Select count on 2 DBF files

PIEBALDconsult27-Oct-14 11:25